The flaw I wish to harp on today is the error that pops up when you try to use a sequence in the INSERT ALL statement. I, along with countless other DB developers, use sequences just about every time I add a record to a database. It seems very short sighted to restrict the INSERT ALL statement so that they can’t handle sequences.

This blog entry will show how I was able to bypass this limitation and make productive use of INSERT ALL.

I intend to show how to overcome this particular error:ORA-02287: sequence number not allowed here

First, an example of a few statements that will fail, and some comments on why.

Suppose you have one table that has a one to many relationship with another table. Oracle allows you to use a single statement to add a row to the parent and also add one or more rows to the child table (or a whole bunch of child tables really) all in one convenient INSERT ALL statement.

Unsuccessful Attempts

Here is what I tried that I thought should have worked.

-- this doesn't work, but should
-- ORA-02287: sequence number not allowed here
insert all
into ordered_list values (list_id, list_title)
into ordered_list_node values (list_id, 1, red_id)
into ordered_list_node values (list_id, 2, yellow_id)
into ordered_list_node values (list_id, 3, blue_id)
select master_sq.NextVal as list_id
, 'Primary colors' as list_title
, (select id from color where title = 'Red') as red_id
, (select id from color where title = 'Yellow') as yellow_id
, (select id from color where title = 'Blue') as blue_id
from dual;

The part that doesn’t work in on line 8, where I pull a value from the SEQUENCE named master_sq. It turns out that, even though this statement selects only a single row, it still could get confused and pull multiple values from the sequence. I know that it can get confused because of an attempt I made (described below) to cheat and bypass the restriction on using a sequence. Rather than fix that problem, they just make it raise an error.

I feel that raising an error is a real cop out, because it makes sense that an obvious use of the INSERT INTO statement is to insert a row into a parent table along with its child elements. Why else would you do an insert into multiple tables at once, except to reuse the primary key of the main table? Why they don’t allow reading from sequences is beyond me, because sequences are where we get our primary key values from in the first place. But, I digress, lets solve the problem and not rant about the need for a solution.

So, the next thing I tried was to pop the master_sq.NextVal BEFORE the INSERT INTO statement, and then just use CurrVal instead. I figured this would work because it doesn’t run the chance of being called multiple times and doing something unexpected.

Here’s the next piece of SQL I tried that failed, prompting this whole blog entry:

-- this doesn't work, but could if they wanted to allow it
-- ORA-02287: sequence number not allowed here
select master_sq.nextval from dual;
insert all
into ordered_list values (list_id, list_title)
into ordered_list_node values (list_id, 1, red_id)
into ordered_list_node values (list_id, 2, yellow_id)
into ordered_list_node values (list_id, 3, blue_id)
select master_sq.CurrVal as list_id
, 'Primary colors' as list_title
, (select id from color where title = 'Red') as red_id
, (select id from color where title = 'Yellow') as yellow_id
, (select id from color where title = 'Blue') as blue_id
from dual;

I know this COULD work because one of the solutions I came up with uses this method. But, someone at Oracle decided it would be easier to just block all uses of sequences in the select clause of an insert all statement.

This Works, But I Don’t Like It

The next thing I tried was this, it does work, but defeats the purpose, and isn’t as elegant as what I ended up with in the end:

In this chunk of code, I insert the parent row first, and then I find its id using a select query when I create the child statements. I think this is a bad solution for two reasons. (1) you have to fetch the record you just inserted using something other than the primary key, which sometimes isn’t going to be unique, and creates overhead of searching a table for no good reason. And (2) it isn’t one statement, so there is overhead of sending and compiling 2 statements to the server, and receiving two responses from the server.

The Right Way

Here’s the most elegant thing I could come up with. Instead of using the Sequence down in the select clause, you put it up in the values clauses. The parent record grabs the nextval and the child records grab the currval. Like so:

Now, even though this solution uses one statement, and doesn’t need pl/sql, it still has its drawbacks. For instance, if a trigger on my child table pulled from the sequence in the background, that could be bad.

Also, Oracle just happens to process these inserts sequentially, but SQL is not a sequential language. What if down the road they did the inserts in reverse order, or each on its own thread? Who knows how portable this answer is? On the other hand, it seems to work well, and these questions are the reasons we write unit tests and test things before we move to a new generation of Oracle’s server — right?

I really wish that we could have “selected” the sequence.nextval down below into a single row result set that didn’t keep hitting the sequence for a new value each time it was used, becuase then we wouldn’t have to worry about any of this. But, I guess you cant have your cake and eat it too.

The 1st Runner Up (Previously the Right Way)

This solution is not as good as the “Right way” because it requires a little PL/SQL magic, and it uses two statements, so it has a little more overhead.

-- You'll need to do some things (described below) in order for this to work
select master_sq.nextval from dual;
insert all
into ordered_list values (list_id, list_title)
into ordered_list_node values (list_id, 1, red_id)
into ordered_list_node values (list_id, 2, yellow_id)
into ordered_list_node values (list_id, 3, blue_id)
select getCurrVal('master_sq') as list_id
, 'Primary colors' as list_title
, (select id from color where title = 'Red') as red_id
, (select id from color where title = 'Yellow') as yellow_id
, (select id from color where title = 'Blue') as blue_id
from dual;

As you can see, I wasn’t able to overcome the need to execute a separate SQL statement for updating the sequence so it contains a unique value for us to use. But, I did eliminate the need to search the table in order to find the recently inserted id.

I use the first select statement to “claim” a new ID from the sequence that we will use when we insert the record with its children.

To get it to work, all you need to do is create a PL/SQL function to read the current value of the sequence rather than read it right there in the INSERT ALL statement. Here is the function I used…

The Wrong Way

Before I came to the above solution, I tried this out, which works in the sense that it compiles and Oracle will run it without complaining, and records will be inserted. But, it fails in the sense that the parent table’s id that was pulled from the sequence gets pulled several times, and all the child records don’t actually get a reference to the correct parent.

--DON'T do this, it does bad things.
insert all
into ordered_list values (list_id, list_title)
into ordered_list_node values (list_id, 1, red_id)
into ordered_list_node values (list_id, 2, yellow_id)
into ordered_list_node values (list_id, 3, blue_id)
select getNextVal('master_sq') as list_id
, 'Primary colors' as list_title
, (select id from color where title = 'Red') as red_id
, (select id from color where title = 'Yellow') as yellow_id
, (select id from color where title = 'Blue') as blue_id
from dual;

The fact that this happens is really quite disturbing. Not just because it is inconvenient.

Think about this with me. Oracle doesn’t just run the getNextVal PL/SQL function once when it selects all the values that will be spread out across the tables we are inserting into, it actually runs the PL/SQL function once for every row that reads the list_id field as part of its insert operation.

That could have unforeseen consequences if you don’t know that, and, it seems awfully performance heavy to be switching context between SQL and PL/SQL all the time like that. But, in the end, that is up to them to fix, this solution works, and seems to perform better than the other alternatives.

Example

Here is a full blown example from start to finish that illustrates using this method.

-- this sequence keeps a unique id across all tables that
-- are (or may some day be) listable so that the records they
-- reference are unique, even across multple tables.
create sequence master_sq;
-- create tables for organizing listable items into ordered lists
create table ordered_list
( id number
, title varchar2(120)
);
create table ordered_list_node
( list_id number
, list_index number
, item_id number
);
-- create an auto-sequencing id for the ordered_list table
create or replace trigger ordered_list_auto_sequence
before insert on ordered_list
for each row when (new.id is null)
begin
select master_sq.nextval into :new.id from dual;
end;
/
-- some fun test data
create table color
( id number
, title varchar2(20)
);
create or replace trigger color_auto_sequence
before insert on color
for each row when (new.id is null)
begin
select master_sq.nextval into :new.id from dual;
end;
/
-- add colors to our color table, in no particular order
insert all
into color (title) values ('Red')
into color (title) values ('Violet')
into color (title) values ('Green')
into color (title) values ('Blue')
into color (title) values ('Indigo')
into color (title) values ('Orange')
into color (title) values ('Yellow')
select * from dual;
-- make a ordered_list of the primary colors,
-- in the order people usually talk about them
select master_sq.nextval from dual;
insert all
into ordered_list values (master_sq.nextval, list_title)
into ordered_list_node values (master_sq.currval, 1, red_id)
into ordered_list_node values (master_sq.currval, 2, yellow_id)
into ordered_list_node values (master_sq.currval, 3, blue_id)
select 'Primary colors' as list_title
, (select id from color where title = 'Red') as red_id
, (select id from color where title = 'Yellow') as yellow_id
, (select id from color where title = 'Blue') as blue_id
from dual;
-- make a ordered_list of colors, in rainbow order
select master_sq.nextval from dual;
insert all
into ordered_list values (master_sq.nextval, list_title)
into ordered_list_node values (master_sq.currval, 1, red_id)
into ordered_list_node values (master_sq.currval, 2, orange_id)
into ordered_list_node values (master_sq.currval, 3, yellow_id)
into ordered_list_node values (master_sq.currval, 4, green_id)
into ordered_list_node values (master_sq.currval, 5, blue_id)
into ordered_list_node values (master_sq.currval, 6, indigo_id)
into ordered_list_node values (master_sq.currval, 7, violet_id)
select 'Rainbow colors' as list_title
, (select id from color where title = 'Red') as red_id
, (select id from color where title = 'Orange') as orange_id
, (select id from color where title = 'Yellow') as yellow_id
, (select id from color where title = 'Green') as green_id
, (select id from color where title = 'Blue') as blue_id
, (select id from color where title = 'Indigo') as indigo_id
, (select id from color where title = 'Violet') as violet_id
from dual;
-- Now, lets see the results...
-- pull some data from our test lists
select clr.title as color_title
from ordered_list list
join ordered_list_node node
on node.list_id = list.id
join color clr
on node.item_id = clr.id
where list.title = 'Primary colors'
order by node.list_index;
select clr.title as color_title
from ordered_list list
join ordered_list_node node
on node.list_id = list.id
join color clr
on node.item_id = clr.id
where list.title = 'Rainbow colors'
order by node.list_index;