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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
