Re: HELP! Dynamic SQL

In article <38e52eb0$0$29454_at_news.choice.net>, "Dana Jian" <dana_jian_at_hotmail.com> wrote:
> Hi,
>
> I'm building a function in Oracle8i which need to return a recordset.
>
> The recordset need to be created dynamically, which can be done by
using
> DBMS_SQL, now my question is, how to return the result set??
>
> Can I use Reference cursor? then How to save the value of
> DBMS_SQL.Column_Value variables into it?
> Or have to pass the result set in arrays??
>
> Any help would be greatly appreciated!!
> Dana
> Dana_jian_at_hotmail.com
>
>

In Oracle8i, you won't use DBMS_SQL. You'll use a ref cursor to dynamically return a result set.

It'll look like:

create procedure foo ( C in out types.refcur ) as
begin
  open C for 'select * from emp';
end;

where 'select * from emp' can be any string -- something you dynamically build at run time. See
http://osi.oracle.com/~tkyte/ResultSets/index.html for more pointers on handling result sets in various languages.

--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Apr 01 2000 - 07:26:24 CST