Re: Problem with dynamic SQL

i believe the function must be restricted to both "read no database state" and "write no database state" in order to use the function in the select list of an SQL statement.

e.g. pragma restrict ( <user_function>, RNDS, WNDS ) ;

"Abhijit Bhattacharya" <abhi_mita_at_yahoo.com> wrote in message news:3900AD0B.ED6EB1EA_at_yahoo.com...
> Here is my problem.
> I am trying to include an user defined function as part of an
 SQL
> statement. This user defined packaged function executes an SQL
 statement
>
> generated dynamically e.g. performance indicator C is made of
 A & B i.e.
>
> Select sum(A+B) from <table_name>. The definition of C changes
 over time
>
> and is picked from a definition table hence the need for
 dynamic SQL. I
> am using DBMS_SQL package to accomplish the task.
> As soon as I am try to put this function in my SQL statement,
 database
> generates error because of possible side effects in the called
 function.
>
> I tried even by putting
> PRAGMA RESTRICT_REFERENCES(<packaged function name>,WNDS) in
 package
> declaration, but still it doesn't work. So is it not possible
 to invoked
>
> an user defined packaged function containing DBMS_SQL calls
 from an SQL
> statements ?
> Any insight will be appreciated.
> Thanks
> Abhijit
>
>
>
>
Received on Fri Apr 21 2000 - 00:00:00 CDT