On: April 25, 2022
4 mins read
Hassan AbdElrahman
Introduction
“ORA-06512 at line num” is a common error message that Oracle Database users encounter while executing PL/SQL code. In this article we are going to provides guidance on how to resolve the error, including possible causes and troubleshooting strategies. It is a helpful resource for anyone experiencing the ORA-06512 error.
ORA-06512 error is a backtrace message that appears when unhandled exceptions occur in PLSQL code. It’s a catch-all error for PLSQL exceptions and is commonly encountered. To solve this, identify the root cause of the exception, check the error stack trace, and use debugging tools like DBMS_TRACE or DBMS_OUTPUT to troubleshoot the issue.
ORA-06512 at Line Error Cause
This is usually the last of a message stack and indicates where a problem occurred in the PL/SQL code. Reference: Oracle documentation
The ORA-06512: At Line (n) error message is a generic PL/SQL error message that happens when an exception is not handled within PL/SQL program.
(n) represent the line number that causes this error to be displayed. it refers to the exact line number within the program to facilitate the troubleshooting process.
Note: If the PL/SQL program has multiple issues in different places and doesn’t have an exception handler section, Oracle will raise and point to the first line that has the issue until fixing it, then check other lines in the case has issues and so on so forth.
ORA-06512 Solution
A common question is How do I fix error ORA-06512? There are two solutions to resolve the ora06512 error, which are:
- Fixing the issue within the PL/SQL program that causes this unhandled exception to raise.
- Write an exception handler for this unhandled exception.
Solution 1: Fix the issue within the PL/SQL program
Let’s take the first example, which expresses the error
DECLARE
L_SITE_NAME VARCHAR2 (6);
BEGIN
L_SITE_NAME := 'Oraask.com';
END;In the above example, we have an anonymous PL/SQL block; when we try to execute it, it will raise an ORA-06512 error as follows:
Error report –
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
- 00000 – “PL/SQL: numeric or value error%s”
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
In this error report, the first error ( ORA-06502 ) indicates the exact error that occurred within the program, while the second error line of the error report ( ORA-06512 ) indicates the line number that causes that error.
Basically, in this PL/SQL program, we have tried to assign character values more than L_SITE_NAME variable should hold. We could correct this error by increasing the length of the variable “L_SITE_NAME” to be VARCHAR2 (10).
DECLARE
L_SITE_NAME VARCHAR2 (10);
BEGIN
L_SITE_NAME := 'Oraask.com';
END;Now after updating our script and executing the anonymous PL/SQL block again, it will execute successfully.
PL/SQL procedure successfully completed.
Solution 2: Write an exception handler
Let’s take the second example, which expresses the error
DECLARE
L_OBJECT_CNT NUMBER(1);
BEGIN
SELECT COUNT(OBJECT_ID)
INTO L_OBJECT_CNT
FROM ALL_OBJECTS
WHERE OWNER = 'SYS';
END;In the above example, we have an anonymous PL/SQL block; when we try to execute it, it will raise an ORA-06512 error as follows:
Error report –
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
In this error report, the first error ( ORA-06502 ) indicates the exact error that occurred within the program, while the second error line of the error report ( ORA-06512 ) indicates the line number that causes that error.
Basically, in this PL/SQL program, we have tried to assign numerical values more than the L_OBJECT_CNT variable should hold. We could correct this error by adding an exception handling section in the program like below.
DECLARE
L_OBJECT_CNT NUMBER (1);
BEGIN
SELECT COUNT (OBJECT_ID)
INTO L_OBJECT_CNT
FROM ALL_OBJECTS
WHERE OWNER = 'SYS';
DBMS_OUTPUT.PUT_LINE ('L_OBJECT_CNT' || L_OBJECT_CNT);
EXCEPTION
WHEN OTHERS THEN
L_OBJECT_CNT := 0;
END;Now after updating our script by adding the exception-handling part and executing the anonymous PL/SQL block again, it will execute successfully.
PL/SQL procedure successfully completed.
Conclusion
ORA-06512 is a kind of error that aims to help developers troubleshoot by pinpointing the line number causing the problem. And there are multiple ways to handle this kind of error which we explained here in this topic.
Learn the cause and how to resolve the ORA-06512 error message in Oracle.
Description
When you encounter an ORA-06512 error, the following error message will appear:
- ORA-06512: at line <number>
Cause
This error is caused by the stack being unwound by unhandled exceptions in your PLSQL code.
The options to resolve this Oracle error are:
- Fix the condition that is causing the unhandled error.
- Write an exception handler for this unhandled error.
- Contact your DBA for help.
The ORA-06512 error message indicates the line number of the unhandled error in the PLSQL code. This is quite useful when troubleshooting.
Resolution
Option #1 — Fix the Error Condition
Let’s look at an example of how to resolve an ORA-06512 error by fixing the error condition.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 100; 6 END; 7 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06512 error as follows:
SQL> execute TestProc(); BEGIN TestProc(); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "EXAMPLE.TESTPROC", line 5 ORA-06512: at line 1
The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.
In this example, you’ve tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this error by redefining the v_number variable as number(3).
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(3); 4 BEGIN 5 v_number := 100; 6 END; 7 / Procedure created.
And now when we execute our TestProc procedure, the ORA-06512 error has been resolved.
SQL> execute TestProc(); PL/SQL procedure successfully completed.
Option #2 — Write an Exception Handler
Let’s look at an example of how to resolve an ORA-06512 error by writing an exception handler.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 100; 6 END; 7 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06512 error as follows:
SQL> execute TestProc(); BEGIN TestProc(); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "EXAMPLE.TESTPROC", line 5 ORA-06512: at line 1
The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.
In this example, you’ve tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this by writing an exception handler to set the v_number variable to 99 (so that it is only 2 digits) when this error occurs.
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 100; 6 EXCEPTION 7 WHEN OTHERS THEN 8 v_number := 99; 9 END; 10 / Procedure created.
And now when we execute our TestProc procedure, the ORA-06512 error has been resolved.
SQL> execute TestProc(); PL/SQL procedure successfully completed.
Did you get an ORA-06512 error when running an SQL query? Learn what this error is and how to resolve it in this article.
The ORA-06512: At Line error is caused by an exception in your query that is not handled. The error message you get will look similar to this:
ORA-06512: at line n.
Where n is a line number.
This ORA-06512: At Line error message is a generic PL/SQL error message that happens when an exception is not handled.
When an error message is displayed, a stack trace is also shown, which shows the sequence of calls made to the database by the code. There might be several lines here, and one of them will be the ORA-06512 error.
Here’s an example from SQL Developer”
So, how do you resolve it?
ORA-06512 Solution
There are two main ways to resolve this error:
- Fix the code that is causing the error
- Add an exception handler to your PL/SQL code.
I’ll show an example of this error, and how to resolve it using both of these errors in this article.
Example of This Error
Let’s say you had this PL/SQL stored procedure:
CREATE OR REPLACE PROCEDURE TestOutput AS
pName VARCHAR2(5);
BEGIN
pName := 'Steven';
END;
/This is a simple procedure that sets a variable.
If we run the statement to create the procedure, there is no issue.
Procedure TESTOUTPUT compiledNow, if we run the procedure itself:
EXEC TestOutput;Error starting at line : 8 in command - EXEC TestOutput Error report - ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SYSTEM.TESTOUTPUT", line 4 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
It gives us an error that wasn’t found when it was compiled.
If you look at the message, you’ll see our ORA-06512 error:
ORA-06512: at "SYSTEM.TESTOUTPUT", line 4 ORA-06512: at line 1
However, the actual error that occurred is further up in the message.
>ORA-06502: PL/SQL: numeric or value error: character string buffer too small
If we resolve this error, then the ORA-06512 should also disappear.
Let’s take a look at the procedure.
CREATE OR REPLACE PROCEDURE TestOutput AS
pName VARCHAR2(5);
BEGIN
pName := 'Steven';
END;
/It looks like the error is being triggered on line 4, where pName is being initialised. It’s happening because the pName variable is 5 characters long, but the variable is 6 characters.
We can resolve this in two ways. First, we can adjust the size of the variable.
CREATE OR REPLACE PROCEDURE TestOutput AS
pName VARCHAR2(6);
BEGIN
pName := 'Steven';
END;
/I’ve increased pName from 5 to 6 characters to handle the value of “Steven”.
Or, we can add an exception handler. This will mean that any errors that are found are treated in a certain way.
Let’s say if the value is over 5 characters then we trim it to 5 characters.
CREATE OR REPLACE PROCEDURE TestOutput AS
pName VARCHAR2(5);
BEGIN
pName := 'Steven';
EXCEPTION
WHEN OTHERS THEN
pName := SUBSTR('Steven', 1, 5);
END;
/If we run this procedure now, then the error does not appear.
ORA-06512 at sys.utl_file line 536
Are you getting this specific error message, which mentions the sys.utl_file package?
This is most likely happening because of a permissions issue when exporting a file to a directory. This question on StackExchange and this question on StackOverflow are a couple of examples.
To resolve it, you can do several things:
- Make sure the user that is running the procedure has write access to the directory you’re mentioning. Double-check this – as it can often seem like the right permissions are defined but they are not.
- Check that the directory is correct. It often needs a trailing slash, or if using a network directory, the full path might be needed.
So, in summary, the ORA-06512 error appears because there is an unhandled error in the PL/SQL code being called. To resolve it, either fix the error in the code or add an exception handler.
If you just want to export data once-off and not in PL/SQL, you could use SQL Developer’s export functionality which I’ve written about here.
ORA-06512 is part of the error stack. It gives us the line number where the exception occurred, but not the cause of the exception. That is usually indicated in the rest of the stack (which you have still not posted).
In a comment you said
«still, the error comes when pNum is not between 12 and 14; when pNum
is between 12 and 14 it does not fail»
Well, your code does this:
IF ((pNum < 12) OR (pNum > 14)) THEN
RAISE vSOME_EX;
That is, it raises an exception when pNum is not between 12 and 14. So does the rest of the error stack include this line?
ORA-06510: PL/SQL: unhandled user-defined exception
If so, all you need to do is add an exception block to handle the error. Perhaps:
PROCEDURE PX(pNum INT,pIdM INT,pCv VARCHAR2,pSup FLOAT)
AS
vSOME_EX EXCEPTION;
BEGIN
IF ((pNum < 12) OR (pNum > 14)) THEN
RAISE vSOME_EX;
ELSE
EXECUTE IMMEDIATE 'INSERT INTO M'||pNum||'GR (CV, SUP, IDM'||pNum||') VALUES('||pCv||', '||pSup||', '||pIdM||')';
END IF;
exception
when vsome_ex then
raise_application_error(-20000
, 'This is not a valid table: M'||pNum||'GR');
END PX;
The documentation covers handling PL/SQL exceptions in depth.
- Find out more.
Знатоки баз данных узнают имя Oracle как мощную силу в мире реляционных систем управления базами данных (СУБД). Oracle производит очень мощные решения для СУБД на протяжении десятилетий и остается лидером в этой области. Многие конечные пользователи продуктов баз данных могут быть сбиты с толку или озадачены, когда они используют готовое решение, а оно выдает сообщение об ошибке. Одним из распространенных сообщений об ошибке, выдаваемых Oracle, является ошибка ORA-06512.
Содержание
Oracle — это система управления базами данных, которая существует уже сорок лет в различных формах. Первоначально она использовала схему SCOTT, названную в честь одного из первых сотрудников Oracle. Вы даже впервые вошли в Oracle с именем пользователя «scott» и паролем «tiger», названным в честь кота Скотта. Сейчас существует несколько схем, используемых в зависимости от того, для чего вы используете Oracle.
Если вы хотите узнать больше о Oracle с нуля, эта страница очень полезна .
Исправление ошибок ORA-06512
В Oracle ошибка ORA-06512 — это ошибка общего исключения, которая говорит вам, где что-то идет не так. Это одна из наименее специфичных ошибок, выдаваемых Oracle, поскольку она сообщает только о наличии проблемы, но не о том, что именно идет не так.
Например, типичное сообщение об ошибке может выглядеть так:
«ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at «DATABASE_NAME», line 66
ORA-06512: at line 1′′
Первая строка говорит о типе ошибки, в данном случае запрос возвращает больше данных, чем ожидает запрос, поэтому он не знает, как это обработать. Код ‘ORA-01422’ — это фактический код ошибки, на который вам нужно обратить внимание. ORA-06512 — это просто общий код ошибки.
Вторая строка говорит вам, где происходит ошибка. DATABASE_NAME — это имя базы данных, в которой вы работаете в данный момент. Строка 66 — это строка, в которой происходит ошибка, и именно ее нужно проверить, чтобы исправить ошибку.
Третья строка в синтаксисе ошибки говорит вам, откуда происходит вызов. Проверьте первую строку и вы увидите обращение к DATABASE_NAME.
Чтобы исправить эту конкретную ошибку, вам нужно исправить проблему, вызванную ORA-01422, а именно ‘exact fetch возвращает больше запрошенного количества строк’ или добавить обработчик исключений, чтобы сказать Oracle игнорировать ее. Поскольку исправление основной проблемы всегда предпочтительнее, то лучше поступить следующим образом.
Вы можете сделать две вещи. Если вы ожидаете, что запрос вернет больше одной строки, вы можете изменить его так, чтобы он не был удивлен. Если вы ожидаете, что запрос вернет только одну строку, вы можете изменить его и для этого.
Ожидая больше одной строки:
for X in ( select * from t where … )
loop
— обработать запись X здесь
end loop;
Это должно устранить ошибку в запросах к базе данных, где возвращается более одной строки.
Если вы ожидаете возврата только одной строки, вы можете попробовать:
begin
select * into ….
from t where ….
process….
exception
when NO_DATA_FOUND then
error handling code when no record is found
when TOO_MANY_ROWS then
error handling code when too many records are found
end;
Этот второй метод должен доставить только один ряд без выброса ошибки ‘ORA-01422: exact fetch returns more than requested number of rows’ и, следовательно, оригинальной ошибки ORA-06512.
Вы также можете изменить запрос так, чтобы он возвращал только первую строку ответа из нескольких строк. Это может сработать, если у вас нет полного контроля над базой данных или вы не хотите сильно возиться, но ответ все равно нужен.
declare
c1 cursor for select * from t where …
begin
open c1;
fetch c1 into ….
if ( c1%notfound ) then
error handling for no record found
end if;
close c1;
end;
(Если вы знаете SQL, вас могут немного смутить эти командные строки… Oracle использует не Transact-SQL, а собственное процедурное расширение языка SQL, PL/SQL. Хотя он похож на Transact-SQL, PL/SQL делает много умных вещей и является очень мощным инструментом сам по себе. Вы можете найти этот FAQ по PL/SQL полезным при попытке изучить Oracle.)
Итак, основной урок заключается в том, что ошибка ORA-06512, сама по себе, не является чем-то, что вы можете исправить напрямую. Вместо этого вы должны выяснить, в чем собственно заключается ошибка, о чем вам расскажут другие коды ошибок, а затем устранить эти ошибки одну за другой.
У вас есть советы или рекомендации по Oracle, которыми вы можете поделиться? Расскажите нам о них в комментариях!