Re: A question about performing UPDATE and multiple tables

On Sep 7, 11:31 am, Ubiquitous <web..._at_polaris.net> wrote:
> I currently have a stored procedure which reads a transaction table for new
> records, writes them to a flat file (which is uploaded to a mainframe system
> for processing) and then updates the records as being processed.
>
> We will be creating a new ORACLE instance which also creates transactions. For
> network traffic and other issues, we will create an indentical transaction
> table in the new instance that will be processed the same was as the original.
>
> My current stored procedure looks something like this (simplified a bit for
> clarity's sake):
>
> /************************************************/
> CURSOR GetTrans_cur IS
> SELECT *
> FROM TRANS
> WHERE processing_dt IS NULL
> FOR UPDATE OF processing_dt;
>
> [...]
>
> FOR v_gettrans IN GetTrans_cur LOOP
> [build file record from cursor fields]
> UTL_FILE.PUT_LINE(v_outfile, v_REC);
>
> UPDATE TRANS
> SET processing_dt = SYSDATE
> WHERE CURENT OF GetTrans_cur;
> END LOOP;
> /************************************************/
>
> The easiest way to process the new table would be to create a second cursor
> and perform a second FOR LOOP for it, but is there a way to query and update
> multiple tables with a single cursor?
>
> I thought using a UNION would be perfect for the task but it doesn't like
> having an UPDATE OF clause. Am I barking up the wrong tree?

CURSOR c1 IS SELECT t1.col1, t1.col2, t2.col3, t2.col4

               FROM t1, t2
               WHERE t1.id = t2.id
             FOR UPDATE OF t1.something, t2.something;