Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Procedure MyCompare(): Update directly

Procedure MyCompare(): Update directly

From: Andreas Mosmann <keineemails_at_gmx.de>
Date: Fri, 07 Oct 2005 16:53:23 +0200
Message-ID: <1128696803.02@user.newsoffice.de>


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?

--

wenn email, dann AndreasMosmann <bei> web <punkt> de Received on Fri Oct 07 2005 - 09:53:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US