RE: Variable where clause

From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Wed, 15 Jun 2005 09:04:58 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF16E6E5@EXCNYSM0A1AJ.nysemail.nyenet>

Ranko,

I used the second method extensively. We build the where clause depending on input variables and then use "open cursor" and fetches to get the data.

Take advantage of substitution variables like this:

Select_clause varchar2(2000) :=3D 'select * from emp ';

Where_clause varchar2(2000);

Begin

If in_ename is not null then

   Where_clause :=3D 'where ename =3D :ename'; End if;

OPEN p_refRS FOR=20

     select_clause || where_clause USING in_ename;

Of course, you need to set up the variables to fetch the data into. =20

I like the SYS_CONTEXT approach that Tom Kyte's web site demo'ed. I just have not used it yet - something else to look into!

Good Luck!

Tom

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ranko Mosic Sent: Tuesday, June 14, 2005 9:22 PM
To: Jared Still
Cc: oracle-l_at_freelists.org
Subject: Re: Variable where clause

Thanks Jared,=3D20
I like second option because it is black box approach -there are no 3 separate signatures as with orerloaded stuff. I think actually I will do compbination of the first and third suggestion - first check which parameters I got, then build sql. It can be done quite easily now with
this execute immediate stuff.=3D20

Thanks again.=3D20
PS Hope book is selling well.=3D20

On 6/14/05, Jared Still <jkstill_at_gmail.com> wrote:
> The first thing that comes to mind is a package
> with 3 overloaded procedures, one for each set
> of input parameters.

>=3D20

> The second thing that comes to mind is use=3D20
> a single procedure and just check your inputs
> to determine how to proceed.

>=3D20

> Use the input validation to determine which of 3
> cursors to parse, open and return.

>=3D20

> You could also generate the SQL dynamically.

>=3D20

> Personally, I prefer avoid that. It works, but may
> cause more work on your end for design, testing
> and troubleshooting.

>=3D20

> There are no doubt other ways to accomplish this,
> but those are the first I thought of.

>=3D20

> HTH

>=3D20

> Jared

>=3D20
>=3D20

> On 6/14/05, Ranko Mosic <ranko.mosic_at_gmail.com> wrote:
> > Hi all,=3D3D20
> > I have to write pl/sql proc that will return cursor.=3D3D20
> > Input parameters could be 1) map, 2) map, map_division or 3) map,
> > map_division, map_subdivision.
> > I don't want to write separate procedures for each input parameter
> combinat=3D3D=3D20
> > ion.=3D3D20
> > Input parameters will be matched against portions of 19 char
string,=3D3D=3D
20
> > for example substr(roll, 4, 3) =3D3D3D map
> > substr(roll, 7, 3) =3D3D3D map_division
> >=3D20
> > How do I do this ?=3D3D20
> >=3D20
> > Regards, Ranko.
> > --
> > http://www.freelists.org/webpage/oracle-l
> >=3D20

>=3D20
>=3D20
>=3D20

> --=3D20
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>=3D20
>

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 15 2005 - 09:10:08 CDT