ORA-00600 Dynamic SQL Problem...

From: tIM <tjm_at_geac.dk>
Date: 15 Oct 2001 07:09:05 -0700
Message-ID: <b1f8412c.0110150609.14191512@posting.google.com>

Hi!

Here is what I am trying to do in words... I loop through table that has a default supplier field containing 1,2,3,4 or 5..
If the value is 1 I want field supplier1 if the value is 2 I want supplier2.

While typing this I just thoght of using decode... So I'll give this a try but

I still want to post this cause it could be a bug on 9i NT?

you can use this code to reproduce the error 00600:

   DROP TABLE TEMP;
   DROP TABLE TEMP_TEMP;        CREATE GLOBAL TEMPORARY TABLE TEMP_TEMP (xxx number(1));    CREATE TABLE TEMP (xxx number(1));
   INSERT INTO TEMP VALUES (1);
   INSERT INTO TEMP VALUES (2);        DECLARE
           l_sql varchar2(400);
   BEGIN

   	 for x in (select xxx from temp)
   	 loop
   	 	l_sql:='INSERT INTO TEMP_TEMP (xxx) values (:x.xxx)';
   	 	EXECUTE IMMEDIATE l_sql;
   	 	
   	 end loop;

   END;     the reason I need to do this is stated above again here is the real code I was trying to do... Does anyone else get this error on other platforms?

for x in (select ST.KEY1,ST.KEY2,ST.KEY5,

 	       ST.MOM001,ST.MOC038,ST.PICTMODA01,
 	       ST.MOC010,MA.MOC010TXT,ST.MOC007,ST.RECNO,
 	       ca.defsup,CA.Supplier1,ca.supplier2,ca.supplier3,ca.supplier4,
               ca.supplier5,ca.article,ca.quality1,ca.quality2,ca.quality3,ca.quality4,
 	       ca.quality5,ca.color,'N' BEH
 	       FROM STYLES ST,MAINSTNO MA,CATALOG CA  
 	       WHERE ST.MAINKEY=p_mainkey
	       AND ST.MOC010=MA.MOC010(+)
               AND ST.MAINKEY=CA.MAINKEY(+)
               order by ca.lineno)
   loop
	        
	
		l_sql:='INSERT INTO TEMP_INTEGRATION_OUT (StyleNo,Program,
	        	Season,Description,ProdGroup,StylePict,SizeRangeCode,SizeRange,
	       	 	Mark,Quality ,Supplier,Color,recno,beh,intdate) 
     			values (:x.key1,:x.key2,:x.key5,:x.mom001,:x.moc038,:x.pictmoda01,
     		:x.moc010txt,:x.moc010,:x.moc007
,:x.quality'||to_char(x.defsup)||
     		',:x.supplier'||to_char(x.defsup)||',:x.color,
:x.recno,:x.beh,sysdate)';
     		
     		insert into temp values(l_sql);
	      	EXECUTE IMMEDIATE  l_sql;

   end loop; Received on Mon Oct 15 2001 - 09:09:05 CDT