Re: variable select list in a cursor

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/11/22
Message-ID: <32963ab5.510163_at_dcsun4>#1/1


In pro*c:

EXEC SQL DECLARE C1 CURSOR FOR
select decode( :col1, 'jan', revenue_jan,

                      'feb', revenue_feb,
                       .....
                      'dec', revenue_dec ) col1,
       decode( :col2, 'jan', revenue_jan,
                      'feb', revenue_feb,
                       .....
                      'dec', revenue_dec ) col2
 from company
where company_name = :company_name
/
strcpy( col1, "dec" );
strcpy( col2, "mar" );
strcpy( company_name, "AT&T" );

EXEC SQL OPEN C1; In pl/sql

EXEC SQL DECLARE C1 CURSOR FOR
select decode( :col1, 'jan', revenue_jan,

                      'feb', revenue_feb,
                       .....
                      'dec', revenue_dec ) col1,
       decode( :col2, 'jan', revenue_jan,
                      'feb', revenue_feb,
                       .....
                      'dec', revenue_dec ) col2
 from company
where company_name = :company_name
/
strcpy( col1, "dec" );
strcpy( col2, "mar" );
strcpy( company_name, "AT&T" );

EXEC SQL OPEN C1; In pl/sql

declare

   cursor
   c1( p_col1 in varchar2, p_col2 in varchar2, p_company_name in varchar2)    is
   select decode( p_col1, 'jan', revenue_jan,

                         'feb', revenue_feb,
                          .....
                         'dec', revenue_dec ) col1,
         decode( p_col2, 'jan', revenue_jan,
                         'feb', revenue_feb,
                           .....
                         'dec', revenue_dec ) col2
    from company
   where company_name = p_company_name;
begin

open c1( 'mar', 'apr', 'AT&T' );

end;
/

On Sun, 17 Nov 1996 17:36:09 -0500, Tansel Ozkan <tansel_at_openix.com> wrote:

>Is there any way one can have a variable list of fields in a cursor?
>
>e.g.
>
>company table have these fields:
> company_name, revenue_jan,revenue_feb .... revenue_dec;
>
>1)
>CURSOR mycursor is
> SELECT revenue_mar,revenue_apr
> FROM company
> WHERE company_name = 'AT&T';
>2)
>CURSOR mycursor is
> SELECT revenue_sep,revenue_oct
> FROM company
> WHERE company_name = 'AT&T';
>
>I want to be able to define one cursor and specify the fields
>corresponding to different months. If you look at the above example, I
>want to be able to open the cursor 'mycursor' with any field combination
>I want ( revenue_mar, revenue-apr or revenue_sep, revenue-oct)
>
>If the answer is no, suggestions on how else I could achieve this would
>be greatly appreciated...
>
>God bless your programs :-)
>
>Tansel

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com

  • Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Nov 22 1996 - 00:00:00 CET

Original text of this message