This tutorial discusses one way to implement a serial number generator. Serial number generators create a unique, sequential, gap free series of numbers typically used for implementing invoice numbers, check numbers, part numbers, etc.
This serial number generator uses two custom objects, a table and a function, created as follows.
create table SERIAL_NUMBER_CONTROLS ( key varchar2(30) primary key , last_serial_number number , increment_size number ) ;
create function NEXT_SERIAL_NUMBER( p_key in varchar2 ) return number is v_next_serial_number serial_number_controls.last_serial_number%type ; begin update SERIAL_NUMBER_CONTROLS set last_serial_number = last_serial_number + increment_size where key = p_key returning last_serial_number into v_next_serial_number ; if sql%rowcount = 0 then raise_application_error ( -20000, 'Serial number control "' || p_key || '" does not exist' ); end if; return( v_next_serial_number ); end; /
To start a new series of numbers simply insert a row into the SERIAL_NUMBER_CONTROLS table. For example, the following command creates a control for a series of numbers that starts with 10,000 and increments by 10.
insert into serial_number_controls ( key, last_serial_number, increment_size ) values ( 'MY_TABLE.SERIAL_NUMBER_COLUMN' , -- I like to use the target column name here 9990 , -- use the number immediately before the one you want to start with 10 ) ; commit;
The tutorials to follow show how to use the serial number generator in INSERT and UPDATE commands.
Gotchas
Do not attempt to retrieve the next serial number using a query. Besides being a risky thing to do (the retrieved value may not make its way into the target table) the serial number generator's design actually defends against such attempts.
select next_serial_number( 'T.SERIAL_NUMBER' ) as next_number from dual; select next_serial_number( 'T.SERIAL_NUMBER' ) as next_number * ERROR at line 1: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "SCOTT.NEXT_SERIAL_NUMBER", line 9
This restriction does not apply to subqueries inside an INSERT command (see Generator With INSERT Commands.