Re: Dynamic PL/SQL ?

On 22 Jan 2002 12:06:47 GMT, turnergw_at_logica.nojunk.com (Graham Turner) wrote:

>
>I need to produce a stored procedure that is capable of reading data from one
>of ten tables created dynamically by a system, but which may or may not exist
>at the time of installation of the procedure.
>
>One of the arguments passed in is an ID which indicates the table : I have a
>simple procedure which will just do the relevant select from the relevant table
>which is fine, as long as all the tables exist: However, if one or more tables
>don't exist, obviously the procedure will not compile as it cannot parse the
>select.
>
>Hence I need a method of using dynamic SQL within this stored procedure. I can
>do this easily in a Pro*C executable, but cannot get the same functionality to
>work within PL/SQL.
>
>All I want to do is build a string containing a select statement, and pass
>selected values back to host variables. I can build the string OK, do an
>execute on it, and it all sems to work, except I cannot get a value passed back
>to a host variable.
>
>The PREPARE statement doesn't seem usable in PL/SQL, and an EXECUTE IMMEDIATE
>doesn't let you pass values back to host variables ... I think. Can anyone
>shed some light on this for me?
>
>Many Thanks
>
>Graham Turner

8i and above: Native Dynamic SQL: Open <ref cursor var> for :sqlstatement
8.0 and before : dbms_sql (this still exists in 8i)

Question is being asked multiple times per day and seems to be surpassing the popularity of 'How can I return a resultset'

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Jan 22 2002 - 06:13:51 CST