SQL Snippets ™: Tutorials for Oracle Developers

This tutorial discusses how to use the Serial Number Generator created earlier in this section with INSERT commands.

To do this we first create a serial number control row.

insert into serial_number_controls
  ( key, last_serial_number, increment_size )
values
  ( 'T.SERIAL_NUMBER' , 0 , 1 )
;

commit;
 

Then we simply call NEXT_SERIAL_NUMBER() from the insert command that creates the row the serial number will be saved in.

insert into t ( key, name, serial_number )
  values ( 1, 'a', NEXT_SERIAL_NUMBER( 'T.SERIAL_NUMBER' ) );

insert into t ( key, name, serial_number )
  values ( 2, 'b', NEXT_SERIAL_NUMBER( 'T.SERIAL_NUMBER' ) );

insert into t ( key, name, serial_number )
  values ( 3, 'c', NEXT_SERIAL_NUMBER( 'T.SERIAL_NUMBER' ) );

commit;
 

select * from t order by key ;
 

       KEY NAME  SERIAL_NUMBER
---------- ----- -------------
         1 a                 1
         2 b                 2
         3 c                 3
 

If there is more than one insert statement per transaction and one or more of them fail, the sequence will still be gap free.

insert into t ( key, name, serial_number )
  values ( 4, 'd', next_serial_number( 'T.SERIAL_NUMBER' ) );

insert into t ( key, name, serial_number )
  values ( 5, 'OOPS, TOO BIG', next_serial_number( 'T.SERIAL_NUMBER' ) );
  values ( 5, 'OOPS, TOO BIG', next_serial_number( 'T.SERIAL_NUMBER' ) )
              *
ERROR at line 2:
ORA-12899: value too large for column "SCOTT"."T"."NAME" (actual: 13, maximum: 5)



insert into t ( key, name, serial_number )
  values ( 6, 'f', next_serial_number( 'T.SERIAL_NUMBER' ) );

insert into t ( key, name, serial_number )
  values ( 7, 'g', next_serial_number( 'T.OOPS_WRONG_NAME' ) );
  values ( 7, 'g', next_serial_number( 'T.OOPS_WRONG_NAME' ) )
                   *
ERROR at line 2:
ORA-20000: Serial number control "T.OOPS_WRONG_NAME" does not exist
ORA-06512: at "SCOTT.NEXT_SERIAL_NUMBER", line 16



insert into t ( key, name, serial_number )
  values ( 8, 'h', next_serial_number( 'T.SERIAL_NUMBER' ) );

commit;
 

select * from t order by key ;
 

       KEY NAME  SERIAL_NUMBER
---------- ----- -------------
         1 a                 1
         2 b                 2
         3 c                 3
         4 d                 4
         6 f                 5
         8 h                 6
 

INSERT with Subquery

Alternatively you can use NEXT_SERIAL_NUMBER() in the subquery of an INSERT command.

insert into t ( key, name, serial_number )
select
  t.key + 10 ,
  upper(t.name) ,
  NEXT_SERIAL_NUMBER( 'T.SERIAL_NUMBER' )
from
  t
;
 

select * from t order by key ;
 

       KEY NAME  SERIAL_NUMBER
---------- ----- -------------
         1 a                 1
         2 b                 2
         3 c                 3
         4 d                 4
         6 f                 5
         8 h                 6
        11 A                 7
        12 B                 8
        13 C                 9
        14 D                10
        16 F                11
        18 H                12
 

Gotchas

Scalability

Be aware the row in SERIAL_NUMBER_CONTROLS that directs serial number generation is locked for the entire time between the first INSERT and the COMMIT. All other users will be blocked from generating serial numbers in this time period. This seriously limits scalability. If this will be a problem, consider using one of the approaches described in Generator With UPDATE Commands or Precreated Serial Numbers.