Re: Variable where clause

The first thing that comes to mind is a package with 3 overloaded procedures, one for each set of input parameters.
The second thing that comes to mind is use a single procedure and just check your inputs to determine how to proceed.

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

You could also generate the SQL dynamically.

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

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

HTH Jared

On 6/14/05, Ranko Mosic <ranko.mosic_at_gmail.com> wrote:
>
> Hi all,=20
> I have to write pl/sql proc that will return cursor.=20
> 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=
> ion.=20
> Input parameters will be matched against portions of 19 char string,=20
> for example substr(roll, 4, 3) =3D map
> substr(roll, 7, 3) =3D map_division
>
> How do I do this ?=20
>
> Regards, Ranko.
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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