ORA-06512: at line Error Messages
The ORA-06512 error message is a generic error message given to capture all PL/SQL error messages. There are multiple situations and solutions below so please read the full article.
The Problem
You are running a piece of PL\SQL and are receiving this message:
ORA-06502 and ORA-06512: at line <line number>
Note: There are multiple solutions to this problem so see below about which one applies to you.
One Possible Cause
Somewhere in your code you are declaring a variable to hold a value which is populated later on in the PL\SQL code.
For example, you have a numeric value which is 5 digits long and you are trying to store it in a variable which is a NUMBER(4) then it will be too large and you will get:
ORA06502: PL/SQL: numeric or value error: number precision too large and ORA06512: at line <line number>
Multiple Solutions
You need to increase the length of the variable which holds the value to at least a NUMBER(5).
There will be another error message appearing directly before the ORA-06512 error message, as the ORA-06502 error does in the example above, which will be of more use to help you identify the cause of the problem.
Personally, I’ve encountered several different errors which have the ORA 06512 error message in them. For example:
- ORA-06512: at “SYS.UTL_TCP” – this was the result of incorrect logic in my PL/SQL code when I was trying to find out the name of the database
- ORA-06512: at “SYSMAN.MGMT_JOB_UI”, line 4847 ORA-06512 – this error was thrown by the Oracle Enterprise Manager while I was trying to remove a job
- ORA-06512: at “SYS.UTL_SMTP” – After upgrading from Oracle 10g to 11g I got errors in PLSQL packages which tried to send out mail because I had not configured the Access Control Lists (ACLs) properly. On a similar note you might be getting the ORA-29278: SMTP transient error: 421 Service not available error to which there are multiple ways to fix it which I have written about in the article on my other website I just linked to.
I also received various error message such as ORA-01031: insufficient privileges and ORA-27486: insufficient privileges when trying to administer and use the scheduler.
One final point. As the range of different error messages above shows, the ORA-06512 error message is a generic error which is preceded by a another error message which can be more helpful; The ORA 06512 message is a catch-all error message from anything which errors within a PL/SQL block.
That’s all for now, happy hunting!
Like it, share it...
Category: PL/SQL
select table_name, to_number(extractvalue(xmltype(
dbms_xmlgen.getxml(‘select count(*) c from ‘||table_name))
,’/ROWSET/ROW/C’)) count from user_tables order by table_name ;
Sir,
What is the problem and rectify this query please…
thanks
select table_name, to_number(extractvalue(xmltype(
dbms_xmlgen.getxml(‘select count(*) c from ‘||table_name))
,’/ROWSET/ROW/C’)) count from user_tables order by table_name ;
is any error on this query ?