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