Re: Variable where clause

> If in_ename is not null then
> Where_clause :=3D 'where ename =3D :ename';
> End if;

Of course if you have a bunch of variables that can be or can't be null then you have to add

> If in_ename is not null then
> Where_clause :=3D 'where ename =3D :ename';

   ELSE
    Where_clause :=3D 'and :ename IS NULL' ;
> End if;

So at the very end you can write
OPEN p_refRS FOR

    select_clause || where_clause USING in_ename, in_next_var, ...., in_last_var;
and don't worry whether your input variable was or wasn't null.

BUT as usual Kytes proposed solution works very nicely and I'v already used it for a few times in real projects.

Gints

On 6/15/05, Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us> wrote:
> Ranko,
>=20
> 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.
>=20
> Take advantage of substitution variables like this:
>=20
> Select_clause varchar2(2000) :=3D3D 'select * from emp ';
>=20
> Where_clause varchar2(2000);
>=20
> Begin
>=20
> If in_ename is not null then
> Where_clause :=3D3D 'where ename =3D3D :ename';
> End if;
>=20
> OPEN p_refRS FOR=3D20
> select_clause || where_clause USING in_ename;
>=20
>=20
> Of course, you need to set up the variables to fetch the data into. =3D20
>=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!
>=20
> Good Luck!
>=20
> Tom
>=20
>

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