Hi Experts,
I have to write a procedure for inserting purposes while using the BULK COLLECT method. In case any error will happen at inserting that error captured by SAVE EXCEPTION, now my question is if it is possible to capture the error using the error log table in the EXCEPTION part and i write a procedure for this purpose through an error(target table name: bulk_emp1, error log table name: error_log1) using ORACLE 18c
CREATE OR REPLACE PROCEDURE bulk_sp
AS
TYPE first_name_typ IS TABLE OF employees.first_name%TYPE;
TYPE employee_id_typ IS TABLE OF employees.employee_id%TYPE;
TYPE dep_id_typ IS TABLE OF employees.department_id%TYPE;
CURSOR rec_c
IS
SELECT employee_id,
first_name,
department_id
FROM employees;
v_first_name first_name_typ;
v_emp_id employee_id_typ;
v_dep_id dep_id_typ;
v_err_count NUMBER := 0;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE bulk_emp';
OPEN rec_c;
LOOP
FETCH rec_c BULK COLLECT INTO v_emp_id, v_first_name, v_dep_id LIMIT 50;
EXIT WHEN rec_c%NOTFOUND;
END LOOP;
BEGIN
FORALL i IN v_emp_id.FIRST..v_emp_id.COUNT SAVE EXCEPTIONS
INSERT INTO bulk_emp1 VALUES (v_emp_id(i), v_first_name(i), v_dep_id(i));
COMMIT;
EXCEPTION
WHEN others THEN
v_err_count := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..v\_err\_count LOOP
INSERT INTO error\_log1 (error\_code, error\_index, employee\_id, first\_name, department\_id)
VALUES (SQL%BULK\_EXCEPTIONS(i).ERROR\_CODE,
SQL%BULK\_EXCEPTIONS(i).ERROR\_INDEX,
v\_emp\_id(SQL%BULK\_EXCEPTIONS(i).ERROR\_INDEX),
v\_first\_name(SQL%BULK\_EXCEPTIONS(i).ERROR\_INDEX),
v\_dep\_id(SQL%BULK\_EXCEPTIONS(i).ERROR\_INDEX));
END LOOP;
END;
END;
/
this procedure throw following error:
38/9 PL/SQL: SQL Statement ignored
39/20 PL/SQL: ORA-00911: invalid character
please help me to resolve this bug…