2

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?

1
  • 1
    Please could you add the expected result in tabular form? Aug 21, 2012 at 18:44

4 Answers 4

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 
3
  • clever workaround! Aug 27, 2012 at 19:24
  • The function is clearly not needed, as this is identical to my answer (with a superfluous function) which was posted several days before yours.
    – Philᵀᴹ
    Oct 14, 2012 at 8:13
  • @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. Oct 15, 2012 at 11:58
5

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.

2
  • +1 though the results are not deterministic without an order by Aug 21, 2012 at 16:28
  • What's wrong with mixing currval and 'nextval`? Aug 21, 2012 at 18:20
4

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 
*/
4
  • 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. Aug 21, 2012 at 18:19
  • 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. Aug 21, 2012 at 18:43
  • true, I'm not particular about that though. I just constructed this scenario as a MWE. Aug 21, 2012 at 19:06
  • 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). Aug 21, 2012 at 19:10
2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.