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?

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 

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.

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 
*/

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.