Re: Variable selects in PL/SQL

Dennis,

Use the DBMS_SQL package. It allows you to parse the statement with bind variables, and then assign values to the variables at run-time.

Good Luck,

--
Tom Mettling
WT Chen & Company, Inc.
mettlingt_at_wtchen.com



Dennis de Visser wrote:



> Dear ORACLE users,

>

> Can some one help me to create a variable select statement in PL/SQL.


>

> I'm looking for a way to get the same result as the example ( SQL*Plus

> ), so

> it can be used in Forms.

>

> COLUMN where1  NEW_VALUE where1    NOPRINT

>

> SELECT DECODE('&1','%'   ,NULL

>

>                   ,'EAST','AND dept IN (''10'',''20'')'

>

>                   ,'WEST','AND dept IN (''30'',''40'')'

>

>                          ,NULL)       where1

>

> FROM   DUAL

>

> ;

>

> SELECT ename

>

> FROM   emp

>

> WHERE  mng  IS NOT NULL &where1

>

> ORDER  BY ename

>

> ;

>

> I hope some of you got the answer.

>

> Kind regards,

>

> Dennis de Visser,

> ORACLE programmer

> Syntegra Netherlands

> dennisv_at_bart.nl




















--
Tom Mettling
W.T. Chen & Company, Inc.
mettlingt_at_wtchen.com