Re: Variable table name in update command

drake wrote:
> I have a stored procedure that uses a cursor:
>
> CURSOR ExtractCur IS
> select remform, action_id, effdt...
> from WorkingTable
> order by effdt;
>
> I then wish to update a record in a table based on the data in the
> cursor:
>
> FOR c IN ExtractCur LOOP
> ...
> update c.remform
> set status = 3
> where action_id = c.action_id;
>
> END LOOP;
>
> but it trips on the compile - complaining that the table 'c.remform'
> does not exist.
> 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 Received on Wed Sep 13 2006 - 11:21:23 CDT