Re: Execute Immediate DDL & DML

On Feb 9, 12:26 pm, "00steve" <steven_macl..._at_hotmail.com> wrote:
> 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:
>
> 1. get max() value of column name
> 2. set nextval of sequence
> 3. set increment value of sequence to difference between max() value
> and currval
> 4. nextval on sequence (with new increment value)
> 5. reset sequence to original increment value
>
> 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;
> END IF;
> END;
I have managed to fix this issue, as it turns out you must change the following line:

EXECUTE IMMEDIATE 'SELECT '||seq_name||'.NextVal FROM DUAL' ;

to:

EXECUTE IMMEDIATE 'SELECT '||seq_name||'.NextVal FROM DUAL' INTO val_1;

Making sure that val_1 is defined. I am unsure as to why reading the sequence value into a variable makes the difference. Received on Fri Feb 09 2007 - 09:07:37 CST