Re: Problem with dynamic SQL

I tried with RNDS also . Still PL/SQL generates compilation error indicating -
Subprogram voilates its associated pragma.

spencer wrote:

> 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 Mon Apr 24 2000 - 00:00:00 CDT