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!

How to resolve PL/SQL: ORA-00911: invalid character ?

user-mez92Jun 30 2023

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…

Comments
Post Details
Added on Jun 30 2023
1 comment
2,126 views