Serial Numbers

Precreated Serial Numbers - API Code

On this page we examine the source code used to implement the T2_API package demonstrated in Precreated Serial Numbers.

IMPORTANT NOTE: While I have not personally used or seen this approach in the systems I have worked with, the following solution is how I expect it could be implemented. I have only run it on a single user test system so please treat it as a first draft. I have done minimal testing on this code and only included basic exception handling to make the solution easier to understand. If you notice any issues or bugs please drop me a line at the email address listed in the About: Feedback page.

Here is the package.

create package t2_api as

  function new_item( p_name )
    return t2.key%type -- returns the primary key of the new row


create package body t2_api as

  procedure create_t2_batch is -- see Note 1

    pragma autonomous_transaction;

    v_create_t2_batch_lock_id   constant integer        := 123 ; -- See Note 2
    v_batch_size                constant binary_integer := 5   ; -- See Note 3
    v_max_create_batch_run_time constant number         := 10  ; -- See Note 4

    v_dbms_lock_request_status integer ;
    v_max_serial_number t2.serial_number%type ;


    -- ensure that only one instance of CREATE_T2_BATCH
    -- runs at any given time (See Note 5)
    v_dbms_lock_request_status :=
      ( id                => v_create_t2_batch_lock_id ,
        timeout           => v_max_create_batch_run_time ,
        release_on_commit => true
      ) ;

    case v_dbms_lock_request_status
    when 0 then null ;
    when 1 then -- timeout detected
      ( -20000, 'Another CREATE_T2_BATCH process appears to be hung.' ) ;
      ( -20000,
        'DBMS_LOCK reported error status ' || v_dbms_lock_request_status
      ) ;
    end case ;

    -- create new rows with non-null serial numbers
    select nvl( max( serial_number ), 0 )
    into   v_max_serial_number
    from   t2 ;

    insert into t2 ( key, name, status, serial_number )
      ts.nextval                  as key ,
      'I' || ts.currval           as name ,
      'Available'                 as status ,
      v_max_serial_number + level as serial_number
    connect by
      level <= v_batch_size  -- see Note 6



  function new_item( p_name in ) return t2.key%type is

    v_row_id urowid ;
    v_key    t2.key%type ;

    v_max_attempts  constant binary_integer := 3 ;

    e_resource_busy exception ;
    pragma exception_init ( e_resource_busy, -54 );


    for v_attempt_number in 1 .. v_max_attempts loop

      -- find the first available row, lock it, update it, return
      for v_row in                              -- (see Gotcha #1)
      ( select ROWID as row_id, key
        from   t2
        where  status = 'Available'
        order by key
      loop begin

        select key
        into   v_key
        from   t2
        where  rowid = v_row.row_id
        for update nowait ;

        update t2 set status = 'Assigned', name = p_name
        where  rowid = v_row.row_id ;

        return( v_row.key );


        when e_resource_busy then null; -- try the next 'Available' row

      end; end loop ; -- v_row loop

      -- at this point there are no 'Available', unlocked rows
      -- left in T; create a new batch
      create_t2_batch ;

      -- there should be some 'Available' rows now,
      -- loop back and try again

    end loop; -- v_attempt_number loop

    raise_application_error( -20000, 'Too many unsuccessful attempts.' );



Note 1: This method only works when CREATE_T2_BATCH is the only module in the application allowed to insert non-null T2.SERIAL_NUMBER values. No modules should be allowed to update T2.SERIAL_NUMBER.

Note 2: Use a number that is unique among all modules in your database that use DBMS_LOCK.

Note 3: Set this to the number of rows you want to create in each batch. We use "5" here for demonstration purposes. Typically this number would be higher. The best value will be a compromise between frequency and duration. A number that is too small will block NEW_ROW callers frequently for short periods of time. A number that is too large will block NEW_ROW callers infrequently for long periods of time.

Note 4: Set this to the largest number of seconds you expect CREATE_T2_BATCH will ever take to run. If CREATE_T2_BATCH hangs for some reason, the next transaction that tries to call it will fail with a timeout error.

Note 5: To serialize access to CREATE_T2_BATCH we use a User Lock here to implement what is sometimes called a MUTEX, a mechanism for controlling mutually exclusive access to a shared resource.

Note 6: See Integer Series Generators: CONNECT BY LEVEL Method for an explanation of the connect by level <= v_batch_size clause.


1. Some people may be tempted to replace this loop with a single SELECT statement that uses the undocumented "FOR UPDATE NOWAIT SKIP LOCKED" syntax. I do not recommend this practise for the simple fact that undocumented features may not always work as expected (of course, the documented ones don't always either, but that's another story ;-) ).

2. If you insert item A at a given time and then insert item B some time later do not expect A's serial number to always be lower than B's. In certain situtations item A's serial number may be higher than B's. Here is an example.

  1. User 1 creates item X with serial number 123
  2. User 2 creates item A with serial number 124
  3. User 2 commits item A
  4. User 1 rolls back creation of item X
  5. User 3 creates item B with serial number 123
  6. User 2 commits item B

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 - Precreated Serial Numbers - API Code[/url]

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

  • <a href="">SQL Snippets: Serial Numbers - Precreated Serial Numbers - API Code</a>

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

  • Link Text : SQL Snippets: Serial Numbers - Precreated Serial Numbers - API Code
  • URL (href):