Restriction when column default is sequence.nextval

Oracle 12c introduced the ability to specify sequence.nextval as the default on a column, which is really nice – including the fact that it eliminates one of your excuses why you don’t decommission those old triggers.

Unfortunately it doesn’t work as you might expect if you use an INSERT ALL statement; it evaluates the default expression once per statement, instead of once per row.

Test case:


create sequence test_seq;

create table test_tab
( id number default test_seq.nextval primary key
, dummy varchar2(100) not null );

insert into test_tab (dummy) values ('xyz');

1 row inserted.

insert all
into test_tab (dummy) values ('abc')
into test_tab (dummy) values ('def')
select null from dual;

Error report -
SQL Error: ORA-00001: unique constraint
(SCOTT.SYS_C00123456) violated

A minor issue, usually, but something to be aware of – especially if you’re not in the habit of declaring your unique constraints to the database!

create sequence test_seq;

create table test_stupid_tab
( id number default test_seq.nextval
, dummy varchar2(100) not null );

insert into test_tab (dummy) values ('xyz');

1 row inserted.

insert all
into test_tab (dummy) values ('abc')
into test_tab (dummy) values ('def')
select null from dual;

2 rows inserted.

select * from test_tab;

i dummy
= =====
1 xyz
2 abc
2 def

ADDENDUM 28/10/2016

Another similar scenario which might trip you up is where you are inserting from a UNION view:

create sequence test_seq;

create table test_tab
( id number default test_seq.nextval primary key
, dummy varchar2(100) not null
);

insert into test_tab (dummy) select 'x' from dual;
-- success

insert into test_tab (dummy)
select 'y' from dual union all select 'z' from dual;
-- fails with ORA-01400 "cannot insert NULL into id"

insert into test_tab (dummy) select c from (
select 'y' c from dual union all select 'z' from dual
);
-- success
Must See: AUSOUG Connect 2016 Perth
Enhancement Request for SQL Developer for users of Logger

Comments

  1. connormcdonald
    3 October 2016 - 5:58 pm

    Nothing to do with 12c and/or default – that has always been the case with multi-table insert

    SQL> create sequence test_seq;
    
    Sequence created.
    
    SQL> create table test_tab
      2  ( id number
      3  , dummy varchar2(10) not null );
    
    Table created.
    
    SQL> insert all
      2  into test_tab (id,dummy) values (test_seq.nextval,'abc')
      3  into test_tab (id,dummy) values (test_seq.nextval,'def')
      4  select null from dual;
    
    2 rows created.
    
    SQL> select * from test_tab;
    
    ID DUMMY
    == =====
    1 abc
    1 def
    
    2 rows selected.
    
  2. Yep, thanks for that. My only point was that 12c gives the ability to move the sequence code out of triggers, and this is just some code that used to work with triggers that no longer works when the sequence is applied a column default. I should have been clearer that I’m not suggesting this is a bug or anything.

  3. That’s something that would not have occurred to me. I have created a similar example in Spanish at the URL which goes with this comment.

Leave a Reply

Your email address will not be published / Required fields are marked *