Re: variable select list in a cursor
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 ) col2from 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 ) col2from 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 ) col2from 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
- 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