Re: Dynamic cursors using dbms_sql

"Sam H" <sam_at_hasc.com> wrote in message
news:8FFCB8EC3samhasccom_at_216.46.1.7...
> Hi,
>
> I'm not sure if this is possible or not.
>
> Essentially I'm trying to do the following in a function:
>
> dbms_sql.parse('open pCursor for select a, b from tableX');
>
> pCursor is a generic reference cursor that is passed (unopened) to my
> function.
>
> The reason for using dbms_sql is that the return fields can be set by the
> function caller.
>
> Ex: return only a, only b, or a and b. Of course this is only an example.
> The real table has about 20 columns and I want to make a generic function
> that returns any combination of columns.
>
> Thanks in advance.
>
> S_at_M

As you don't mention the version you are using, for this time only I will provide an answer for different versions.

First of all: this is improper syntax for dbms_sql, the purpose of dbms_sql is to process sql statements or anynomous pl/sql blocks using it's own cursor mechanism. Submitting this code as an anonymous pl/sql block will make it end up nowhere.

So, in 7.3 and 8.0 this is not going to work at all. You'll need Pro*C for that.
In 8i you have Native Dynamic SQL allowing you to define a statement like this
Open Mycursor for :sqlstring.

I'm also not too sure whether it is advisable to do what you want or not. I wouldn't develop a sql*plus replacement, and allowing the user to submit completely random statements will usually result in performance problems.

Hth,

Sybrand Bakker, Oracle DBA Received on Fri Dec 01 2000 - 01:17:32 CST