Dynamic SQL method 2

From: Adam C <adam_at_ddisolutions.com.au>
Date: 24 Jun 2003 22:10:44 -0700
Message-ID: <8bdc35cd.0306242110.6d979c6c@posting.google.com>

Hi All {Platform Win2kPro and Oracle8i),

I am having some problems with executing dynamic sql from within a function. If i copy the code into SQL*Plus it executes and inserts a record, however when I run this from the function it executes (no exception) and DOES NOT insert a record! Here is an example of the code that I am using:

BEGIN

	EXECUTE IMMEDIATE 
	'ALTER TABLE MULTIDATAREQUESTREGION ADD (TEMP NUMBER DEFAULT 0 )';
	--CREATE COPIES OF THE MULTI DR REGION INFO  	  
	EXECUTE IMMEDIATE 
	'INSERT INTO MULTIDATAREQUESTREGION (DRID, REGIONID, TEMP)  		
	SELECT 
	  MDR.DRID,
	  MDR.REGIONID,
	  1
	FROM 
	  MULTIDATAREQUESTREGION MDR,
	  DATAREQUESTS DR,
	  INDICATORS I,
	  SUBCRITERION SC,
	  CRITERION C
	WHERE 


	  ( (MDR.DRID = DR.DRID) AND

	  (DR.INDICATORID = I.INDICATORID) AND

	  (I.SUBCRITERIONID = SC.SUBCRITERIONID) AND

	  (SC.CRITERIONID = C.CRITERIONID) AND

	  (C.PERIODID = :PID) )' USING 1;


        --DO MORE PROCESSING HERE

        EXECUTE IMMEDIATE 
        'ALTER TABLE MULTIDATAREQUESTREGION DROP COLUMN TEMP';

END; As you can see from the code, I first create a temp field that is used to identify the new reocrds, then I insert duplicate(s) of existing record(s) (hence the need to identify the new record(s)). I do some further manipulation and finally I drop the temp column.

If anyone has any suggestions as to the cause of my problem it would be greatly appreciated.

AdamC Received on Wed Jun 25 2003 - 00:10:44 CDT