Dynamic SQL variable problem

From: Daniel N. <daniel.navarro_at_intervoice-brite.com>
Date: 4 Feb 2002 09:34:22 -0800
Message-ID: <2a49a9a4.0202040934.56d59e10@posting.google.com>

All,

I am having a problem trying to modify a sequence, and if it does not exist then I would like to create it using Dynamic SQL. My code checks to see if the sequence exists, if it does then I would like to store it's current value, modify it's MIN and MAX and then start with the previous stored value. The code works fine if the sequence already exists, but if it doesn't, Oracle gives me an error message like this:

   ERROR at line 31:

   ORA-06550: line 31, column 12:
   PLS-00201: identifier 'FRD_ALERT_SEQ.NEXTVAL' must be declared
   ORA-06550: line 31, column 5:

   PL/SQL: SQL Statement ignored

I have narrowed down to this statement:

"select frd_alert_seq.NEXTVAL

   INTO old_seq_value FROM dual';"

It seems Oracle needs to initialize this variable before the code can run, but how to initialize the variable if the sequence does not exist?

My (modified) code follows:

create or replace procedure CS
is
  old_seq_value NUMBER(10);

  seq_var          NUMBER(1);
  dbname           VARCHAR2(30);
  string           VARCHAR2(256);

BEGIN
  dbms_output.ENABLE;

  SELECT global_name INTO dbname FROM global_name;   SELECT count(*) INTO seq_var FROM user_sequences     WHERE sequence_name = 'A_SEQ';

  IF (dbname = 'TEST.WORLD') AND (seq_var = 1)   THEN

        string:= 'SELECT a_seq.NEXTVAL INTO old_seq_value FROM dual';
        execute immediate string;

        string := 'DROP SEQUENCE a_seq';
        execute immediate string;

        string := 'CREATE SEQUENCE a_seq INCREMENT BY 1 START WITH '
                || to_char(old_seq_value) 
                || ' MINVALUE 1 MAX VALUE 200000000 CYCLE NOCACHE';
        execute immediate string;

  ELSE
    string:= 'CREATE SEQUENCE a_seq INCREMENT BY 1

             START WITH 1 MINVALUE 1 MAXVALUE 200000000 CYCLE NOCACHE';     execute immediate string;
  END IF;   EXCEPTION

        WHEN OTHERS THEN
        RAISE;

END;
/

Any help would be appreciated,
Dan N. Received on Mon Feb 04 2002 - 11:34:22 CST