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.

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.




Linking to SQL Snippets ™

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

  • [url=http://www.sqlsnippets.com/en/topic-11881.html]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="http://www.sqlsnippets.com/en/topic-11881.html">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): http://www.sqlsnippets.com/en/topic-11881.html