Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Calling procedures from PL/SQl functions (called from a SQL stmt)
It is probable that you mistakely used select statement when you meant to
use execute statement.
Simply replace the select statement with the following
execute myfunc(parent);
A select statement is to query data for a given point in time, and
should not alter it. Therefore
a select statement should never invoke a function that issues insert/delete
or update commands.
Future SQL standards will recommend that stored functions specify if they can modify data or not (much the same as in C++ does in classes by specifiy the function as const). This way you would receive a clearer warning of what you were doing was probably wrong.
Ciao...
Ananth Rani wrote in message <368D0428.4A78F5EC_at_ix.netcom.com>...
>Hi,
>
>This is a bit convoluted but:
>
>- I am using an application that can only execute SQL statements
>- The SQL statement can call user defined functions
>- The function in turn can call procedures
>
>However, I noticed that the procedure that is called cannot do any
>updates, inserts or deletes. Is there a trick to get past this ?
>
>select myfunc(parent) from dual;
>
>Function myfunc
>.....
>bom_exploder(parent) <---- this proc does a BOM explosion which inserts
>
>records into a temp table.
>
>End;
>
>Thanks,
>AR
>
>
Received on Mon Jan 04 1999 - 14:34:18 CST