From: Tom Mettling <mettlingt@wtchen.com>
Subject: Re: Variable selects in PL/SQL
Date: 1997/09/09
Message-ID: <34155864.AE0A5CC4@wtchen.com>#1/1
References: <5v1l7d$hfs@nosy.bart.nl>
X-Priority: 3 (Normal)
Organization: WT Chen & Company, Inc.
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools




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@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@bart.nl



















--
Tom Mettling
W.T. Chen & Company, Inc.
mettlingt@wtchen.com