Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query help - Exception handle to log problematic records in logging table

Ricky007Jan 5 2024

Experts please help to log the exception in error log table which is common table for all and I don't want use DBMS_ERROR package ,because it creates error logging table for associated target table.

create table EMPLOYEETABLE
(
emp_id NUMBER,
emp_name VARCHAR2(50),
emp_salary NUMBER
);

create or replace TYPE EmployeeType AS OBJECT (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    emp_salary NUMBER
);

create or replace TYPE EmployeeListType AS TABLE OF EmployeeType;



CREATE TABLE ErrorLog (
    error_id NUMBER PRIMARY KEY,
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    emp_salary NUMBER,
    error_message VARCHAR2(4000)
);



CREATE OR REPLACE PROCEDURE InsertEmployee(
    p_employees IN EmployeeListType
) AS
    V_REJCT_RCRD   EXCEPTION;
    v_err_msg      VARCHAR2(500);

BEGIN
    FOR i IN 1..p_employees.COUNT
LOOP
BEGIN
            BEGIN
            INSERT INTO EmployeeTable (emp_id, emp_name, emp_salary)
            VALUES (p_employees(i).emp_id, p_employees(i).emp_name, p_employees(i).emp_salary);
        EXCEPTION
            WHEN OTHERS THEN
             V_ERR_MSG:='While doing insert into EmployeeTable'|| SUBSTR
            (
              sqlerrm,1,200
            )
            ;
            RAISE V_REJCT_RCRD;
        END;

EXCEPTION
when V_REJCT_RCRD then
        -- Log the error and continue processing other records
        INSERT INTO ErrorLog (error_id, emp_id, emp_name, emp_salary,ERROR_MESSAGE)
        VALUES (error_seq.NEXTVAL, p_employees(i).emp_id, p_employees(i).emp_name, p_employees(i).emp_salary,V_ERR_MSG);
END;
    END LOOP;
END;
/


----- Calling the procedure with the array as a parameter



SET serveroutput on;
DECLARE
    v_employees EmployeeListType;
BEGIN
    v_employees := EmployeeListType(
        EmployeeType(1, 'John Doe', 50000),
        EmployeeType(2, 'Jane Smith', 60000),
        EmployeeType(3, 'Invalid Record', 'Invalid Record') -- This record will cause an error
    );
InsertEmployee(v_employees);
END;
/

Expected result :

1 and 2 should insert in employeetable and 3 should log in errorlog table while iterating the loop.

Actual error encountering by the procedure

Exception I am encountering while executing the procedure

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Please help

This post has been answered by BluShadow on Jan 5 2024
Jump to Answer
Comments
Post Details
Added on Jan 5 2024
19 comments
351 views