Title: Automatically Adjustment The Sequences In User Schema

Author: Jason Jia, a DBA for InterTrans Logistics System in Toronto, Ontario.

Most of the time we define the primary key in the tables, and the name of the primary key is dependent on its sequence. For
example, we define a table named 'emp_t', the sequence for primary key named 'emp_tseq', and we retain this name
convention.

Every time we do database migration from one application version to the other, we must migrate data, then create sequences
by running scripts, then change each sequence curval to or higher than the maximum value of primary key to adjust all the
sequences defined in the system.

The problem is that there are so many sequences defined in some systems that it is not possible to check each sequence,
compare it with current primary key value, then adjust sequence manually. I created this script to adjust all the sequences
defined in the system dynamically if necessary.

REM ************************************************************************
REM    Name: adjst_seq.sql
REM    Creation Date: 13-APR-1998
REM    Description: Allows to adjust sequence value automatically.
REM    Database: Oracle7 Release 7.1 or higher.
REM    Tool: SQL*Plus 3.1 AND higher.
REM    Author: Jason Jia
REM
REM ************************************************************************

set echo off
set feedback off
set verify off
set showmode off
spool c:\temp_stats.sql

declare
cursor c1 is
 select a.sequence_name seq,
        b.table_name tab,
        b.column_name col,
        a.last_number cur_val
 from   user_sequences a,
        user_ind_columns b
 where  substr(a.sequence_name , 1,length(a.sequence_name)-3)=b.table_name
  and   b.index_name like 'PK_%';
c1_rec c1%rowtype;
    adj_value               NUMBER;
        s_value                 NUMBER;
        seq_num                 NUMBER;
        r1                      integer;
        cid                     INTEGER;
        sql_stmt                varchar2(100);

        PROCEDURE adj_seq
               (seq_name                VARCHAR2,
                cur_value               integer,
                new_value               integer)
        AS

        dummy1  integer;
        BEGIN
        if new_value > cur_value then
        dummy1:=new_value-cur_value+1;
                DBMS_OUTPUT.PUT_LINE('alter sequence '||seq_name||
                        ' increment by '||dummy1||';');


                DBMS_OUTPUT.PUT_LINE('select '||seq_name||'.nextval from dual;');

                DBMS_OUTPUT.PUT_LINE('alter sequence '||seq_name||
                        ' increment by 1;');
        end if;

        EXCEPTION
                WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(sqlerrm);
                RAISE_APPLICATION_ERROR(-20000,'Critical Error.  Exiting...');
        END;
begin

        /* pick up the sequences */
        open c1;
        loop
           fetch c1 into c1_rec;
           exit when c1%notfound;

                /* get current value for PK */

              sql_stmt := 'select NVL(MAX('||c1_rec.col||'),0) from '||c1_rec.tab;

              cid := DBMS_SQL.OPEN_CURSOR;
              DBMS_SQL.PARSE(cid, sql_stmt, dbms_sql.v7);

                   DBMS_SQL.DEFINE_COLUMN(cid, 1, s_value);
                   r1 := DBMS_SQL.EXECUTE_AND_FETCH(cid);
                   DBMS_SQL.COLUMN_VALUE(cid, 1, s_value);

              DBMS_SQL.CLOSE_CURSOR(cid);


        /* creating adjust sequence statement */
        adj_seq(c1_rec.seq,c1_rec.cur_val,s_value);

        end loop;
        close c1;



EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(sqlerrm);
end;
/
spool on
set echo on
set feedback on
set verify on
set showmode on
REM Adjusting ...
@"c:\temp_stats.sql"
host "del c:\temp_stats.sql"