How to capture error stack when using forall in PL/SQL?

0 pts.
Tags:
Oracle
PL/SQL
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;
ASKED: October 30, 2007  11:58 PM
UPDATED: October 31, 2007  12:26 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following