create sequence in PL/SQL anonymous block - needs to be in separa

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 04 Sep 2001 15:26:53 -0700
Message-ID: <F001.003828CB.20010904153031@fatcity.com>

Using Oracle 8.1.6 on Windows 2000

I try to use EXECUTE IMMEDIATE to create a sequence in an anonyms PL/SQL block, and then immediately use it in an INSERT statement. The INSERT statement fails saying "sequence does not exist." However, if I create the sequence in a separate PL/SQL anonymous block immediately before the anonymous PL/SQL block containing the insert, there is no error. A DROP SEQUENCE that follows the insert is successful.

Any suggestions?

SQL> -- Creating sequence in same anonymous block:
SQL> -- Insert statement doesn't recognize the
SQL> -- sequence name
SQL> declare


  2     i pls_integer ;

  3  begin

  4     execute immediate 'create sequence temporary_sequence_s' ;

  5     for i in 1..1000

  6     loop

  7       insert into t (n)

  8       values

  9         (temporary_sequence_s.nextval) ;

 10     end loop ;

 11     commit ;

 12     execute immediate 'drop sequence temporary_sequence_s' ;

 13  end ;

 14  / 

       (temporary_sequence_s.nextval) ;
        *

ERREUR à la ligne 9 :

ORA-06550: Ligne 9, colonne 9 :
PLS-00201: l'identificateur 'TEMPORARY_SEQUENCE_S.NEXTVAL' doit être déclaré
ORA-06550: Ligne 7, colonne 6 :

PL/SQL: SQL Statement ignored

SQL> -- When creating the sequence in a separate block,
SQL> -- I see no error message
SQL> begin


  2     execute immediate 'create sequence temporary_sequence_s' ;

  3  end ;
  4  /

Procédure PL/SQL terminée avec succès.

SQL> declare
  2     i pls_integer ;
  3  begin
  4     for i in 1..1000
  5     loop
  6       insert into t (n)
  7       values
  8         (temporary_sequence_s.nextval) ;
  9     end loop ;
 10     commit ;
 11     execute immediate 'drop sequence temporary_sequence_s' ;
 12  end ;
 13  /

Procédure PL/SQL terminée avec succès.

SQL> -- please note that 'execute immediate drop sequence'
SQL> -- was successful
SQL> select * from user_sequences ;

aucune ligne sélectionnée Received on Tue Sep 04 2001 - 17:26:53 CDT