Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Execute Immediate DDL & DML
Hi, I am using the code below in attempting to create a procedure
which resets sequence values to the highest value of a column (+1).
The basic chain of events is as follows:
When I do this using standard SQL it works fine. When I attempt to do it in a PL/SQL I find that the sequence is only incremented by the original increment value, and not the newly altered one. Code is posted below, thanks for any help with this:
CREATE OR REPLACE procedure resetSeq
( table_name IN VARCHAR2, col_name IN VARCHAR2, seq_name IN
VARCHAR2 )
IS
max_val number(10);
next_seq_value NUMBER;
seq_increment NUMBER;
difference NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT max('||col_name||'+1) FROM '||table_name
INTO max_val;
EXECUTE IMMEDIATE 'SELECT '||seq_name||'.NextVal FROM DUAL' INTO next_seq_value;
difference := max_val-next_seq_value;
IF difference >=1 THEN
SELECT Nvl(INCREMENT_BY,1) INTO seq_increment FROM user_sequences WHERE SEQUENCE_NAME = Upper(seq_name);
EXECUTE IMMEDIATE 'ALTER SEQUENCE '||seq_name||' INCREMENT BY '||To_Char(difference)||' ';
EXECUTE IMMEDIATE 'SELECT '||seq_name||'.NextVal FROM DUAL'; EXECUTE IMMEDIATE 'ALTER SEQUENCE '||seq_name||' INCREMENT BY '||seq_increment||' '; COMMIT;
![]() |
![]() |