FixSurrogateIdSequence.sql

DECLARE

/*
    Banner XE Sequence Fixer (also see Banner XE Sequence Checker)
    This procedure attempts to fix the situation where a Banner SURROGATE_ID_SEQUENCE
    gets behind the max SURROGATE_ID that it creates values for.
    We fix it by selecting the sequence nextval in a loop until it's greater
    than the surrogate ID.
    
    This situation can happen when data rows are copied from one region to another
    along with the surrogate id column data.  (It can happen if an insert statment simply
    inserts data into the surrogate id as well.)
    
    An alternative fix is documented in the Ellucian support site under
    Article: 000030469
    Product Line: Banner
    Product: Banner Student
*/

schema_owner    VARCHAR(50);
table_name      VARCHAR(100);
seq_name        VARCHAR(100);
id_column_name  VARCHAR(100);
seq_number      int;
id_number       int;
diff_number     int;

BEGIN
    -- prompt for schema
    schema_owner := '&schema_owner';

    -- prompt for table name
    table_name := '&table_name';
    
    seq_name := schema_owner || '.' || table_name || '_SURROGATE_ID_SEQUENCE' ;
    id_column_name := table_name || '_SURROGATE_ID' ;
    
    -- Get the sequence value and the max surrogate id as they stand now
    EXECUTE IMMEDIATE 'select ' || seq_name || '.nextval from dual' into seq_number;
    EXECUTE IMMEDIATE 'select max(' || id_column_name || ') from ' || table_name into id_number;
    
    -- Determine if we need to fix the sequence
    -- NOTE: There is a small possibility that we just fixed the sequence
    -- with the initial nextval call
    diff_number := id_number - seq_number;
    DBMS_OUTPUT.PUT_LINE('Id = ' || id_number || '   Seq = ' || seq_number || '   Diff = ' || diff_number);    -- debug

    if diff_number > 0 then
        DBMS_OUTPUT.PUT_LINE('Fixing ' || seq_name);    -- debug

        -- If the value from the nextval query is not greater than the max(TABLE_surrogate_id)
        -- then the sequence will need to be adjusted.
        -- This is done in the following loop avoiding the need for alter sequence privilege.

        while diff_number >= 0 loop
            execute immediate 'select ' || seq_name || '.nextval from dual' into seq_number;
            --DBMS_OUTPUT.PUT_LINE(seq_number);    -- debug
            diff_number := diff_number - 1;
        end loop;
        
        -- Get the new sequence value and output all the values
        execute immediate 'select ' || seq_name || '.currval from dual' into seq_number;
        
        diff_number := id_number - seq_number;
        
        DBMS_OUTPUT.PUT_LINE('Fixed ' || seq_name ||':  Id = ' || id_number || '   Seq = ' || seq_number || '   Diff = ' || diff_number);    -- debug

    else
        DBMS_OUTPUT.PUT_LINE('No fix needed for ' || seq_name);

    end if;
    
END;

PL/SQL script for fixing Banner XE surrogate id sequences should the surrogate id get ahead of the sequence generator.

The original version used the alter sequence increment method. Later revisions use the nextval loop method to work around the need to have alter access on the sequence.