We have Main table having Product ID 1 to 3, we will loop these records and insert each record into Table 1 and Table 2, i.e.
Loop for Product ID 1 - Insert Product ID 1 to table 1 success, Insert Product ID 1 to table 2 success;
Loop for Product ID 2 - Insert Product ID 2 to table 1 success, Insert Product ID 2 to table 2 fail;
Loop for Product ID 3 - Insert Product ID 3 to table 1 success, Insert Product ID 3 to table 2 success;
The tricky part is - we want to rollback the inserting Product ID 2 to table 1 and record the exception of inserting Product ID 2 to table 2 into Error Log and continue the next looping;
How we design the Exception Handling to handle this requirement?
The solution is we need to handle the exception within looping, in main loop, we should commit the transaction for each record's sub-transactions success, and ignore/rollback when any record sub-transaction is failure, and the logging of the exception should do it in sub transaction.
Here is the main stored procedure and sub stored procedures to simulate this scenario:-
PACKAGE BODY "YB_TEST" IS
PROCEDURE "PROC_MAIN" IS
BEGIN -- executable part starts here
DECLARE CURSOR P IS
SELECT PRODUCT_ID, PRODUCT_NAME
FROM TABLE_LOOP
ORDER BY PRODUCT_ID;
BEGIN
FOR r IN p LOOP
BEGIN
PROC_SUB1(r.PRODUCT_ID, r.PRODUCT_ID, 'REMARK FOR ' || r.PRODUCT_ID);
PROC_SUB2(r.PRODUCT_ID, r.PRODUCT_ID, 'AUDIT FOR ' || r.PRODUCT_ID);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
NULL;
-- ROLLBACK;
END;
END LOOP;
END;
END "PROC_MAIN";
BEGIN -- executable part starts here
DECLARE CURSOR P IS
SELECT PRODUCT_ID, PRODUCT_NAME
FROM TABLE_LOOP
ORDER BY PRODUCT_ID;
BEGIN
FOR r IN p LOOP
BEGIN
PROC_SUB1(r.PRODUCT_ID, r.PRODUCT_ID, 'REMARK FOR ' || r.PRODUCT_ID);
PROC_SUB2(r.PRODUCT_ID, r.PRODUCT_ID, 'AUDIT FOR ' || r.PRODUCT_ID);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
NULL;
-- ROLLBACK;
END;
END LOOP;
END;
END "PROC_MAIN";
PROCEDURE "PROC_SUB1" (
P_ORDER_ID IN NUMBER,
P_PRODUCT_ID IN NUMBER,
P_REMARKS IN VARCHAR2) IS
BEGIN -- executable part starts here
INSERT INTO TABLE1 (ORDER_ID, PRODUCT_ID, REMARKS)
VALUES (P_ORDER_ID, P_PRODUCT_ID, P_REMARKS);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK; -- Rollback the previous insert
INSERT INTO TABLE_LOG (PRODUCT_ID, EXCEPTION_DESC)
VALUES(P_PRODUCT_ID, SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 100));
COMMIT; -- Commit this exception logging
RAISE; -- Re-throw Exception to caller, i.e. Main Loop
END "PROC_SUB1";
P_ORDER_ID IN NUMBER,
P_PRODUCT_ID IN NUMBER,
P_REMARKS IN VARCHAR2) IS
BEGIN -- executable part starts here
INSERT INTO TABLE1 (ORDER_ID, PRODUCT_ID, REMARKS)
VALUES (P_ORDER_ID, P_PRODUCT_ID, P_REMARKS);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK; -- Rollback the previous insert
INSERT INTO TABLE_LOG (PRODUCT_ID, EXCEPTION_DESC)
VALUES(P_PRODUCT_ID, SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 100));
COMMIT; -- Commit this exception logging
RAISE; -- Re-throw Exception to caller, i.e. Main Loop
END "PROC_SUB1";
PROCEDURE "PROC_SUB2" (
P_AUDIT_ID IN NUMBER,
P_PRODUCT_ID IN NUMBER,
P_AUDIT_DESC IN VARCHAR2) IS
BEGIN -- executable part starts here
INSERT INTO TABLE2 (AUDIT_ID, PRODUCT_ID, AUDIT_DESC)
VALUES (P_AUDIT_ID, P_PRODUCT_ID, P_AUDIT_DESC);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK; -- Rollback the previous insert
INSERT INTO TABLE_LOG (PRODUCT_ID, EXCEPTION_DESC)
VALUES(P_PRODUCT_ID, SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 100));
COMMIT; -- Commit this exception logging
RAISE; -- Re-throw Exception to caller, i.e. Main Loop
END "PROC_SUB2";
END "YB_TEST";
P_AUDIT_ID IN NUMBER,
P_PRODUCT_ID IN NUMBER,
P_AUDIT_DESC IN VARCHAR2) IS
BEGIN -- executable part starts here
INSERT INTO TABLE2 (AUDIT_ID, PRODUCT_ID, AUDIT_DESC)
VALUES (P_AUDIT_ID, P_PRODUCT_ID, P_AUDIT_DESC);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK; -- Rollback the previous insert
INSERT INTO TABLE_LOG (PRODUCT_ID, EXCEPTION_DESC)
VALUES(P_PRODUCT_ID, SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 100));
COMMIT; -- Commit this exception logging
RAISE; -- Re-throw Exception to caller, i.e. Main Loop
END "PROC_SUB2";
