Serial Numbers

Serial Number Generator

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.

  key                varchar2(30) primary key ,
  last_serial_number number ,
  increment_size     number
) ;
create function NEXT_SERIAL_NUMBER( p_key in varchar2 )
  return number

  v_next_serial_number serial_number_controls.last_serial_number%type ;


  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
      ( -20000, 'Serial number control "' || p_key || '" does not exist' );
  end if;

  return( v_next_serial_number );


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 )
    '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


The tutorials to follow show how to use the serial number generator in INSERT and UPDATE commands.


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.

Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=]SQL Snippets: Serial Numbers - Serial Number Generator[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="">SQL Snippets: Serial Numbers - Serial Number Generator</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Serial Numbers - Serial Number Generator
  • URL (href):