Blog

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.

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.