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

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 05 Sep 2001 06:11:02 -0700
Message-ID: <F001.0038322A.20010905052637@fatcity.com>

like Connor said, why use a sequence? you have a perfectly good sequence number in the loop index.
why waste the time of creating and dropping a sequence?

change your proc to:

> SQL> declare
> 2 i pls_integer ;
> 3 begin
> 5 for i in 1..1000
> 6 loop
> 7 insert into t (n)
> 8 values
> 9 (i) ; <=============== the only thing I changed.
> 10 end loop ;
> 11 commit ;
> 13 end ;
> 14 /

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, September 05, 2001 5:23 AM To: Multiple recipients of list ORACLE-L separa

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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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 - 08:11:02 CDT