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;
/
|
] 
Blog
Search
Help
Members
Register
Login
Home
