- 05 Nov, 2021
- 0 Comments
- 2 Mins Read
ORA-06512 Error Message
ORA-06512 ERROR MESSAGE
Description
When you encounter an ORA-06512 error, the following error message will appear:
ORA-06512: at line
Cause
This error is caused by the stack being unwound by unhandled exceptions in your PLSQL code.
The options to resolve this Oracle error are:
1.Fix the condition that is causing the unhandled error.
2.Write an expention handler for this unhandled error.
3.Contact your DBA for help.
The ORA-06512 error message indicates the line number of the unhandled error in the PLSQL code. This is quite useful when troubleshooting.
Resolution
Option #1 – Fix the Error Condition
Let’s look at an example of how to resolve an ORA-06512 error by fixing the error condition.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 100; 6 END; 7 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06512 error as follows:
SQL> execute TestProc(); BEGIN TestProc(); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "EXAMPLE.TESTPROC", line 5 ORA-06512: at line 1
The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.
In this example, you’ve tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this error by redefining the v_number variable as number(3).
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(3); 4 BEGIN 5 v_number := 100; 6 END; 7 /
Procedure created.
And now when we execute our TestProc procedure, the ORA-06512 error has been resolved
SQL> execute TestProc(); PL/SQL procedure successfully completed.
Option #2 – Write an Exception Handler
Let’s look at an example of how to resolve an ORA-06512 error by writing an exception handler.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 100; 6 END; 7 /
Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06512 error as follows:
SQL> execute TestProc(); BEGIN TestProc(); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "EXAMPLE.TESTPROC", line 5 ORA-06512: at line 1
The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.
In this example, you’ve tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this by writing an exception handler to set the v_number variable to 99 (so that it is only 2 digits) when this error occurs.
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 100; 6 EXCEPTION 7 WHEN OTHERS THEN 8 v_number := 99; 9 END; 10 /
Procedure created.
And now when we execute our TestProc procedure, the ORA-06512 error has been resolved.
SQL> execute TestProc(); PL/SQL procedure successfully completed.