Re: Dynamic SQL , Define Columns, and Ref Cursors in Oracle 8.0.6

Tom,

    First, I want to thank you for answering my query so fast. Second, I have another question. I realize I can return a REF CURSOR to both ASP and ColdFusion; however, how may I do this when the number of columns will differ each time? Thanks.

Greg

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<a5o48o011c3_at_drn.newsguy.com>...
> In article <6948ef8.0202281929.1a6d3bb2_at_posting.google.com>,
> gmazujian_at_crosswinds.net says...
> >
> >I am new to Dynamic SQL so please bear with me. I know am trying to
> >make a dynamic query of a database. Let's say I one time might have
> >three benefits and another time four. I just don't know.
> >Consequently, when I make my dynamic query up I do a SELECT Column 1,
> >Column 2, Column 3 . I must also Define columns afterwards. This is
> >part of the problem. Since I don't know until I make up the dynamic
> >SQL, how many columns there will be, how can I Define Columns
> >afterwards? Even if this can be done, how may I return this resultset
> >to an ASP or ColdFusion page? Thanks for any help.
> >
> >Greg
>
> ASP and coldfusion both accept ref cursors.
>
> You will just
>
> create package my_pkg
> as
> type rc is ref cursor;
>
> procedure p( p_cursor in out rc );
> end;
> /
>
> create package body my_pkg
> as
> procedure p( p_cursor in out rc )
> is
> l_query long;
> begin
> .... logic to build a query into l_query ....
> open p_cursor for l_query;
> end;
> end;
> /
>
>
> see:
> http://osi.oracle.com/~tkyte/ResultSets/index.html
> and
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1288401763279
> for other important information.
Received on Wed Mar 06 2002 - 13:05:33 CST