Re: variable column query in cursor?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/06
Message-ID: <339807e0.19182012_at_newshost>#1/1

On Thu, 05 Jun 1997 13:14:51 -0500, "R. Burton" <rburton_at_rohms.com> wrote:

>I'm wondering if anyone can tell me why this does not work:
>
> cursor SONGS (scol varchar2, searchval varchar2) is
> select * from ss1 where scol like searchval ;
>
>if I call SONGS with the column name specified, it won't work; it works
>with searchval.
>
>What are the appropriate work-arounds? Obviously, I'd like to have a
>single cursor defined which allows me to specify both the search text as
>well as the column to search on.
>
>Please e-mail: rburton_at_rohms.com Thanks!

With your cursor, the above would turn "open songs( 'column1', '%x%' )" would be like saying:

  select * from ss1 where 'column1' like '%x%'

What you can try is:

 cursor songs( scol varchar2, searchval varchar2 ) is

    select * from ss1
    where decode( scol, 'C1', c1,

                        'C2', c2, 
                         ....
                        'CN', cn ) like searchval;

The decode will look at the column name and return the right column.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 06 1997 - 00:00:00 CEST