I'm not sure if what you've posted is just an example,
but why not just do
insert into t select rowum from sys.source$ where
rownum < 1000;
It's a lot quicker and easier
hth
connor
- Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
wrote: > 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
>
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 Wed Sep 05 2001 - 04:04:12 CDT
