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.