Blog

Learnomate Technologies > Blog > Oracle DBA > ORA-06512 Error Message

ORA-06512 Error Message

  • November 5, 2021
  • Posted by: Ankush Thavali
  • Category: Oracle DBA
No Comments

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.
ora-06512