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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1 Mar 2002 06:46:48 -0800
Message-ID: <a5o48o011c3@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.         

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri Mar 01 2002 - 08:46:48 CST