RE: create sequence in PL/SQL anonymous block

From: Djordje Jankovic <djankovic_at_corp.attcanada.ca>
Date: Tue, 04 Sep 2001 16:49:11 -0700
Message-ID: <F001.003829DF.20010904165550@fatcity.com>

PL/SQL has to be compiled first. Compilation does not take "contents" into account, as such does not take into account the statement to create a sequence, and so for the compiler the sequence does not exist.

Djordje

-----Original Message-----

To: Multiple recipients of list ORACLE-L Sent: 9/4/01 7:30 PM
separa

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Djordje Jankovic
INET: djankovic_at_corp.attcanada.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Sep 04 2001 - 18:49:11 CDT

RE: create sequence in PL/SQL anonymous block - needs to be in se