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: Variable table name in update command

Re: Variable table name in update command

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Wed, 13 Sep 2006 18:55:46 +0200
Message-ID: <ee9crf$67o$3@online.de>


fitzjarrell_at_cox.net schrieb:
> drake wrote:
>> I have a stored procedure that uses a cursor:
...
>> I then wish to update a record in a table based on the data in the
>> cursor:

...

>> How can I use a variable for a tablename in an update command?
>
> The same way we've told everyone else how to do it: use dynamic SQL,
> either using dbms_sql or through native dynamic sql (execute
> immediate):
>
> ...
> sqlTxt := 'update '||c.remform||' set status = 3 where action_id =
> '||c.action_id;
> execute immediate sqlTxt;
> ...
>
>
>
> David Fitzjarrell
>

David,

you're teaching how to NOT use bind variables!

Drake, this is how to do it right:

sqlTxt :=

   'update '||c.remform||' set status = :st where action_id = :id';

execute immediate sqlTxt using 3, c.action_id;

Urs Metzger Received on Wed Sep 13 2006 - 11:55:46 CDT

Original text of this message

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