SQL & PL/SQL

Hi Everyone,

After data migration i have to reset all oracle sequences to do this i used below procedure, it is working fine for one time,

Actually in one weekend we are migrating some set of data

Then after migration when i execute procedure it will reset all oracle sequences

After that application team /End users will insert/edit the data .

Next weekend we are migrating some more set of data into destination tables,

When i execute the procedure this is not set to correct sequnce value

procedure:

CREATE OR REPLACE PROCEDURE SP_test_dyn

As

SQLSTR1 varchar(40);

SQLSTR2 varchar(40);

SQLSTR3 varchar(40);

SQLSTR4 varchar(40);

max_val_incol number:=0;

max_val_currval number:=0;

q1 varchar(1000);

q2 varchar(1000);

begin

for c in ( select TABLE_NAME, SHORT_PK_SEQ_NAME, PK_NAME from M_BAE_DYNAMIC_DRIVER_tst )

loop

SQLSTR1:=c.TABLE_NAME;

SQLSTR2:= upper(TRIM(SQLSTR1))||'_ID';

SQLSTR3:= upper(TRIM(SQLSTR1))||'_ID_SEQ';

SQLSTR4:=SQLSTR3||'.NEXTVAL';

EXECUTE IMMEDIATE 'SELECT NVL(MAX ('||SQLSTR2||' ),1) FROM ' || SQLSTR1　 INTO　 max_val_incol;

q1 := 'ALTER SEQUENCE ' ||　 SQLSTR3 ||' INCREMENT BY ' ||max_val_incol;

　　 EXECUTE IMMEDIATE q1;

eXECUTE IMMEDIATE 'SELECT '|| SQLSTR4　 || ' FROM DUAL' INTO　　　 max_val_currval ;

q2 := 'ALTER SEQUENCE ' ||　 SQLSTR3 ||' INCREMENT BY 1';

　　 EXECUTE IMMEDIATE q2;　

　　　　end loop;

END;

example:

table : M_BAE_DYNAMIC_DRIVER_tst which will store table names, pk id, oracle sequencer name :

select * from M_BAE_DYNAMIC_DRIVER_tst ;

TABLE_NAME      PK_NAME           SHORT_PK_SEQ_NAME

----------------     --------------                ------------------------------

DYN_TEST      DYN_TEST_ID           DYN_TEST_ID_SEQ

i am migrating data into table : DYN_TEST, column_name: DYN_TEST_ID,

for this oracle sequence name: DYN_TEST_ID_SEQ

initaily one weekend for example i migrated 8 records

SQL> select * from dyn_test;

DYN_TEST_ID

-----------

1

2

3

4

5

6

7

8

then using above procedure it will reset oracle sequence

SQL> select DYN_TEST_ID_SEQ.nextval from dual;

NEXTVAL

----------

9

then application team insert a record 20 into this table, when i execute the procedure , it should reset to 21 but i am getting this as 30

SQL> select DYN_TEST_ID_SEQ.nextval from dual;

NEXTVAL

----------

30

i want procedure to return max_value+1 this value i have to set to oracle sequencer if i execute procedure n no of times but it should retun max_value of DYN_TEST_ID +1.

Please advise on this.