Re: select command with a variable in PL/SQL?
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