Solución:
- Siempre se puede hacer que la opción 1 falle de alguna manera en un entorno con usuarios concurrentes.
- La opción 2 funcionará, pero limitará la escalabilidad: referencia obligatoria de Tom Kyte: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1508205334476
Como recomienda, realmente debería revisar la necesidad del requisito de “sin espacios”.
Los espacios aparecen si una transacción usa un número de secuencia pero luego se revierte.
Quizás la respuesta sea no asignar el número de factura hasta que la factura no se pueda revertir. Esto minimiza (pero probablemente no elimina) las posibilidades de brechas.
No estoy seguro de que haya una manera rápida o fácil de garantizar que no haya espacios en la secuencia: escanear para MAX, agregar uno e insertar que sea probablemente lo más cercano a la seguridad, pero no se recomienda por razones de rendimiento (y dificultades con la concurrencia). ) y la técnica no detectará si se asigna el último número de factura, luego se elimina y se reasigna.
¿Puede explicar las brechas de alguna manera, identificando qué números de factura se ‘usaron’ pero ‘no se hicieron permanentes’ de alguna manera? ¿Podría una transacción autónoma ayudar a hacer eso?
Otra posibilidad: asumiendo que las brechas son relativamente pocas y distantes entre sí.
Cree una tabla que registre los números de secuencia que deben reutilizarse antes de obtener un nuevo valor de secuencia. Normalmente, estaría vacío, pero algún proceso que se ejecuta cada … minuto, hora, día … busca huecos e inserta los valores perdidos en esta tabla. Todos los procesos primero verifican la tabla de valores perdidos y, si hay alguno presente, usan un valor de allí, pasando por el lento proceso de actualizar la tabla y eliminar la fila que usan. Si la mesa está vacía, tome el siguiente número de secuencia.
No es muy agradable, pero el desacoplamiento de ’emitir números de factura’ de ‘escanear en busca de valores perdidos’ significa que incluso si el proceso de facturación falla para algún hilo cuando está usando uno de los valores perdidos, ese valor será redescubierto como faltante y reeditado la próxima vez, repitiendo hasta que algún proceso tenga éxito con él.
Mantenga la secuencia actual: puede usar lo siguiente para restablecer el valor al máximo de lo que está almacenado actualmente en la (s) tabla (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.
-- -------------------------------------------------------------------------------------
El ejemplo es un sproc anónimo: cámbielo para que sea el procedimiento adecuado en un paquete y llámelo antes de insertar una nueva factura para mantener la numeración coherente.