I need to generate unique and sequential numbers (for use on the invoice) in a fast and reliable way. currently using the Oracle sequence, but in some cases the numbers generated are not sequential due to exceptions that may occur.
I thought of several solutions to solve this problem, but none of them convinced me. Which solution do you recommend?
Use select max ()
SELECT MAX (NVL (doc_num, 0)) +1 FROM invoices
Use the table to store the last number generated for the invoice.
UPDATE docs_numbers SET last_invoice = last_invoice + 1
Another solution?
, , .
, , , . (, , ) .
, - MAX, , , , , ( concurrency), , , .
- - , "", " " - ? ?
- , .
, , , . , - , ... , , ... . , , , , . , .
, " " " " , , , - , - .
, " "
- reset , ():
-- -------------------------------- -- 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. -- -------------------------------------------------------------------------------------
sproc - , .
, "- , ". , NOT , , SEQUENCE , , , NEXTVAL , .
, , , , - " ", .
, , MAX() - , -. ( , ...).
, INVOICE , -. - , -, , , " -", , INVOICE, (INVOICE_NO) ( UNIQUE), , - INVOICE , INVOICE - .: -)
.
, , . :
. , "" - , , , "" "" .
, NOCACHE , , , "void" .
, . , - ( , ), . , , , - , "".
, / , , , .
The dpbradley link in # 2 sounds like your best bet. The volume saves the transaction with the caller if you do not want you to make it a stand-alone transaction as follows:
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 the transaction sequence number, and then when the item being processed is completed, we issue a constant number (also a sequence). Works well for us.
RegardsK