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.

ORA-06512 Cause

The error message you get will look similar to this:

ORA-06512: at line n.

Where n is a line number.

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

So, how do you resolve it?

 

ORA-06512 Solution

There are two main ways to resolve this error:

  1. Fix the code that is causing the error
  2. 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 compiled

Now, 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:

  1. 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.
  2. 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.

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!

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

Want To Improve Your Oracle SQL?

Improve your Oracle SQL queries by signing up to my free 10-day Oracle Tips email course.

BONUS: Oracle SQL functions PDF cheat sheet

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Want To Improve Your Oracle SQL?

Improve your Oracle SQL queries by signing up to my free 10-day Oracle Tips email course.

BONUS: Oracle SQL functions PDF cheat sheet

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit