Re: select command with a variable in PL/SQL?

From: N Prabhakar <prabhs_at_po.pacific.net.sg>
Date: 1996/11/19
Message-ID: <56sbnf$6n9_at_newton.pacific.net.sg>#1/1


"Klaus Hartjes" <hartjes_at_uni-muenster.de> wrote:
>Hello,
>
>We run a Oracle-Server 7.0 with Forms 4.5 on a IBM-RS6000 with AIX 4.0.
>
>In our database exists a table >>persons<< with a column >>name<<.
>
>In Forms the following PL/SQL-Statement works fine:
> SELECT name INTO dummy FROM persons WHERE .....
>
>We now have the problem that we want to replace the tablename >>persons<<
>with a variable:
>
> SELECT name INTO dummy FROM &variable WHERE .....
>
>In SQLPLUS this works but not in PL/SQL.
>
>So, does anybody have a solution for this problem?
>
>Greetings
>
>Klaus Hartjes
>
>--

Hi there,

If your select statement is not a part of a base table block, then use DBMS_SQL to execute your statement. Through DBMS_SQL, a dynamic SQL statement can be executed.

If you want to implement this as a base table block, then try using dymanic where clause in FORMS 4.5

Frame the whole SQL statement and keep it in a variable. Then replace the DEFAULT_WHERE block property with your new variable. This will answer dynamic querying.

If you want to have a base table dynamically in Forms 4.5, then use UNION with the above where clause.

Regards

N.Prabhakar Received on Tue Nov 19 1996 - 00:00:00 CET

Original text of this message