I’ve spent much of the last week learning my way around the Oracle database and ran into a nice problem that I thought I’d share.
We often need to have a unique column to identify the record in a table. Unfortunately, there is no universal method of doing this in all database systems. Some of the better Object-Relational Mapping (ORM) systems create a unique identity column for us and automatically increment the id whenever we add a new record to the table. Oracle makes use of a sequence, an object that generates a new value every time you call sequence.NEXTVAL. The Syntax for creating a sequence is below:
CREATE SEQUENCE cid_seq START WITH 100 INCREMENT BY 1;
That looks simple enough. So let’s use it to insert some values in a table:
INSERT INTO orphans ( SELECT cid_seq.NEXTVAL AS customer_id, name FROM ( SELECT ... ) );
This doesn’t work. For one reason or another, Oracle does not permit the use of a sequence in a nested select statement like this. This left me scratching my head for a while, until I found a work-around. If we create a simple function that returns the next value from the sequence, we can call the function from the nested select query. Here’s our function:
CREATE OR REPLACE FUNCTION get_next_cseq RETURN NUMBER AS l_return NUMBER; BEGIN SELECT cid_seq.NEXTVAL INTO l_return FROM DUAL; RETURN l_return; END; /
And here’s the query again, modified to use the new function:
INSERT INTO orphans ( SELECT get_next_cseq() AS customer_id, name FROM ( SELECT ... ) );
That’ll do it.