How to resolve ORA-06512 at line num

Last updated on July 26th, 2018 at 09:31 am

Description of ORA-06512

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

Here is what documentation says about this error

ORA-06512Reference :Oracle documentation

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 errorORA-06512: at line 1112

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-1422 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.

So we can resolve the problem by carefully checking the full error stack and taking required action item

Hope you like this post on ORA-6512 Error Message


Leave a Reply