Re: Dynamic SQL Cursors

A copy of this was sent to "Chad Sheley" <csheley_at_usa.capgemini.com> (if that email address didn't require changing) On Fri, 5 Nov 1999 11:11:48 -0600, you wrote:

>I am fairly knew to the PL/SQL / Oracle world, but have become pretty
>familiar with a couple of concepts: returning a cursor from a function or
>stored procedure AND dynamic SQL.
>
>Now... I want to combine the 2 concepts. I want to dynamically create a SQL
>statement and then return a cursor object from my function that creates the
>statement. As near as I can tell, the dynamic SQL package (DBMS_SQL)
>identifies the cursor with an integer variable, not a cursor object.
>
>How can I reference a cursor object created from a dynamic SQL statement.
>

you cannot until Oracle8i, release 8.1. then you can:

begin
  open ref_cursor_variable for some_variable_with_a_query; end;

so that:

begin

   open x for 'select * from ' || table_name; end;

is now valid in that release.

>Thanks,
>
>Chad Sheley
>csheley_at_usa.capgemini.com
>Senior Consultant
>Cap Gemini
>Des Moines, IA
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Nov 05 1999 - 11:48:29 CST