How do you capture all the errors in an error stack when using forall/bulk processing within PL/SQL?
This is the error stack:
ORA-12801: error signaled in parallel query server P000
ORA-01722: invalid number
The only error captured in PL/SQL is the first error message but the second is actually more important because it gives specifics:
ORA-12801: error signaled in parallel query server P000
Here's how to reproduce:
-- prepare session
-- force parallel and set serverout on
alter session force parallel dml;
set serverout on;
DROP TABLE test_parallel;
-- CREATE TABLE
CREATE TABLE test_parallel
(A NUMBER);
-- INSERT DATA
INSERT
INTO test_parallel
(a)
VALUES (1);
INSERT
INTO test_parallel
(a)
VALUES (2);
COMMIT;
-- EXAMPLE OF ERROR STACK
DELETE /*+ parallel(t8) */
FROM test_parallel t
WHERE a = 'abc';
-- PROCEDURE
DECLARE
TYPE t_test IS TABLE OF VARCHAR2(10)
INDEX BY PLS_INTEGER;
v_test t_test;
v_errInt PLS_INTEGER;
v_errM VARCHAR2(500);
v_errNum number;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
v_test(1) := 'abc';
v_test(2) := 1;
BEGIN
FORALL i IN 1..v_test.COUNT
SAVE EXCEPTIONS
DELETE
FROM test_parallel
WHERE a = TO_NUMBER(v_test(i));
EXCEPTION
WHEN dml_errors THEN
-- handle bulk errors
FOR x IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
v_errInt := SQL%BULK_EXCEPTIONS(x).error_index;
v_errNum := SQL%BULK_EXCEPTIONS(x).error_code * -1;
v_errM := SUBSTR(SQLERRM(v_errNum),1,600);
dbms_output.put_line('ONLY ERROR MESSAGE THAT GETS LOGGED FROM PROCEDURE.');
dbms_output.put_line('errInt: '||v_errInt);
dbms_output.put_line('errNum: '||v_errNum);
dbms_output.put_line('errMsg: '||v_errM);
END LOOP;
END;
COMMIT;
dbms_output.put_line('SUCCESS');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line('other err: '||SUBSTR(SQLERRM,1,100));
END;
/
SELECT *
FROM test_parallel;
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: