streams insert - update - commit error handler
- From: "Ujang Jaenudin" <ujang.jaenudin@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 15 Sep 2008 20:18:58 +0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
