Re: Problem with dynamic SQL

Yes, Oracle checks that the pl/sql function does not violate the declared restrictions.

In order to include a user-defined PL/SQL function in the SELECT list, the function must neither READ nor WRITE database state.

Perhaps you could try resolving the definition of the measure in a separate step, prior to creating your desired SQL statement.

"Abhijit Bhattacharya" <abhi_mita_at_yahoo.com> wrote in message news:3904708E.9893E05F_at_yahoo.com...
> 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