Troubleshoot ORA-06512: at line

ORA-06512 is a common error faced by Oracle DBA, programmers as well as end users. ORA-06512 does not identify the root cause of the problem, rather it only prints the line number where the errors or exception happened. So just before ORA-06512 there will be additional error which we may need to investigate. If the errors come from any function or package or package body or procedure then with ORA-06512 there exists the name of the function or package or package body or procedure as well as the line number of those objects where error is occurred.

A simple demonstration of ORA-06512 error using PL/SQL anonymous block is shown below.

SQL> declare
  2  var1 number(3);
  3  begin
  4  var1:=1000;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

As this one is simply anonymous block and no package, function or procedure involved, so it does not generate any additional ORA-06512 error. However, just before ORA-06512 error there contains the root cause of the problem which is due to "ORA-06502: PL/SQL: numeric or value error: number precision too large". ORA-06512 simply notifies the problem happened at line number 4 of the anonymous block because we declare var1 length as 3 digits but we were going to insert 4 digits.

Now let's look a small variation of above error. Same code with a procedure.

SQL> create or replace procedure ORA06512_demo as
  2   var1 number(3);
  3   begin
  4   var1:=1000;
  5   end;
  6  /

Procedure created.

SQL> exec ORA06512_demo
BEGIN ORA06512_demo; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "ARJU.ORA06512_DEMO", line 4
ORA-06512: at line 1

Here notice as the code is run by a procedure and procedure contains same error so ORA-06512 is generated twice. If there is several ORA-06512 error then you should first take care of the first one. It is specified in which procedure as well as in which line problem happened. Then step by step you should fix second, third and so on if after fixing first one you get any more. Here "ORA-06512: at line 1" error is generated for the statement "exec ORA06512_demo".

To make you more clear, I will show you a more detail example.

SQL> create or replace procedure ORA06512_DEMO_SUB2(var1in number, string1in varchar2) as
  2    var1 number;
  3    var2 number;
  4    var3 number;
  5    begin
  6    var1:=var1in;
  7    var2:=string1in;
  8    var3:='a';
  9    end;
 10   /

Procedure created.

SQL>  create or replace procedure ORA06512_DEMO_SUB1(string1 varchar2) as
  2    var1 number ;
  3    begin
  4    var1:=100;
  5    ORA06512_DEMO_SUB2(var1,string1);
  6    end;
  7   /

Procedure created.

SQL>  create or replace procedure ORA06512_DEMO_MAIN as
  2    begin
  3    ORA06512_DEMO_SUB1('This is a string');
  4    end;
  5   /

Procedure created.

SQL>  exec ORA06512_DEMO_MAIN;
BEGIN ORA06512_DEMO_MAIN; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "ARJU.ORA06512_DEMO_SUB2", line 7
ORA-06512: at "ARJU.ORA06512_DEMO_SUB1", line 5
ORA-06512: at "ARJU.ORA06512_DEMO_MAIN", line 3
ORA-06512: at line 1

Here you see the first ORA-06512 is generated for the procedure "ARJU.ORA06512_DEMO_SUB2" and line 7. So you should take care of that first. The main cause of the problem is for ORA-06502 which is shown just before very first of ORA-06512 error. So fix the "ARJU.ORA06512_DEMO_SUB2" procedure first by changing datatype and then "ARJU.ORA06512_DEMO_SUB1" and so on.

Let's try to fix the problem. So deal with "ARJU.ORA06512_DEMO_SUB2" procedure. We see input parameter string1in is datatype of varchar2. Within procedure it is assigned to variable var2 which is number datatype, so we need to change it to varchar2. Also var3 is number datatype, so we will assign number variable into it not any char type. So doing these two changes and then run the main procedure ORA06512_DEMO_MAIN.

SQL> create or replace procedure ORA06512_DEMO_SUB2(var1in number, string1in varchar2) as
  2    var1 number;
  3    var2 varchar2(20);
  4    var3 number;
  5    begin
  6    var1:=var1in;
  7    var2:=string1in;
  8    var3:=10;
  9    end;
 10   /

Procedure created.

SQL>   exec ORA06512_DEMO_MAIN;

PL/SQL procedure successfully completed.

It looks perfect.

Related Documents
ORA-39127 ORA-04063 ORA-06508 ORA-06512 package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-13600, QSM-00775, ORA-06512 when running DBMS_ADVISOR
Expdp fails with ORA-31626, ORA-31633, ORA-06512, ORA-01031
Export Full=y fails with PLS-00201 ORA-06510 ORA-06512
Expdp fails with ORA-39001,ORA-39169,ORA-39006,ORA-39022