From oracle-l-bounce@freelists.org Tue Feb 1 00:32:26 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j116WQPh027800 for ; Tue, 1 Feb 2005 00:32:26 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j116WQem027795 for ; Tue, 1 Feb 2005 00:32:26 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1E8AE69728; Tue, 1 Feb 2005 00:31:33 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09755-01; Tue, 1 Feb 2005 00:31:33 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5F3D469633; Tue, 1 Feb 2005 00:31:32 -0500 (EST) Date: Tue, 01 Feb 2005 05:29:47 +0000 From: Mladen Gogala Subject: Re: PL/SQL - Use bind variable in "from " query To: ltiu@alumni.sfu.ca Cc: oracle-l References: <200501312105.06794.ltiu@alumni.sfu.ca> In-Reply-To: <200501312105.06794.ltiu@alumni.sfu.ca> (from ltiu@alumni.sfu.ca on Tue Feb 1 00:05:06 2005) Message-Id: <1107235787l.6799l.1l@medo.noip.com> MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: 8bit X-archive-position: 15536 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: gogala@sbcglobal.net Precedence: normal Reply-To: gogala@sbcglobal.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.2 required=5.0 tests=AWL,HTML_MESSAGE, HTML_TAG_BALANCE_TABLE autolearn=no version=2.60 X-Spam-Level: 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 =20 > in: >=20 > select * from
>=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