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.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
Description 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 Oracle 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 oracle 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. Check the solution by clicking the link
Hope you like this post on ORA-6512 Error Message
Related articles
ORA-00911
ORA-03113
ORA-00257
ORA-27154
ORA-29913
ORA-20001 in Gather schema stats on 11g(FND_HISTOGRAM_COLS)
ORA-06512 Error Message
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 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 error
ORA-06512: at line 12
The preceding message names the reason for the error (“numeric or value error”) while Ora-06512 indicates the line number of the error (line 12).
There are 3 ways to resolve Ora-06512:
Fix the error causing the unhandled error.
Write an exception handler for the unhandled error.
Contact the database administrator (DBA).
The Solution
The steps of fixing the error will depend on the error itself. This is an example of an Ora-06512 message in a “AProc” procedure for which the error is fixed:
CREATE OR REPLACE PROCEDURE AProc
AS
a_number number(3);
BEGIN
a_number := 1000;
END;
/
When this procedure is written, you will see the following error message:
execute AProc();
BEGIN AProc(); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at “EXAMPLE.APROC”, line 5
ORA-06512: at line 1
The preceding error indicates the user the error (number precision too large). Ora-06512 indicates the line number in which the error occurred (line 5). In this case, the error occurred because a 4-digit number was assigned to the variable a_number which is set to handle only 3 digits. The error is resolved by setting the variable a_number to handle 4 digits:
CREATE OR REPLACE PROCEDURE AProc
AS
a_number number(4);
BEGIN
a_number := 1000;
END;
/
Write an Exception Handler
To resolve the same example error as above, you may choose to write an exception handler rather than fixing the error. The following is how you would write an exception handler:
CREATE OR REPLACE PROCEDURE AProc
AS
a_number number(3);
BEGIN
a_number := 1000;
EXCEPTION
WHEN OTHERS THEN
a_number := 999;
END;
/
Since the variable a_number can only handle 3-digits, you can write an exception handler to set the variable a_number to a 3-digit number (999) when this error occurs.
If you cannot resolve the error by fixing the error or writing an exception hander, contact your DBA.
On: April 25, 2022
3 mins read
Hassan AbdElrahman
Introduction
In this tutorial, we are going to explain how to resolve ORA-06512: at line num error in Oracle.
ORA-06512 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
1 DECLARE
2 L_SITE_NAME VARCHAR2 (6);
3 BEGIN
4 L_SITE_NAME := 'Oraask.com';
5 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
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.
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).
1 DECLARE
2 L_SITE_NAME VARCHAR2 (10);
3 BEGIN
4 L_SITE_NAME := 'Oraask.com';
5 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
1 DECLARE
2 L_OBJECT_CNT NUMBER(1);
3 BEGIN
4 SELECT COUNT(OBJECT_ID)
5 INTO L_OBJECT_CNT
6 FROM ALL_OBJECTS
7 WHERE OWNER = 'SYS';
8 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.
1 DECLARE
2 L_OBJECT_CNT NUMBER (1);
3 BEGIN
4 SELECT COUNT (OBJECT_ID)
5 INTO L_OBJECT_CNT
6 FROM ALL_OBJECTS
7 WHERE OWNER = 'SYS';
8
9 DBMS_OUTPUT.PUT_LINE ('L_OBJECT_CNT' || L_OBJECT_CNT);
10 EXCEPTION
11 WHEN OTHERS THEN
12 L_OBJECT_CNT := 0;
13 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.