Re: Dynamic SQL in Stored Procedures

In article <48obd8$ela_at_inet-nntp-gw-1.us.oracle.com>,

   surman_at_oracle.com (Scott Urman) wrote:
>In article <48geik$c61_at_clarknet.clark.net>, costello_at_clark.net (Matthew C.
 Costello) writes:
>|> Is it possibe to define a select inside a Stored Procedure (using some
>|> input parameters and string concatenation) and then execute it within
>|> the procedure?
>|>
>|> I'm using 7.1.3 Workgroup/NT and it seems that pl/sql only wants
>|> statically defined SQL.
>|>
>|> Thanks!
>|>
>Yep. Check out the DBMS_SQL package, documented in the Oracle7 Server
>Documentation Addendum and online in dbmssql.sql.

One catch. It seems that a call to any of the DBMS_SQL functions causes a change in the state of the database. The result? You can't call the function from within the WHERE clause of a SELECT statement. And NO, you can't use the RESTRICT_REFERENCES pragma to overcome this because doing this results in the following when creating your package body:

PLS-00452: Subprogram '<function name>' violates its associated pragma

If you figure out how to solve this one, please let me know.

• Robert