Re: A question about performing UPDATE and multiple tables

From: Ed Prochak <edprochak_at_gmail.com>
Date: Fri, 07 Sep 2007 16:51:21 -0000
Message-ID: <1189183881.887721.299420@k79g2000hse.googlegroups.com>

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?

Personally, if that is all you are doing in this procedure, then I would just use two update statements instead of the explicit cursor loop. So it might look like this:

/************************************************/

CURSOR GetTrans_cur (getdate DATE)
IS SELECT *
FROM TRANS
WHERE processing_dt = getdate ;

[...]

    thisdate:=SYSDATE;

    UPDATE TRANS

       SET processing_dt = thisdate
     WHERE processing_dt IS NULL ;

     UPDATE TRANS2
       SET processing_dt = thisdate
     WHERE processing_dt IS NULL ;

FOR v_gettrans IN GetTrans_cur(thisdate) LOOP

    [build file record from cursor fields]     UTL_FILE.PUT_LINE(v_outfile, v_REC);

END LOOP;

/****************************************************/

/*** NOTE: obviously incomplete and untested code ***/

/****************************************************/

HTH,
ed Received on Fri Sep 07 2007 - 11:51:21 CDT