Re: PL/SQL - Use bind variable in "from <table>" query

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Tue, 01 Feb 2005 05:29:47 +0000
Message-Id: <1107235787l.6799l.1l@medo.noip.com>

On 02/01/2005 12:05:06 AM, Lyndon Tiu wrote:
> Hello,

>=20
> I have a lookup table with a column.

That's interesting. Are you sure you have a column in the lookup table?

>=20

> This column holds a variable - the name of another table.
>=20
> In PL/SQL code, I need to query (at run time) the lookup table, retrieve =
the=20
> value stored in the lookup table's column and use it as the table in anot=
her=20
> query.

>=20
> I have tried using bind variables but it seems that I cannot set the <tab=
le>=20
> in:

>=20

> select * from <table>

>=20
> to a bind variable.

Lyndon, you misunderstood the concept of binding. Bind variables are parkin= g lots. You draw a=20
little square on the concrete and imagine that there is a car in that squar= e. When the parking=20
garage is used, a car does get in there. In other words, you "bind" your ca= r to the "variable"=20
(parking lot) in the "SQL" (garage).
What you are trying to do is equivalent to building a garage on the fly, wh= enever you want=20
to park the car. What you are talking about is constructing SQL dynamically= , not binding.=20
You should be binding variables to an already parsed SQL statement ("parkin= g garage") which=20
has placeholders to which variables should be bound ("parking lot"). Oracle=  cannot parse SQL=20
if it doesn't know about all the referenced objects. Oracle must check the = accessibility of=20
the objects before the statement can be parsed. Dynamic SQL is another, ent= irely different=20
story. Binding is not magic, it is parallel parking. You should practice i= t in Manhattan,=20
around Broadway, at 08:30 AM, Mon-Fri. You'll love it.

--=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 01 2005 - 00:32:26 CST