streams insert - update - commit error handler

From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
Date: Mon, 15 Sep 2008 20:18:58 +0700
Message-ID: <3edcb66e0809150618l8c2a060r65f8de001ee86b64@mail.gmail.com>

lists,

I have tested streams on very simple configuration, which consist of source and destination database.
for all simple transactions there are no serious conflict/error, but with this kind of transaction, the apply process become stuck :)

at source database:
insert into tbl values (......) ; -- insert 1 record update tbl set ...... where ..... ; -- update the row that inserted justnow commit;

at destination, apply process complaining that no data found (ORA-01403). I Know this error, due to the first command is INSERT bu the latest command is UPDATE and then commit.

please let me know if you have handy procedure/function to handle this kind of transaction.
I thinking that I have to set error handler and insert into particular table at destination first, prior to execute the LCR, is it make sense? :)

from trace files:

kngofre: freeing lcr
kngoonew: request dur= 89, numcols=7
kngoonew: creating lcr 3 - dur 89, actual dur 89 colsize = 3, allocsz_kngxrcol = 3
nat_kngoxrcol, allocated size = 12, actual size = 3 xrcolp->nat_kngoxrcol = ffffffff7acaec70 colsize = 7, allocsz_kngxrcol = 7
nat_kngoxrcol, allocated size = 12, actual size = 7 xrcolp->nat_kngoxrcol = ffffffff7acaeb40 colsize = 7, allocsz_kngxrcol = 7
nat_kngoxrcol, allocated size = 12, actual size = 7 xrcolp->nat_kngoxrcol = ffffffff7acaea10 colsize = 3, allocsz_kngxrcol = 3
nat_kngoxrcol, allocated size = 12, actual size = 3 xrcolp->nat_kngoxrcol = ffffffff7acae8e0 colsize = 3, allocsz_kngxrcol = 3
nat_kngoxrcol, allocated size = 12, actual size = 3 xrcolp->nat_kngoxrcol = ffffffff7acae7b0 colsize = 5, allocsz_kngxrcol = 5
nat_kngoxrcol, allocated size = 12, actual size = 5 xrcolp->nat_kngoxrcol = ffffffff7acae680 colsize = 1, allocsz_kngxrcol = 1
nat_kngoxrcol, allocated size = 12, actual size = 1 xrcolp->nat_kngoxrcol = ffffffff7acae550 knipdis ---------
*** 2008-09-15 20:24:38.667

   userid: 69
   sname:CC
   oname:DIALCODES
   opname:REP_INSERT

knipgisql: insert -------------------

insert /*+ restrict_all_ref_cons */ into "CC"."DIALCODES" p("DCID","DCIDPARENT","DCTEXT","DCTYPE","DCV ALIDFROM","DCVALIDTO","DCVALUE")values(:1,:2,:3,:4,:5,:6,:7) knipdmpargs: dump new argument list:

    column: "DCID"

(dty, kncdty, acl, csf, csi, ind)=(2,0,3,0,0,0)
value = 8888

    column: "DCIDPARENT"

(dty, kncdty, acl, csf, csi, ind)=(2,0,1,0,0,0)
value = 0

    column: "DCTEXT"

(dty, kncdty, acl, csf, csi, ind)=(1,0,5,1,31,0)
value = PPPPP

    column: "DCTYPE"

(dty, kncdty, acl, csf, csi, ind)=(1,0,3,1,31,0)
value = PPP

    column: "DCVALIDFROM"

(dty, kncdty, acl, csf, csi, ind)=(12,0,7,0,0,0)
value = Mon Sep 15 19:42:57 2008

    column: "DCVALIDTO"

(dty, kncdty, acl, csf, csi, ind)=(12,0,7,0,0,0)
value = Mon Sep 15 19:42:57 2008

    column: "DCVALUE"

(dty, kncdty, acl, csf, csi, ind)=(1,0,3,1,31,0)
value = PPP
executing REP_INSERT
leaving knipdis ----
*** 2008-09-15 20:24:38.668
kngofre: freeing lcr
kngoonew: request dur= 89, numcols=5
kngoonew: creating lcr 3 - dur 89, actual dur 89 colsize = 3, allocsz_kngxrcol = 3
nat_kngoxrcol, allocated size = 12, actual size = 3 xrcolp->nat_kngoxrcol = ffffffff7acaec70 colsize = 3, allocsz_kngxrcol = 3
nat_kngoxrcol, allocated size = 12, actual size = 3 xrcolp->nat_kngoxrcol = ffffffff7acaeb40 colsize = 7, allocsz_kngxrcol = 7
nat_kngoxrcol, allocated size = 12, actual size = 7 xrcolp->nat_kngoxrcol = ffffffff7acaea10 colsize = 3, allocsz_kngxrcol = 3
nat_kngoxrcol, allocated size = 12, actual size = 3 xrcolp->nat_kngoxrcol = ffffffff7acae8e0 colsize = 3, allocsz_kngxrcol = 3
nat_kngoxrcol, allocated size = 12, actual size = 3 xrcolp->nat_kngoxrcol = ffffffff7acae7b0 knipdis ---------
*** 2008-09-15 20:24:38.668

   userid: 69
   sname:CC
   oname:DIALCODES
   opname:REP_UPDATE

knipgusql: update -------------------

update /*+ streams or_expand(p "DCIDPARENT" )restrict_all_ref_cons */ "CC"."DIALCODES" p set "DCID"=de
code(:1,'N',"DCID",:2), "DCIDPARENT"=decode(:3,'N',"DCIDPARENT",:4), "DCTEXT"=decode(:5,'N',"DCTEXT",:6
), "DCTYPE"=decode(:7,'N',"DCTYPE",:8),

"DCVALIDFROM"=decode(:9,'N',"DCVALIDFROM",:10), "DCVALIDTO"=dec
ode(:11,'N',"DCVALIDTO",:12), "DCVALUE"=decode(:13,'N',"DCVALUE",:14)
where  (:15="DCID") and(:16="DCID

PARENT" or(:16 is null and "DCIDPARENT" is null)) and(:17="DCTEXT") and(:18="DCTYPE") and(:19="DCV
ALIDFROM") and(:20="DCVALIDTO") and(:21="DCVALUE") knipdmpargs: dump old argument list:

    column: "DCID"

(dty, kncdty, acl, csf, csi, ind)=(2,0,3,0,0,0)
value = 8888

    column: "DCIDPARENT"

              no available value
    column: "DCTEXT"

              no available value
    column: "DCTYPE"

(dty, kncdty, acl, csf, csi, ind)=(1,0,3,1,31,0)
value = PPP

    column: "DCVALIDFROM"

              no available value
    column: "DCVALIDTO"

(dty, kncdty, acl, csf, csi, ind)=(12,0,7,0,0,0)
value = Mon Sep 15 19:42:57 2008

    column: "DCVALUE"

(dty, kncdty, acl, csf, csi, ind)=(1,0,3,1,31,0)
value = PPP
knipdmpargs: dump new argument list:

    column: "DCID"

              no available value
    column: "DCIDPARENT"

              no available value
    column: "DCTEXT"

              no available value
    column: "DCTYPE"

(dty, kncdty, acl, csf, csi, ind)=(1,0,3,1,31,0)
value = XXX

    column: "DCVALIDFROM"

              no available value
    column: "DCVALIDTO"

              no available value
    column: "DCVALUE"

              no available value
executing REP_UPDATE
*** 2008-09-15 20:24:38.669
ksedmp: internal or fatal error
ORA-01403: no data found

-- 
thanks and regards
ujang | oracle dba
jakarta | http://ora62.wordpress.com
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 15 2008 - 08:18:58 CDT