Oracle PL/SQL Stored Procedure Looping Nested Exception Handling

Today we have a requirement that we want to have a stored procedure loop an Oracle records, inside the loop, we want to insert each record into few more tables via sub stored procedure, but we want to continue the loop if the insertion fail, and just only rollback the fail insert for the particular record, let's see an example:-

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";

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";

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";