Oracle – Sync Any Sequence And Any Table Column In A Generic Fashion To Avoid ORA-00001

Table to Sequence relation

Typically, you have tables that rely on a sequence for their PK. Almost all of the time the relationship between a table and its sequence is one-to-one.

Often times, as releases are done with manual data inserts, not too much attention is paid to use the value from the sequence and so, the sequence and the table data get out out sync.

You are special. You get Oracle’s very first error – ORA-00001 !!

Later when a new release comes in and tries to insert data the right way using the sequence, it runs into this error!

SQL> @C:\~Tmp\MyApp\3_InsertData.sql
Insert into MY_SCHEMA_NAME.MY_DATA_TABLE (ID, REF_TYPE, REF_DESCRIPTION)
*
ERROR at line 1:
ORA-00001: unique constraint (MY_SCHEMA_NAME.MY_DATA_TABLE_PK) violated

Solution:

All you need to do is to increase the sequence value to be one above the current MAX value of the table’s ID value that uses the sequence. Usually, you just use

ALTER SEQUENCE MY_SEQ INCREMENT BY [X]

Where X is the value you determined to be 1 above the largest value of the table’s ID column.

Generic solution for any table or sequence combination:

I get tired of doing a new script for every new table. So, when I was a developer, I created a generic script years ago that I keep re-using and sharing with others for any new table/sequence combinations. Please adapt it to fit your needs but you should get the general idea quickly. If not, please comment below and I will respond as soon as I get a chance.

Please connect in SQL*Plus before you run what the script
connect my_id/my_pwd@my_db

-- ###########################################################################
-- # TITLE......              :  Generic_Sequence_Sync.sql
-- # LOCATION...              :  Ora_Generic/OTR_Scripts
-- # PARAMETERS
-- #    /INPUTS.: None
-- # USAGE......              :  Using SQLPlus run @/file_path/Generic_Sequence_Sync.sql
-- # OUTPUTS....              :  None
-- # DESCRIPTION/FUNCTION     :
-- #                             Sync's the sequence with the table value if necessary
-- # NOTES......              :
-- # AUTHOR.....              : Jana Sattainathan
-- # Total Execution Time     : A matter of seconds.
-- # Modifications            :
-- #-Date----------Author and Description----------------------------------
-- #MM/DD/YY      Name       description
-- #09/15/10     Jana S        Initial creation
-- ############################################################################

--connect sys/&sys_pwd_dwms@&dwdbname

set serveroutput on

spool c:\temp\sync_sequence.log

prompt
prompt Generic sequence sync script to sync sequence values to latest table values
prompt ***********************************************************************o
prompt Sometimes sequences go out of sync with the table data usign the sequence
prompt          (may be due to people manually entering values for the seq column),
prompt          Given the appropriate table + sequence details, this script, syncs the seq.
prompt
PAUSE 'Press ENTER to continue OR CNTL+C to exit!'

accept table_owner char prompt 'Enter table_owner using sequence ==> ' DEFAULT NONE
accept table_name char prompt 'Enter table_name using sequence ==> ' DEFAULT NONE
accept table_seq_column_name char prompt 'Enter the table column name that uses the sequence ==> ' DEFAULT NONE
accept seq_owner char prompt 'Enter owner name of sequence ==> ' DEFAULT NONE
accept seq_name char prompt 'Enter name of sequence ==> ' DEFAULT NONE

DECLARE
    --Error Handling
    v_ProcName                  VARCHAR2(50) := 'Generic_Sequence_Sync.sql(): ';

    v_err_cd                    NUMBER;                     --Error code SQLCODE
    v_sqlerrm                   VARCHAR2(1024);               --Error message SQLERRM
    v_errm_generic              VARCHAR2(255) DEFAULT '';    --Generic error message for this function/procedure
    v_msg_cur_operation         VARCHAR2(1024);             --Holds the message that identifies the specific operation in progress        

    v_Table_Owner       VARCHAR2(30) := UPPER('&table_owner');
    v_Table             VARCHAR2(30) := UPPER('&table_name');
    v_Seq_Column        VARCHAR2(30) := UPPER('&table_seq_column_name');

    v_Seq_Owner         VARCHAR2(30) := UPPER('&seq_owner');
    v_Seq_Name          VARCHAR2(30) := UPPER('&seq_name');
    v_Seq_Increment_By  NUMBER := 0;
    v_Seq_Cache_Size      NUMBER := 0;

    v_SQL        VARCHAR2(1024);
    v_Max_Table_Value   NUMBER;
    v_Max_Seq_Value     NUMBER;
    v_Difference        NUMBER;
    v_Dummy             NUMBER;

BEGIN
    v_errm_generic := 'Parameters v_Table_Owner: ' || v_Table_Owner || ', ' ||
                                'v_Table: ' || v_Table || ', ' ||
                                'v_Seq_Column: ' || v_Seq_Column || ', ' ||
                                'v_Seq_Owner: ' || v_Seq_Owner || ', ' ||
                                'v_Seq_Name: ' || v_Seq_Name;

    --Getting the latest value from the sequence
    ---------------------------------------------------------------------------------
    v_msg_cur_operation := 'Getting latest value from sequence';
    DBMS_OUTPUT.PUT_LINE(v_ProcName|| v_msg_cur_operation);

    SELECT
        NVL(MAX(last_number), -1),
        MAX(increment_by),
        MAX(cache_size)
    INTO
        v_Max_Seq_Value,
        v_Seq_Increment_By,
        v_Seq_Cache_Size
    FROM
        dba_sequences
    WHERE
        sequence_name = UPPER(v_Seq_Name)
       AND sequence_owner = UPPER(v_Seq_Owner);

    --Validate
    ---------------------------------------------------------------------------------
    IF v_Max_Seq_Value = -1 THEN
        v_err_cd    := -20100;         --<<REVISIT : To renumber error numbers properly>>
        v_sqlerrm   := v_ProcName || 'Either sequence does not exist/sequence name misspelled/sequence not been initialized..Please verify.';
        RAISE_APPLICATION_ERROR(v_err_cd, v_sqlerrm);
    END IF;

    DBMS_OUTPUT.PUT_LINE(v_ProcName|| 'MAX sequence value: ' || TO_CHAR(v_Max_Seq_Value));

    --Getting the latest value from the table
    ---------------------------------------------------------------------------------
    v_msg_cur_operation := 'Getting latest value from table';
    DBMS_OUTPUT.PUT_LINE(v_ProcName|| v_msg_cur_operation);

    v_SQL := 'SELECT NVL(MAX( ' || v_Table_Owner || '.' || v_Table || '.' || v_Seq_Column || '), -1)
                FROM ' || v_Table_Owner || '.' || v_Table;

    EXECUTE IMMEDIATE v_SQL INTO v_Max_Table_Value;
    DBMS_OUTPUT.PUT_LINE(v_ProcName|| 'MAX table value: ' || TO_CHAR(v_Max_Table_Value));

    --Validate
    ---------------------------------------------------------------------------------
    IF v_Max_Table_Value = -1 THEN
        v_err_cd    := -20100;         --<<REVISIT : To renumber error numbers properly>>
        v_sqlerrm   := v_ProcName || 'Either table does not exist/table name misspelled/table has no data..Please verify.';
        RAISE_APPLICATION_ERROR(v_err_cd, v_sqlerrm);
    END IF;

    IF (v_Max_Table_Value <= v_Max_Seq_Value) THEN
        DBMS_OUTPUT.PUT_LINE(v_ProcName|| 'The sequence value is already equal to or greater than the max value in table. Sync is not needed');
    ELSE
        --Calculate the difference
        ----------------------------
        v_Difference := v_Max_Table_Value - v_Max_Seq_Value;
        DBMS_OUTPUT.PUT_LINE(v_ProcName|| 'Difference between sequence value and table value: ' || TO_CHAR(v_Difference));

        v_Difference := v_Difference + v_Seq_Cache_Size;
        DBMS_OUTPUT.PUT_LINE(v_ProcName|| 'Difference after sequence cache size is factored in: ' || TO_CHAR(v_Difference));        

        --Alter sequence to increment by the difference
        ----------------------------
        v_SQL := 'ALTER SEQUENCE ' ||  UPPER(v_Seq_Owner) || '.' ||  UPPER(v_Seq_Name) || ' INCREMENT BY ' || TO_CHAR(v_Difference); 

        EXECUTE IMMEDIATE v_SQL;
        DBMS_OUTPUT.PUT_LINE(v_ProcName|| 'Sequence incremented by difference');

        --Get .NEXTVAL so that we can display and increment again after cache was accounted for
        ----------------------------
        v_SQL := 'SELECT ' || UPPER(v_Seq_Owner) || '.' ||  UPPER(v_Seq_Name) || '.NEXTVAL FROM DUAL';
        EXECUTE IMMEDIATE v_SQL INTO v_Dummy;

        DBMS_OUTPUT.PUT_LINE(v_ProcName|| 'New MAX Sequence value: ' || TO_CHAR(v_Dummy));

        --Alter sequence to increment by the original value
        ----------------------------
        v_SQL := 'ALTER SEQUENCE ' ||  UPPER(v_Seq_Owner) || '.' ||  UPPER(v_Seq_Name) || ' INCREMENT BY ' || TO_CHAR(v_Seq_Increment_By); 

        EXECUTE IMMEDIATE v_SQL;
        DBMS_OUTPUT.PUT_LINE(v_ProcName|| 'Sequence increment reset back to normal');

    END IF;

    --All done
    ---------------------------------------------------------------------------------
    v_msg_cur_operation := 'Completed procedure';
    Writer.Write(Writer.c_WRITER_DEFAULT || Writer.c_WRITER_DEBUG, v_ProcName|| v_msg_cur_operation);

EXCEPTION
    WHEN OTHERS THEN
        v_sqlerrm := SUBSTR(
                            v_ProcName || ': ' || 'Unable sync sequence. ' ||
                            CHR(13) || 'When: ' || v_msg_cur_operation ||
                            CHR(13) || 'For: ' || v_errm_generic ||
                            CHR(13) || SQLERRM
                        , 1, 1024);
        RAISE_APPLICATION_ERROR(-20205, v_sqlerrm);
END;
/
spool off

How to use it?

Save off the above code to a location like
c:\Oracle\Ora_Generic\OTR_Scripts\Generic_Sequence_Sync.sql

Once done, get to the commandline and start SQL*Plus and connect to your DB
connect my_id/my_pwd@my_db

Then, just run the script
@c:\Oracle\Ora_Generic\OTR_Scripts\Generic_Sequence_Sync.sql

You will be prompted for the owner and object names for both the Sequence and the Table that need to be synchronized. You are all set!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s