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

From: Ken Shirey <kshirey_at_spindle.net>
Date: 1996/11/15
Message-ID: <328C5753.2A12_at_spindle.net>#1/1

Klaus Hartjes 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
>
> --
> Dr. Klaus Hartjes, Dipl. Phys. Westfaelische Wilhelms-Universitaet
> Universitaetsrechenzentrum Einsteinstr. 60
> hartjes_at_uni-muenster.de 48149 Muenster, Germany

You are correct in deducing that PL/SQL cannot compile with a variable reference to a table name in the select list.

There is, however, a nice way around this.

--Create your query as a string, and store it in a variable.
--Use the package DBMS_SQL to submit your dynamically-built query 
--Use DBMS_SQL to retrieve the values into PL/SQL variables or records.

  to the database.
You'll find a great deal of information about this in :

	Oracle7 Server Application Developer's Guide
	ORACLE Developer's Guide (Oracle Press)
	PL/SQL User's Guide and Reference ( v2.1 or greater)

Learning this skill set will reverse aging, lower your blood pressure, and attract the best women! (no guarantees...)

Enjoy,
Ken Shirey
Oracle Database Administrator
PrimeCo Personal Communications, LLP
kshirey_at_primeco.com Received on Fri Nov 15 1996 - 00:00:00 CET