Re: Dynamic SQL

In article <3900A09B.1326E141_at_yahoo.com>,   Abhijit Bhattacharya <abhi_mita_at_yahoo.com> wrote:
> 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
>
>

won't work (until Oracle8i release 8.1 with autonomous transactions). PLSQL called from SQL must promise to not update the database. dbms_sql specificially does not (and cannot) make this promise.

In anycase -- why not, instead of INSERTING the definition of C into a table, create or replace a VIEW that selects the correct definition of C. That way -- you can use static sql and perhaps avoid having to call plsql from SQL alltogether.

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.