Home » Other » Test » Log ID of the record im processing that gives error
Log ID of the record im processing that gives error [message #141535] Mon, 10 October 2005 15:42 Go to next message
cmsooracle
Messages: 11
Registered: August 2005
Junior Member
Please advise.
I am currently using the merge function to process insert and update based on an input file stored at an external table.

At the end of my procedure I log the job's status and have an exception for my job that WHEN OTHERS it writes out the erros, job name, etc to a table to help troubleshoot. I would like to log the ID of the record I am processing that gives me the error and pass that into my error msg table. How can I do that? I have a variable called e_relid that I am passing into the end of my job stats log.

e_relid relation.relid%type;

MERGE into RELATION_full a
USING (SELECT * from RELATION_UTIL_LOAD_EXT) b
ON (a.RELKEY = b.RELKEY)
WHEN MATCHED THEN UPDATE
SET a.RELID = b.RELID, a.RELRELATID = b.RELRELATID, a.RELWHOSE = b.RELWHOSE, a.RELLINKFIL = b.RELLINKFL,
a.RELLINKKEY = b.RELLINKKEY, a.RELSTRTDAT = b.RELSTRTDAT, a.RELSTOPDAT = b.RELSTOPDAT,
a.RELSOURCE = b.RELSOURCE,a.RELCOMM = b.RELCOMM, a.RELDATE1 = b.RELDATE1, a.RELDATE2 = b.RELDATE2,
a.RELMNY1 = b.RELMNY1, a.RELMNY2 = b.RELMNY2, a.RELNUM1 = b.RELNUM1, a.RELNUM2 = b.RELNUM2, a.RELYESNO = b.RELYESNO
WHEN NOT MATCHED THEN INSERT
(RELID,RELRELATID,RELISA,RELWHOSE,RELLINKFIL, RELLINKKEY, RELSTRTDAT, RELSTOPDAT,
RELSOURCE,RELCOMM, RELDATE1, RELDATE2, RELMNY1, RELMNY2, RELNUM1, RELNUM2, RELYESNO,
RELLOOK1,RELLOOK2, RELLOOK3, RELTEXT)
values (b.RELID,b.RELRELATID,b.RELISA,b.RELWHOSE, b.RELLINKFL, b.RELLINKKEY, b.RELSTRTDAT, b.RELSTOPDAT,
b.RELSOURCE,b.RELCOMM, b.RELDATE1, b.RELDATE2, b.RELMNY1, b.RELMNY2, b.RELNUM1, b.RELNUM2, b.RELYESNO,
b.RELLOOK1,b.RELLOOK2, b.RELLOOK3, b.RELTEXT);

p_cnt := p_cnt + SQL%ROWCOUNT ;

-- Log End of Job Status
--
custom.Common_Utility_Pkg.job_stat_prc
(uzrid,
v_jobname,
s_time,
s_time,
SYSDATE,
'End of Job',
'',
p_cnt,
0,
i_cnt,
0,
e_cnt);
EXCEPTION

WHEN OTHERS THEN
v_error_msg := SQLERRM;
e_cnt := e_cnt + 1;

custom.Common_Utility_Pkg.err_msg_tbl_prc
(uzrid,
v_jobname,
v_jobstep,
s_time,
SYSDATE,
e_relid,
'MERGE ERROR',
SUBSTR(v_error_msg,1,512));
custom.Common_Utility_Pkg.job_stat_prc
(uzrid,
v_jobname,
s_time,
s_time,
SYSDATE,
'Job Failed',
'See CUSTOM.ERR_MSG_TBL for details',
p_cnt,
u_cnt,
i_cnt,
d_cnt,
e_cnt);


END RELATION_UTIL_PRC;
/
Re: Log ID of the record im processing that gives error [message #190881 is a reply to message #141535] Fri, 01 September 2006 18:04 Go to previous message
fahadsami
Messages: 31
Registered: May 2006
Member
good
Previous Topic: how to post message
Next Topic: test
Goto Forum:
  


Current Time: Thu Mar 28 18:30:08 CDT 2024