Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Bind Variable as Column name...

Re: Bind Variable as Column name...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 02 Aug 1999 11:12:06 GMT
Message-ID: <37a67bf7.813920@newshost.us.oracle.com>


A copy of this was sent to tjmxyz_at_my-deja.com (if that email address didn't require changing) On Mon, 02 Aug 1999 08:49:29 GMT, you wrote:

>Hi!
>
>I have been trying to do the following but I don't think you can do
>it...
>
>Make the column name a bind variable.
>
>I.e.
>update table test set :COLUMN_NAME=:COLUMN_VALUE;
>
>is this not possible????
>
>The reason is I want all my SQL Statements to be identical so they
>will be found in the shared pool....
>

bind variables can only be used where a character string constant can be used. Specifically - they cannot be used where an IDENTIFIER is expected/mandatory.

Since you cannot code:

update T set 'C' = '5';

you cannot code:

update T set :x = '5';

identifiers must be 'fixed' in a query to develop a plan. Lets say we did allow for identifiers to be done via bind variables. We would parse "update t set :x = :y". :x could be any column of T. column_1 has an index, column_2 does not, column_3 when updated would cause a trigger to fire, column_4 does not, column_5 is a date, column_6 is not (and so on). We cannot come up with an optimized plan to process that update (since we don't know the column and its attributes). shared sql would not work -- we'd have to create the plan AFTER binding, not before as we do.

>Thanks!
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 02 1999 - 06:12:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US