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
