Limiting Generated Sequence Values in Multi-table Insert

Question

In Oracle, given the following objects:

create table a (x number );
create table b (val number );
create table c (val number );
create sequence my_seq;

Suppose I populate table a as follows:

insert into a (x) values (1);
insert into a (x) values (2);
insert into a (x) values (3);
insert into a (x) values (4);

Using the values in a, I want to populate tables b and c as follows: when a.x is even, generate a new number from the sequence and insert my_seq.nextval into b.val and c.val; otherwise, insert my_seq.currval (the most recently generated value but not a new one) into c.val.

What I currently have is an insert all statement that looks like this:

insert all 
 when mod(x,2) = 0 then
 into b (val) values (my_seq.nextval)
 into c (val) values (my_seq.currval)
 else 
 into c (val) values (my_seq.currval)
select x from a

Since Oracle's treatment of sequences in an insert all statement is to generate a new value for each row returned in the subquery, I'm getting 4 newly generated values inserted into c. How can I attain the behavior I want, 2 newly newly generated values inserted once into b and twice into c?

• Jack Douglas: Please could you add the expected result in tabular form?

Answer 1

This can be done in a single statement, but you have to wrap the sequence call in a function.

By calling a function in the select (as opposed to the sequence directly), you overcome the problem of getting ORA-02287 errors. However, the function will be called once for each row in A, which is not what you want.

This can be overcome by defining the function as deterministic. This allows Oracle to optimize the function so it is only executed once for each distinct parameter value passed in. To make this work in this case, you'll need to pass in ceil(x/2):

create or replace function f(p integer) return number deterministic as
 l_retval pls_integer;
begin
 select my_seq.nextval into l_retval from dual;
 return l_retval;
end;
/
insert all 
 when mod(x,2) = 0 then
 into b (val) values (seq)
 into c (val) values (seq)
 else 
 into c (val) values (seq)
select x, f(ceil(x/2)) seq from a;
select * from b;
VAL
---
 1 
 2 
select * from c;
VAL
---
 1 
 2 
 1 
 2 

• Isaac Kleinman: clever workaround!
• Philᵀᴹ: The function is clearly not needed, as this is identical to my answer (with a superfluous function) which was posted several days before yours.
• Chris Saxon: @Phil - I disagree, the two answers are functionally different. If you modify the value returned by a sequence for the insert all then you'll end up with clashes if you also use it for normal single table inserts. The deterministic function doesn't modify the value returned by the sequence, it just reduces the number of sequence numbers consumed. So this solution can be used alongside normal inserts without generating clashes.

Answer 2

It can't be done because you're mixing curval and nextval (happy to be proven incorrect, by the way). You also can't use WITH xxxx AS in an INSERT ALL, which was my first thought as a way around this.

Anyway, this is a logical workaround for you:

insert all 
 when mod(x,2) = 0 then
 into b (val) values (my_seq.nextval/2)
 into c (val) values (trunc(my_seq.currval/2+0.5))
 else 
 into c (val) values (trunc(my_seq.currval/2+0.5))
select x from a;

Obviously the sequence number is always going to be double what you expect if you use it in other code, but I'm assuming that this is a 1-off statement.

• Jack Douglas: +1 though the results are not deterministic without an order by
• Isaac Kleinman: What's wrong with mixing currval and 'nextval`?

Answer 3

otherwise, insert my_seq.currval (the most recently generated value but not a new one) into c.val

This only means something if you have in mind a certain order that the rows in a will be processed (otherwise what is "the most recently generated value but not a new one"?). I've assumed they will be processed in descending order so that we hit an even number first (otherwise my_seq.currval is undefined).

If you are prepared to use PL/SQL this can be done simply:

testbed:

create table a (x number );
create table b (val number );
create table c (val number );
create sequence my_seq;
insert into a (x) values (1);
insert into a (x) values (2);
insert into a (x) values (3);
insert into a (x) values (4);
select * from a;
/*
X
-
1 
2 
3 
4 
*/

PL/SQL:

begin
 for r in (select * from a order by x desc) loop
 if mod(r.x,2)=0 then
 insert into b(val) values (my_seq.nextval);
 end if;
 insert into c(val) values (my_seq.currval);
 end loop;
end;
/

results:

select * from b;
/*
VAL
---
 1 
 2 
*/
select * from c;
/*
VAL
---
 1 
 1 
 2 
 2 
*/

• Isaac Kleinman: By "the most recently generated value but not a new one" I mean the currval of the sequence; I'm not sure what was not understood about that. And, no, I cant afford to do it in PL/SQL.
• Jack Douglas: Yes, but the values of currval that end up in c for odd values of x are indeterminate - ie they will depend on the order of rows returned by select x from a, which is undefined and unpredictable without an order by clause.
• Isaac Kleinman: true, I'm not particular about that though. I just constructed this scenario as a MWE.
• Jack Douglas: Is Phil's solution going to work for you then? otherwise there is no single statement solution (it can be done with a staging area of course).

Answer 4

Isaac,

As already mentioned by others, you will have to use an ORDER BY clause if you want to implement your logic of using either a new sequence value or previously used sequence value. (In fact, anytime when the question contains either "previous" or "next" words, it almost always need a query with explicit ORDER BY...that is, of course, my rule...) If your test case setup is exactly same as your actual problem, only then you can use suggested solution. Otherwise, you will almost always find a way where any variant of suggested sql containing sequence does not work. There is no way to achieve this in single SQL using sequence. The only way I know to do it in single SQL is to populate the tables using sequence numbers generated by SELECT part of the INSERT ALL statement and then adjusting the sequence next value separately.