A question about performing UPDATE and multiple tables

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;

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? Received on Fri Sep 07 2007 - 10:31:13 CDT