Is it a function or procedure? There are restrictions on calling
PL/SQL function from SQL, like you can not use DDL or DML. Read ORACLE
DOC on the restrictions and requriements.
adam_at_ddisolutions.com.au (Adam C) 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 Thu Jun 26 2003 - 14:18:48 CDT
