Sent: Tuesday, September 04, 2001
16:30
Subject: create sequence in PL/SQL
anonymous block - needs to be in 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
