Best way to generate unique and consecutive numbers in Oracle

I need to generate unique and consecutive numbers (for use on an invoice), in a fast and reliable way. I currently use an Oracle sequence, but in some cases, the generated numbers are not consecutive because of exceptions that may occur.

I thought a开发者_运维知识库 couple of solutions to manage this problem, but neither of them convincing me. What solution do you recommend?

1. Use a select max ()

    SELECT MAX (NVL (doc_num, 0)) +1 FROM invoices
   

2. Use a table to store the last number generated for the invoice.

    UPDATE docs_numbers
        SET last_invoice = last_invoice + 1
   

3. Another Solution?

• Option 1 can always be made to fail some way in an environment with concurrent users.
• Option 2 will work, but will limit scalability — obligatory Tom Kyte reference: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1508205334476

As he recommends, you should really review the necessity for the "no gaps" requirement.

The gaps appear if a transaction uses a sequence number but is then rolled back.

Maybe the answer is not to assign the invoice number until the invoice can't be rolled back. This minimizes (but probably does not eliminate) the possibilities of gaps.

I'm not sure that there is any swift or easy way to ensure no gaps in the sequence - scanning for MAX, adding one, and inserting that is probably the closest to secure, but is not recommended for performance reasons (and difficulties with concurrency) and the technique won't detect if the latest invoice number is assigned, then deleted and reassigned.

Can you account for gaps somehow - by identifying which invoice numbers were 'used' but 'not made permanent' somehow? Could an autonomous transaction help in doing that?

Another possibility - assuming that gaps are relatively few and far between.

Create a table that records sequence numbers that must be reused before a new sequence value is grabbed. Normally, it would be empty, but some process that runs every ... minute, hour, day ... checks for gaps and inserts the missed values into this table. All processes first check the table of missed values, and if there are any present, use a value from there, going through the slow process of updating the table and removing the row that they use. If the table is empty, then grab the next sequence number.

Not very pleasant, but the decoupling of 'issuing invoice numbers' from 'scan for missed values' means that even if the invoicing process fails for some thread when it is using one of the missed values, that value will be rediscovered to be missing and re-reissued next time around - repeating until some process succeeds with it.

Keep the current sequence - you can use the following to reset the value to the maximum of what is currently stored in the table(s):

-- --------------------------------
-- Purpose..: Resets the sequences 
-- --------------------------------

DECLARE
  -- record of temp data table
  TYPE data_rec_type IS RECORD(
    sequence_name VARCHAR2(30),
    table_name    VARCHAR2(30),
    column_name   VARCHAR2(30));

  -- temp data table
  TYPE data_table_type IS TABLE OF data_rec_type INDEX BY BINARY_INTEGER;

  v_data_table data_table_type;
  v_index      NUMBER;
  v_tmp_id     NUMBER;

  -- add row to temp table for later processing
  --
  PROCEDURE map_seq_to_col(in_sequence_name VARCHAR2,
                           in_table_name    VARCHAR2,
                           in_column_name   VARCHAR2) IS
    v_i_index NUMBER;
  BEGIN
    v_i_index := v_data_table.COUNT + 1;
    v_data_table(v_i_index).sequence_name := in_sequence_name;
    v_data_table(v_i_index).table_name := in_table_name;
    v_data_table(v_i_index).column_name := in_column_name;
  END;

  /**************************************************************************
      Resets a sequence to a given value
  ***************************************************************************/
  PROCEDURE reset_seq(in_seq_name VARCHAR2, in_new_value NUMBER) IS

    v_sql       VARCHAR2(2000);
    v_seq_name  VARCHAR2(30) := in_seq_name;
    v_reset_val NUMBER(10);
    v_old_val   NUMBER(10);
    v_new_value NUMBER(10);

  BEGIN

    -- get current sequence value

    v_sql := 'SELECT ' || v_seq_name || '.nextval FROM DUAL';
    EXECUTE IMMEDIATE v_sql
      INTO v_old_val;

    -- handle empty value
    v_new_value := in_new_value;
    if v_new_value IS NULL then
      v_new_value := 0;
    END IF;

    IF v_old_val <> v_new_value then    
      IF v_old_val > v_new_value then
        -- roll backwards
        v_reset_val := (v_old_val - v_new_value) * -1;
      elsif v_old_val < v_new_value then
        v_reset_val := (v_new_value - v_old_val);
      end if;

      -- make the sequence rollback to 0 on the next call
      v_sql := 'alter sequence ' || v_seq_name || ' increment by ' ||
           v_reset_val || ' minvalue 0';
      EXECUTE IMMEDIATE (v_sql);

      -- select from the sequence to make it roll back
      v_sql := 'SELECT ' || v_seq_name || '.nextval FROM DUAL';
      EXECUTE IMMEDIATE v_sql
        INTO v_reset_val;

      -- make it increment correctly again
      v_sql := 'alter sequence ' || v_seq_name || ' increment by 1';
      EXECUTE IMMEDIATE (v_sql);

      -- select from it again to prove it reset correctly.
      v_sql := 'SELECT ' || v_seq_name || '.currval FROM DUAL';
      EXECUTE IMMEDIATE v_sql
        INTO v_reset_val;

    END IF;

    DBMS_OUTPUT.PUT_LINE(v_seq_name || ': ' || v_old_val || ' to ' ||
                     v_new_value);
  END;

  /*********************************************************************************************
    Retrieves a max value for a table and then calls RESET_SEQ.
  *********************************************************************************************/
  PROCEDURE reset_seq_to_table(in_sequence_name VARCHAR2,
                               in_table_name    VARCHAR2,
                               in_column_name   VARCHAR2) IS

    v_sql_body  VARCHAR2(2000);
    v_max_value NUMBER;

      BEGIN

    -- get max value in the table
    v_sql_body := 'SELECT MAX(' || in_column_name || '+0) FROM ' ||
              in_table_name;
    EXECUTE IMMEDIATE (v_sql_body)
      INTO v_max_value;

    if v_max_value is null then
      -- handle empty tables
      v_max_value := 0;
    end if;

    -- use max value to reset the sequence
    RESET_SEQ(in_sequence_name, v_max_value);

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Failed to reset ' || in_sequence_name ||
                       ' from ' || in_table_name || '.' ||
                       in_column_name || ' - ' || sqlerrm);
  END;

BEGIN
  --DBMS_OUTPUT.ENABLE(1000000);

  -- load sequence/table/column associations

  /***** START SCHEMA CUSTOMIZATION *****/
  map_seq_to_col('Your_SEQ',  
                 'your_table',
                 'the_invoice_number_column');

  /***** END SCHEMA CUSTOMIZATION *****/

  -- iterate all sequences that require a reset
  FOR v_index IN v_data_table.FIRST .. v_data_table.LAST LOOP

    BEGIN
      RESET_SEQ_TO_TABLE(v_data_table(v_index).sequence_name,
                         v_data_table(v_index).table_name,
                         v_data_table(v_index).column_name);
    END;
  END LOOP;

END;
/

-- -------------------------------------------------------------------------------------
-- End of Script.
-- -------------------------------------------------------------------------------------

The example is an anonymous sproc - change it to be proper procedures in a package, and call it prior to inserting a new invoice to keep the numbering consistent.

I think you'll find that using the MAX() of the existing numbers is prone to a new and exciting problem - duplicates can occur if multiple invoices are being created at the same time. (Don't ask me how I know...).

A possible solution is to derive the primary key on your INVOICE table from a sequence, but have this NOT be the invoice number. After correctly and properly creating your invoice, and after the point at which an exception or user's whim could cause the creation of the invoice to be terminated, you go to a second sequence to get the sequential number which is presented as "the" invoice number. This means you'll have two unique, non-repeating numbers on your INVOICE table, and the obvious one (INVOICE_NO) will not be the primary key (but it can and should be UNIQUE) so there's a bit of evil creeping in, but the alternative - which is to create the INVOICE row with one value in the primary key, then change the primary key after the INVOICE is created - is just too evil for words. :-)

Share and enjoy.

If you really want to have no gaps, you need to completely serialize access, otherwise there will always be gaps. The reasons for gaps are:

• rollback
• shutdown abort

It's not clear what you mean by 'because of exceptions that may occur'. If you want number NOT to be incremented if your transaction eventually rolls back then SEQUENCE is not going to work for you, because as far as I know, once NEXTVAL is requested from sequence the sequence position is incremented and rollback won't reverse it.

If this is indeed a requirements then you probably would have to resort of storing current counter in a separate table, but beware of concurrent updates - from both 'lost update' and scalability prospective.

I've come across this problem before. In one case, we were able to convince the business to accept that "real" invoices might have gaps, and we wrote a job that ran every day to "fill in" the gaps with "void" invoices for audit purposes.

In practice, if we put NOCACHE on the sequence, the number of gaps would be relatively low, so the auditors will usually be happy as long as their query on the "void" invoices don't return too many results.

You might have to re-think your process slighty and break it into more steps. Have one non-transactional step create the placeholder invoice (this not being in the transaction should eliminate gaps) and then within the transaction do the rest of your business. I think that was how we did it in a system I was stuck with years ago but I can't remember - I just remember it was "weird."

I'd say the sequence will guarantee unique/consecutive numbers but when you throw transactions in the mix that can't be guaranteed unless the sequence generation isn't within that transaction.

dpbradley's link in #2 sounds like your best bet. Tom keeps the transactionality with the caller, if you don't want that you could make it an autonomous transaction like so:

create or replace 
function getNextInvoiceNumber()
return number is
   l_invoicenum     number;

   pragma autonomous_transaction;
   begin
      update docs_numbers
         set last_invoice = last_invoice + 1
      returning last_invoice 
      into l_invoicenum;
      commit;

      return l_invoicenum;

   exception
      when others then
         rollback;
         raise;
end;

What we do is issue a sequence number to the transaction and then when the item we are processing is finalized we issue a permanent number (also a sequence). Works well for us.

Regards
K