I hope you are not really going to do this. Creating a column and then
dropping it is a really bad idea.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Adam C" <adam_at_ddisolutions.com.au> wrote in message
news:8bdc35cd.0306242110.6d979c6c_at_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 - 08:47:55 CDT
