How to resolve ORA-06512 at line num

Last updated on February 23rd, 2019 at 06:08 am

Description of ORA-06512 in Oracle database

ORA-06512  is one of the common error seen in PLSQL programs in Oracle database

Here is what Oracle documentation says about this error

Reference :Oracle documentation

You can find this always by typing below in Unix

oerr ORA 06512

Explanation of the ORA-06512 error:

Error Ora-06512 means the backtrace message as the stack is being unwound by unhandled exceptions in your PLSQL code. This is a catch-all error for All PLSQL exceptions and is commonly seen.

Ora-06512 does not indicate the actual error, but the line number of the unhandled error in the PLSQL code. Ora-06512 will typically appear in a message stack in which the preceding message names the reason for the error, such as in the following example:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1112

In the above , the yellow highlighted error is the main error

Reasons for ORA-06512 error:

The options to resolve this Oracle error are:

1) Fix the condition that is causing the unhandled error.
2) Write an exception handler for this unhandled error.
3) Contact your DBA for help.

Examples of ORA-06512 errors

Lets see few example on how to work on it

CREATE OR REPLACE PROCEDURE Testora_proc
AS
Site_name varchar2(5);
begin
site_name := 'techgoeasy.com';
end;
/
Procedure created.
sql> Exec Testora_proc
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

In the example given above, procedure get compiled successfully but it gave errors while execution

So actual error is the error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

We can easily correct it by increasing the size of the variable

CREATE OR REPLACE PROCEDURE Testora_proc
AS
Site_name varchar2(20);
begin
site_name := 'techgoeasy.com';
end;
/
Procedure created.
SQL> Exec Testoraproc
PL/SQL procedure successfully completed.

We can solve this using exception handler also

CREATE OR REPLACE PROCEDURE Testora_proc
AS
Site_name varchar2(5);
begin
site_name := 'techgoeasy.com';
EXCEPTION
WHEN OTHERS THEN
site_name := 'tech';
end;
/
Procedure created.SQL>Exec Testoraproc;
PL/SQL procedure successfully completed.

Another example would be

CREATE OR REPLACE PROCEDURE Testora_proc
AS
node_name varchar2(5);
begin
select node into node_name from db_node;
end;
/
Procedure created
SQL> Exec Testora_proc;
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at “NODE_NAME”, line 4
ORA-06512: at line 1

Here ORA-01422 is the real error. we have more than 1 rows in db_node table and get multiple values for node.So one fix could be restricting one row

CREATE OR REPLACE PROCEDURE Testora_proc
AS
node_name varchar2(5);
begin
select node into node_name from db_node where rownum <2;
end;
/
Procedure created
SQL> Exec Testora_proc;
PL/SQL procedure successfully completed.

Another example would be

CREATE OR REPLACE PROCEDURE Testora_proc
AS
node_name varchar2(5);
begin
select node into node_name from db_node;
end;
/
Procedure created
SQL> Exec Testora_proc;
ORA-01403: no data found
ORA-06512: at “NODE_NAME”, line 4
ORA-06512: at line 1

Here ORA-01403 is the real error. we have no rows in db_node table and we are getting no data found error.So one fix could be putting exception handling

CREATE OR REPLACE PROCEDURE Testora_proc
AS
node_name varchar2(5);
begin
select node into node_name from db_node where rownum <2;
EXCEPTION WHEN NO_DATA_FOUND  
THEN 
node_name := 'tech'; 
end; 
/
Procedure created
SQL> Exec Testora_proc;
PL/SQL procedure successfully completed.

Some more examples can be on SYS.UTL_FILE

SQL> declare
F_LOG  utl_file.file_type;
begin
F_LOG := utl_file.fopen('TESTDIR','k', 'w');
end;
/  2    3    4    5    6
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4

Here the main error is ORA-29283.  Solution is given in the below post

How to solve ORA-29283 : invalid file operation

Hope you like this post on ORA-6512 Error Message