How to resolve ORA-06512 at line num

Description of ORA-06512 in Oracle database

ORA-06512  in oracle 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 6512 does not indicate the actual error, but the line number of the unhandled error in the PLSQL code. ORA-6512 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:

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