Home » SQL & PL/SQL » SQL & PL/SQL » Handling Rejected Rows (Oracle 10g)
Handling Rejected Rows [message #403122] Wed, 13 May 2009 16:40 Go to next message
pkumar_2008
Messages: 19
Registered: November 2008
Location: usa
Junior Member

Hi,

Here,is the procedure which loads the data from staging table into target table.

Question:Incase,If any of the row does not load into target table then I need to trap the customer_id and load into an error table.

Procedure should not fail If something goes wrong inthe data..Procedure should continue to load data for other customers if anyof the customers data has problem.

Please,provide your idea...



Create or Replace PROCEDURE Proc_transactiontemplate
IS
CURSOR cur_transactiontemplate IS
SELECT Trim(stg.customer_id) AS customer_id,
Trim(stg.customer_name) AS customer_name,
stg.database_id,
Trim(stg.template) AS database_name,
master.instructiontemplatekey,
Row_number()
OVER(PARTITION BY master.instructiontemplatekey ORDER BY master.instructiontemplatekey) AS transactiontemplateid,
stg.bankgroupid,
stg.classification,
stg.version,
Trunc(stg.whenmodified) AS whenmodified,
stg.trntp,
stg.pmtid_endtoendid,
stg.instdamt,
Trim(stg.instdamt_ccy) AS instdamt_ccy,
Trim(stg.acctowner_nm) AS acctowner_nm,
Trim(stg.acctowner_pstladr_adrline) AS acctowner_pstladr_adrline,
Trim(stg.acctowner_pstladr_pstcd) AS acctowner_pstladr_pstcd,
Trim(stg.acctowner_pstladr_twnnm) AS acctowner_pstladr_twnnm,
Trim(stg.acctowner_pstladr_ctrysubdvsn) AS acctowner_pstladr_ctrysubdvsn,
Trim(stg.acctowner_pstladr_ctry) AS acctowner_pstladr_ctry,
Trim(stg.acctowner_id_id) AS acctowner_id_id,
Trim(stg.acct_acctid_idtp) AS acct_acctid_idtp,
Trim(stg.acct_acctid_id) AS acct_acctid_id,
Trim(stg.agt_fininstnid_nm) agt_fininstnid_nm,
Trim(stg.agt_pstladr_adrline) AS agt_pstladr_adrline,
Trim(stg.agt_pstladr_twnnm) AS agt_pstladr_twnnm,
Trim(stg.agt_pstladr_ctrysubdvsn) AS agt_pstladr_ctrysubdvsn,
Trim(stg.agt_pstladr_pstcd) AS agt_pstladr_pstcd,
Trim(stg.agt_pstladr_ctry) AS agt_pstladr_ctry,
Trim(stg.agt_prtryid_id) AS agt_prtryid_id,
Trim(stg.purp_purpcd) AS purp_purpcd,
stg.transactioncode,
Trim(stg.paymentinformation_information) AS paymentinformation_information,
Trim(stg.accttype) AS accttype,
stg.fininstnid_routingnumber,
bene_status
FROM stg_dbentries_tran_temp stg,
master_list master
WHERE Trim(stg.customer_id) = Trim(master.customer_externalid)
AND Trim(stg.template) = Trim(master.template_name)
AND (master.instructiontemplatekey) NOT IN (SELECT instructiontemplatekey
FROM transactiontemplate)
AND (master.instructiontemplatekey) NOT IN (SELECT instructiontemplatekey
FROM bptransactiontemplate);
CURSOR c_exist_rows IS
SELECT customer_id,
template_name
FROM ((SELECT Trim(customer_id) customer_id,
Trim(template) template_name
FROM stg_dbentries_tran_temp
MINUS
SELECT Trim(customer_externalid) customer_id,
Trim(template_name) template_name
FROM master_list)
UNION
(SELECT Trim(customer_externalid) customer_id,
Trim(template_name) AS template_name
FROM master_list
MINUS
SELECT Trim(customer_id) AS customer_id,
Trim(template) AS template_name
FROM stg_dbentries_tran_temp)) a;
v_customer_id master_list.customer_externalid%TYPE;
v_template_name master_list.template_name%TYPE;
ERROR_CODE NUMBER;
error_msg VARCHAR2(200);
BEGIN
IF cur_transactiontemplate%ISOPEN THEN
CLOSE cur_transactiontemplate;
END IF;

/*BEGIN
FOR CUR_STATUS IN C_Exist_Rows
LOOP
MIGRATION_log_error('STG_C2P_LOAD',CUR_STATUS.CUSTOMER_ID,CUR_STATUS.TEMPLATE_NAME,100,'NO DATA FOUND');
END LOOP;
END;*/
FOR cur_template IN cur_transactiontemplate LOOP
INSERT INTO transactiontemplate
(instructiontemplatekey,
transactiontemplateid,
bankgroupid,
classification,
version,
whenmodified,
details)
VALUES (cur_template.instructiontemplatekey,
cur_template.transactiontemplateid,
cur_template.bankgroupid,
cur_template.classification,
cur_template.version,
cur_template.whenmodified,
Xmltype(' '
||cur_template.trntp
||' '
||cur_template.pmtid_endtoendid
||'
||cur_template.instdamt_ccy
||'">'
||cur_template.instdamt
||' '
||cur_template.acctowner_nm
||' '
||cur_template.acctowner_pstladr_adrline
||' '
||cur_template.acctowner_pstladr_pstcd
||' '
||cur_template.acctowner_pstladr_twnnm
||' '
||cur_template.acctowner_pstladr_ctrysubdvsn
||' '
||cur_template.acctowner_pstladr_ctry
||' '
||cur_template.acctowner_id_id
||' '
||cur_template.acct_acctid_idtp
||' '
||cur_template.acct_acctid_id
||' '
||cur_template.agt_fininstnid_nm
||' '
||cur_template.agt_pstladr_adrline
||' '
||cur_template.agt_pstladr_twnnm
||' '
||cur_template.agt_pstladr_ctrysubdvsn
||' '
||cur_template.agt_pstladr_pstcd
||' '
||cur_template.agt_pstladr_ctry
||' '
||cur_template.agt_prtryid_id
||' '
||cur_template.purp_purpcd
||' '
||cur_template.transactioncode
||' '
||cur_template.paymentinformation_information
||' '
||cur_template.accttype
||' '
||cur_template.fininstnid_routingnumber
||' '));

INSERT INTO bptransactiontemplate
(instructiontemplatekey,
transactiontemplateid,
status,
transactionaccountownernameidx)
VALUES (cur_template.instructiontemplatekey,
cur_template.transactiontemplateid,
cur_template.bene_status,
cur_template.acctowner_nm);

UPDATE stg_dbentries_tran_temp
SET status = 'P'
WHERE Trim(customer_id) = Trim(cur_template.customer_id);
END LOOP;

COMMIT;

pack1.proc_agg_instructiontemplate;
EXCEPTION
WHEN OTHERS THEN
ERROR_CODE := SQLCODE;

error_msg := Substr(SQLERRM,100);

INSERT INTO migration_error
(phase,
error_column,
description,
date_of)
VALUES ('TRANSACTIONTEMPLATE_LOAD',
ERROR_CODE,
error_msg,
SYSDATE);

COMMIT;
END proc_transactiontemplate;
Re: Handling Rejected Rows [message #403123 is a reply to message #403122] Wed, 13 May 2009 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
use LOG ERRORS clause on DML statements

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Wed, 13 May 2009 17:07]

Report message to a moderator

Re: Handling Rejected Rows [message #403139 is a reply to message #403123] Wed, 13 May 2009 23:01 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Use your code in begin and end.

some thing like this

BEGIN
   FOR i IN 1 .. 100 LOOP
      BEGIN
	  --<do something>
         NULL;
      EXCEPTION
	  --<catch exception>
      END;
   END LOOP;
END;


1. Also don't commit records for each row in for loop.
2. Use Bulk collect and FORALL (save exception) to trap errors, in more efficient way.

Thanks
Trivendra
Previous Topic: number of tables refering a particular column
Next Topic: Getting error :ORA-06531:Reference to unintialized collection (merged)
Goto Forum:
  


Current Time: Fri Dec 02 23:15:54 CST 2016

Total time taken to generate the page: 0.12961 seconds