Re: Variable where clause

Thanks Jared,=20
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.=20

Thanks again.=20
PS Hope book is selling well.=20

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.
>=20
> The second thing that comes to mind is use=20
> a single procedure and just check your inputs
> to determine how to proceed.
>=20
> Use the input validation to determine which of 3
> cursors to parse, open and return.
>=20
> You could also generate the SQL dynamically.
>=20
> Personally, I prefer avoid that. It works, but may
> cause more work on your end for design, testing
> and troubleshooting.
>=20
> There are no doubt other ways to accomplish this,
> but those are the first I thought of.
>=20
> HTH
>=20
> Jared
>=20
>=20
> On 6/14/05, Ranko Mosic <ranko.mosic_at_gmail.com> wrote:
> > Hi all,=3D20
> > I have to write pl/sql proc that will return cursor.=3D20
> > 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=3D=20
> > ion.=3D20
> > Input parameters will be matched against portions of 19 char string,=3D=
20
> > for example substr(roll, 4, 3) =3D3D map
> > substr(roll, 7, 3) =3D3D map_division
> >=20
> > How do I do this ?=3D20
> >=20
> > Regards, Ranko.
> > --
> > http://www.freelists.org/webpage/oracle-l
> >=20
>=20
>=20
>=20
> --=20
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>=20
>

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