Re: Procedure MyCompare(): Update directly

Andreas Mosmann wrote:
> Hi NG,
>
> there is a need to Compare 2 Tables regarding several columns and if
> there is a difference to change 1 of them. [1]
>
> Left | Right | Action
> ------+-------+---------
> Exist | Exist | if Cols are equal then nothing else update R and protokoll
> Exist | No | protokoll
> No | Exist | update R set flag and protokoll
>
> The left table is created by an overnight process and can be forgotten
> after it.
>
> For this I wrote a procedure that uses 2 cursors ordered by PK of R.
> Inside the procedure I had to update table R. Is it always possible or
> will the cursor refit its data or will oracle deny my intend? Is there a
> possibility to catch an exception there? [2]
>
> Thanks in advance
> Andreas
> [1] Oracle 9.2i
> [2]
> <PSEUDOCODE>
> if LeftRecord.FieldPK=RightRecord.FieldPK then
> if not MyEqual(LeftRecord,RightRecord) then
> -- TRY
> update TableRight set
> Field1=LeftRecord.Field1,
> Field2=LeftRecord.Field2,
> ..
> where
> FieldPK=RightRecord.FieldPK;
> insert into TableProtokoll
> (RightRecord.FieldPK,'Update',StoreOldValues(RightRecord));
> -- EXCEPT
> end if;
> else
> ..
> </PSEUDOCODE>
> This is an overnight- process, but I would like to catch, if update
> fails and write something like
> insert into TableProtokoll
> (RightRecord.FieldPK,Exception.Text,StoreOldValues(RightRecord));
> Is there a way to do so inside the procedure or do I have to create a
> second procedure called by the main procedure?

I would suggest using the DBMS_RECTIFIER_DIFF built-in package. You can find a demo of it in Morgan's Library at www.psoug.org. The package can both identify differences and rectify them.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)