Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Please could you add the expected result in tabular form? –  Jack Douglas Aug 21 '12 at 18:44
add comment

4 Answers

up vote 1 down vote accepted

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 
share|improve this answer
    
clever workaround! –  Isaac Kleinman Aug 27 '12 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. –  FreshPhilOfSO Oct 14 '12 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. –  Chris Saxon Oct 15 '12 at 11:58
add comment

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.

share|improve this answer
    
+1 for your rule :-) –  Jack Douglas Aug 22 '12 at 17:59
add comment

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 
*/
share|improve this answer
    
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. –  Isaac Kleinman Aug 21 '12 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. –  Jack Douglas Aug 21 '12 at 18:43
    
true, I'm not particular about that though. I just constructed this scenario as a MWE. –  Isaac Kleinman Aug 21 '12 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). –  Jack Douglas Aug 21 '12 at 19:10
add comment

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.

share|improve this answer
    
+1 though the results are not deterministic without an order by –  Jack Douglas Aug 21 '12 at 16:28
    
What's wrong with mixing currval and 'nextval`? –  Isaac Kleinman Aug 21 '12 at 18:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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