Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Variable table name in update command
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