Skip to Main Content
  • Questions

  • Default Value From A Sequence: Sequence Updates On Provided Value Entries With INSERT Through LOOP But Not on Individual INSERTs

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Hilari.

Asked: February 22, 2016 - 9:00 pm UTC

Last updated: June 09, 2020 - 4:55 pm UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

I have two samples of code below that use the same table and sequence definitions.

The first one inserts using individual insert statements, with the first three inserts giving an override value to the sequenced column and the final insert relying on the sequence. Here, even though 3 rows were previously inserted, the sequence produces a "1" for the sequenced column when the fourth row is inserted.

The second one inserts the same first three rows as above, but does so in a FOR loop. When the fourth row is inserted outside of the FOR LOOP, the sequence produces a "4" for the sequenced column.

So my question is, why does the sequence increase on each insert (with a value provided) when done in a FOR loop and doesn't increase on each insert when not using a FOR loop?

/*************************************************************************/
/*INSERT USING INDIVIDUAL STATEMENTS*/
/*************************************************************************/
CREATE SEQUENCE default_seq;

CREATE TABLE t1 (
col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
description VARCHAR2(30)
);

/

DECLARE

BEGIN

INSERT INTO t1 (col1, description) VALUES (1000,'1000,DESCRIPTION');
INSERT INTO t1 (col1, description) VALUES (1001,'1001,DESCRIPTION');
INSERT INTO t1 (col1, description) VALUES (1002,'1002,DESCRIPTION');
INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

END;

/

SELECT * FROM t1;

/

DROP TABLE t1;
DROP SEQUENCE default_seq;


/*************************************************************************/
/*INSERT USING A FOR LOOP*/
/*************************************************************************/
CREATE SEQUENCE default_seq;

CREATE TABLE t1 (
col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
description VARCHAR2(30)
);

/

DECLARE

BEGIN

FOR i IN 1..3 LOOP
INSERT INTO t1 (col1, description) VALUES (999 + i,999 + i || ',DESCRIPTION');
END LOOP;

INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

END;

/

SELECT * FROM t1;

/

DROP TABLE t1;
DROP SEQUENCE default_seq;


Thanks,
Hilari


and Connor said...

Thanks for the test case!

I've simplified it down to a non-plsql example, because I dont think it is related to FOR loops etc.


SQL> DROP TABLE t1;

Table dropped.

SQL>
SQL> DROP SEQUENCE default_seq;

Sequence dropped.

SQL> CREATE SEQUENCE default_seq;

Sequence created.

SQL>
SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (1000,'1000,DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (1001,'1001,DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (1002,'1002,DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
      1000 1000,DESCRIPTION
      1001 1001,DESCRIPTION
      1002 1002,DESCRIPTION
         1 DESCRIPTION only

SQL>
SQL> DROP TABLE t1;

Table dropped.

SQL> DROP SEQUENCE default_seq;

Sequence dropped.

SQL> CREATE SEQUENCE default_seq;

Sequence created.

SQL>
SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> variable i number
SQL> exec :i := 0;

PL/SQL procedure successfully completed.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
       999 999,DESCRIPTION
       999 999,DESCRIPTION
       999 999,DESCRIPTION
         4 DESCRIPTION only

SQL>
SQL>
SQL>
SQL>


When you have an *expression* for the value to get into the column with the default sequence, we don't know *in advance* if that expression might evaluate to null. So we'll pick up a sequence value 'along the way' to use just in case it turns out to be null.

For example, if I repeat the exercise, but the column is DEFAULT not DEFAULT ON NULL, you can see we dont have to do that:

SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT default_seq.NEXTVAL,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> variable i number
SQL> exec :i := 0;

PL/SQL procedure successfully completed.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
       999 999,DESCRIPTION
       999 999,DESCRIPTION
       999 999,DESCRIPTION
         1 DESCRIPTION only


Hope this helps.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hilari, February 23, 2016 - 12:55 pm UTC

Thank you so much for your response! It answered my question and gave me an alternative. :)
Connor McDonald
February 23, 2016 - 1:25 pm UTC

Glad we could help

Excelent Explanation

Jaromir Nemec, November 27, 2018 - 9:39 pm UTC

I didn't somehow realized the difference between DEFAULT and DEFAULT ON NULL. Now I know!

thanks,

Marmite Bomber
Connor McDonald
November 28, 2018 - 1:07 am UTC

glad we could help

When is sequence used and why?

Michal, June 09, 2020 - 1:34 pm UTC

Thanks for explanation, but I have another example, which is not clear to me...
drop sequence test_seq;
drop table test_tab;

create sequence test_seq;
create table test_tab
( id number  DEFAULT ON NULL test_seq.nextval primary key, 
  descript   varchar2(30) );
  
insert into test_tab (descript) values ('hello');
insert into test_tab (id, descript) values (101, 'hello!');
insert into test_tab (descript) values ('goodbye');
insert into test_tab (id, descript) select 102, 'goodbye!' from dual union all select 103, 'goodbye' from dual;
insert into test_tab (descript) values ('welcome');
insert into test_tab (id, descript) with x as (select 104, 'welcome!' from dual) select * from x;
insert into test_tab (descript) values ('ok');
insert into test_tab (id, descript) with x as (select 105, 'ok!' from dual union all select 106, 'ok!' from dual) select * from x;
insert into test_tab (descript) values ('surprise');
commit;

select * from test_tab;

Result is:
        ID DESCRIPT                      
---------- ------------------------------
         1 hello                         
       101 hello!                        
         2 goodbye                       
       102 goodbye!                      
       103 goodbye                       
         3 welcome                       
       104 welcome!                      
         4 ok                            
       105 ok!                           
       106 ok!                           
         7 surprise              

11 rows selected.

Why the sequence is used ("burned") only in case of insert using CTE with UNION ALL?
Chris Saxon
June 09, 2020 - 4:55 pm UTC

Because the sequence appears in the plan for when using CTE, but not for the others ;)

explain plan for   
insert into test_tab (id, descript) 
  select 102, 'goodbye!' from dual 
  union all 
  select 103, 'goodbye' from dual;
  
select * 
from   table(dbms_xplan.display);

-----------------------------------------------------------------------------    
| Id  | Operation                | Name     | Rows  | Cost (%CPU)| Time     |    
-----------------------------------------------------------------------------    
|   0 | INSERT STATEMENT         |          |     2 |     4   (0)| 00:00:01 |    
|   1 |  LOAD TABLE CONVENTIONAL | TEST_TAB |       |            |          |    
|   2 |   UNION-ALL              |          |       |            |          |    
|   3 |    FAST DUAL             |          |     1 |     2   (0)| 00:00:01 |    
|   4 |    FAST DUAL             |          |     1 |     2   (0)| 00:00:01 |    
-----------------------------------------------------------------------------
  
explain plan for   
insert into test_tab (id, descript) 
  with x as (select 104, 'welcome!' from dual) 
  select * from x;
  
select * 
from   table(dbms_xplan.display);

-----------------------------------------------------------------------------    
| Id  | Operation                | Name     | Rows  | Cost (%CPU)| Time     |    
-----------------------------------------------------------------------------    
|   0 | INSERT STATEMENT         |          |     1 |     2   (0)| 00:00:01 |    
|   1 |  LOAD TABLE CONVENTIONAL | TEST_TAB |       |            |          |    
|   2 |   FAST DUAL              |          |     1 |     2   (0)| 00:00:01 |    
-----------------------------------------------------------------------------
  
explain plan for  
insert into test_tab (id, descript) 
  with x as (
    select 105, 'ok!' from dual 
    union all 
    select 106, 'ok!' from dual
  ) 
  select * from x;
  
select * 
from   table(dbms_xplan.display);

-------------------------------------------------------------------------------------    
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    
-------------------------------------------------------------------------------------    
|   0 | INSERT STATEMENT         |          |     2 |    16 |     4   (0)| 00:00:01 |    
|   1 |  LOAD TABLE CONVENTIONAL | TEST_TAB |       |       |            |          |    
|   2 |   SEQUENCE               | TEST_SEQ |       |       |            |          |    
|   3 |    VIEW                  |          |     2 |    16 |     4   (0)| 00:00:01 |    
|   4 |     UNION-ALL            |          |       |       |            |          |    
|   5 |      FAST DUAL           |          |     1 |       |     2   (0)| 00:00:01 |    
|   6 |      FAST DUAL           |          |     1 |       |     2   (0)| 00:00:01 |    
-------------------------------------------------------------------------------------


I'm not 100% sure why at this stage; but it probably relates to the VIEW operation at line 3 when using the CTE. Notice this doesn't appear in the other plans.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library