Re: create sequence in PL/SQL anonymous block - needs to be in separa

From: Michael Netrusov <mn_at_g-fax.com>
Date: Tue, 04 Sep 2001 16:14:51 -0700
Message-ID: <F001.0038296A.20010904162542@fatcity.com>

well,
 
 declare
      i pls_integer;
 
 begin
 
     execute immediate 'create

sequence temporary_sequence_s';     
     for i in 1..1000 loop

 
        execute

immediate 'insert into t( n ) values( temporary_sequence_s.nextval )'; 
        
     end loop;
 
     commit;
 
     execute immediate 'drop

sequence temporary_sequence_s';       end;

 

HTH, Michael www.atelo.com

• Original Message ----- <BLOCKQUOTE dir=ltr style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: <A title=Jacques.Kilchoer_at_quest.com href="mailto:Jacques.Kilchoer_at_quest.com">Jacques Kilchoer To: <A title=ORACLE-L_at_fatcity.com href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L

  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) <FONT 

  size=2>  8       values <FONT
  size=2>  9        
  (temporary_sequence_s.nextval) ; <FONT

  size=2> 10     end loop ; <FONT 
  size=2> 11     commit ; <FONT 
  size=2> 12     execute immediate 'drop sequence 

  temporary_sequence_s' ;  13  end ;
   14  / <FONT
  size=2>       (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é <FONT   size=2>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 ; <FONT
  size=2> 10     commit ; <FONT
  size=2> 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 <FONT   size=2>SQL> select * from user_sequences ;   aucune ligne sélectionnée Received on Tue Sep 04 2001 - 18:14:51 CDT