November 2016

Re-set the value of an Oracle sequence

Sometimes it is necessary to re-set the value of an Oracle sequence: DECLARE PROCEDURE set_sequence_value (p_sequence_owner IN VARCHAR2, p_sequence_name IN VARCHAR2, p_new_value IN NUMBER) IS l_reduce_by NUMBER; l_last_number NUMBER; l_seq_val NUMBER; BEGIN SELECT last_number INTO l_last_number FROM dba_sequences s WHERE sequence_owner = p_sequence_owner AND sequence_name = p_sequence_name; dbms_output.put_line(‘last number: ‘||l_last_number); IF l_last_number != p_new_value THEN […]

Read more →