RE: Variable where clause
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: <ranko.mosic@xxxxxxxxx>, "Jared Still" <jkstill@xxxxxxxxx>
- Date: Tue, 14 Jun 2005 20:51:26 -0700
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@xxxxxxxxxxxxxxxxx=20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ranko Mosic
Sent: Tuesday, June 14, 2005 6:22 PM
To: Jared Still
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxx> 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.
> > --
> > //www.freelists.org/webpage/oracle-l
> >=3D20
>=3D20
>=3D20
>=3D20
> --=3D20
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>=3D20
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
