RE: Variable where clause

From: MacGregor, Ian A. <ian_at_slac.stanford.edu>
Date: Tue, 14 Jun 2005 20:51:26 -0700
Message-ID: <7F24308CD176594B8F14969D10C02C6C8A024F@exch-mail2.win.slac.stanford.edu>

It is not readily apparent that this method is applicable to the = problem, and I deleted the original message. Variable where clauses can = be handled through sys_context. See =
http://asktom.oracle.com/pls/ask/f?p=3D4950:8:::::F4950_P8_DISPLAYID:1288= 401763279

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu=20

-----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 6: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 Tue Jun 14 2005 - 23:56:51 CDT