Re: Dynamic SQL in Stored Procedures

costello_at_clark.net (Matthew C. Costello) wrote:
> 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.

This is possible through the Oracle supplied package called DBMS_SQL. Two references showing how to use the package to execute dynamic SQL are:

1. "Oracle7 Server Documentation Addendum, Release 7.1", Chapter 7. This is Oracle part #A12042.
2. "Oracle PL/SQL Programming", by Steven Feuerstein, published by O'Reilly & Associates, September 1995, Chapter 15.

I prefer the second reference but both give adequate examples. I believe DBMS_SQL was first introduced with Oracle 7.1. I hope this helps.

• Andy Zitelli