Re: Dynamic SQL variable problem

You are taking the wrong approach to determining whether the sequence exists. Certainly you could do this by wrapping the entire thing into a block with an exception handler. If the sequence doesn't exist ... create it in the exception handler.

But it would be a lot easier to just go check for whether it exists in ALL_SEQUENCES. SELECT COUNT(1)
INTO x
FROM all_sequences
WHERE sequence_name = 'FRD_ALERT';

IF x = 0 THEN

   EXECUTE IMMEDIATE 'CREATE SEQUENCE FRD_ALERT ....; END IF; Though I must add that this is no way to manage a schema, creating objects on the fly.

Daniel Morgan

"Daniel N." wrote:

> 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 - 12:13:29 CST