Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Execute Immediate DDL & DML

Execute Immediate DDL & DML

From: 00steve <steven_macleod_at_hotmail.com>
Date: 9 Feb 2007 04:26:35 -0800
Message-ID: <1171023995.053735.291460@p10g2000cwp.googlegroups.com>


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; Received on Fri Feb 09 2007 - 06:26:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US