The ORA-6512 is a generic placeholder for the PLSQL call stack when an exception is unwound. Each line generated provides information on the PLSQL program unit and line number that results in the actual error.
Cause: Backtrace message as the stack is unwound by unhandled exceptions.
Action: Fix the problem causing exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA.
ORA-06512 is simply a series of related messages issued at different levels of Oracle. in which the message directly preceding ORA-06512 will list the reason for the error.
The ORA-06512 error itself does not indicate the actual error. It normally indicates the line number at which the Oracle PL/SQL code has caused an error. There will be another main error that occurred in your process and that error happened in the line number as mentioned in the ORA-06512 message description.
declare myname varchar2(5); begin myname := 'Nimish Garg'; end; / ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 4
Here the main error is “character string buffer too small” for the variable “myname” and it can be corrected by increasing the size of “myname”
declare myname varchar2(20); begin myname := 'Nimish Garg'; end; /
create table products (id integer, description varchar2(5));
CREATE OR REPLACE PROCEDURE TEST_ERROR_STACK AS V_ID INTEGER; V_DESCRIPTION VARCHAR2(100); BEGIN v_ID := 100; v_DESCRIPTION := '2007 Honda CRV'; INSERT INTO PRODUCTS VALUES (V_ID, V_DESCRIPTION); END TEST_ERROR_STACK; / Procedure created.
SQL> exec TEST_ERROR_STACK; BEGIN TEST_ERROR_STACK; END; * ERROR at line 1: ORA-12899: value too large for column "SCOTT"."PRODUCTS"."DESCRIPTION" (actual:14, maximum: 5) ORA-06512: at "SCOTT.TEST_ERROR_STACK", line 7 ORA-06512: at line 1
The actual error for this test case scenario is ORA-12899: value too large for column. For determining what code may have caused the ORA-12899, we need to look at the entire call stack and the program unit and line of code where the error originates. The ORA-6512 is an important placeholder that reports the PLSQL call stack for a specific error that originates from a PLSQL program unit or anonymous block.
The actual error will allows be at the top of the call stack. i.e ORA-12899: value too large for column. The PLSQL call stack is read from the bottom up:
ERROR at line 1: ORA-12899: value too large for column "SCOTT"."PRODUCTS"."DESCRIPTION" (actual:14, maximum: 5) ORA-06512: at "SCOTT.TEST_ERROR_STACK", line 7 ORA-06512: at line 1
Walking the Call Stack:
Line 7 in SCOTT.TEST_ERROR_STACK is the line of code that is executing when the error is thrown. You can use the PLSQL call stack to line up the source and line numbers reported to identify the offending specific line of code. i.e Use SQL Developer, SQL PLUS or query the source from the user_source.
In our scenario above, line 7 points to the code:
INSERT INTO PRODUCTS VALUES (V_ID, V_DESCRIPTION);
If we perform a code review around this line of code, we will see we have written code that tries to insert a larger string than the table column can hold.
Option 1: Query the Data Dictionary for the source and identify the line of code.
SQL> COL TEXT HEADING 'SOURCE LINE' FORMAT A60 SQL> select text, line from user_source where name='TEST_ERROR_STACK'; SOURCE LINE LINE ------------------------------------------------------------ ---------- PROCEDURE TEST_ERROR_STACK AS 1 V_ID INTEGER; 2 V_DESCRIPTION VARCHAR2(100); 3 BEGIN 4 v_ID := 100; 5 v_DESCRIPTION := '2007 Honda CRV'; 6 INSERT INTO PRODUCTS VALUES (V_ID, V_DESCRIPTION); 7 END TEST_ERROR_STACK; 8 8 rows selected.
SQL> COL TEXT HEADING 'SOURCE LINE' FORMAT A60 SQL> select text, line from user_source where name='TEST_ERROR_STACK'and LINE=7; SOURCE LINE LINE ------------------------------------------------------------ ---------- INSERT INTO PRODUCTS VALUES (V_ID, V_DESCRIPTION); 7
Option 2: Use a SQL Tool such as SQL Developer to navigate to the offending line of code.
Choose the “Go to Line” option from the Navigate Menu to move the focus to the offending line of code in the source editor.
The ORA-6512 error is a generic placeholder for reporting the PLSQL call stack and can be very helpful for finding the offending line of code that results in a specific error. The ORA-6512 is not the actual error but is used to provide additional information for the specific line number in the PLSQL program unit that caused the actual error reported at the top of the call stack. Once the line number is identified, this information can be used as a starting place for further investigation that may be in the form of a code review, adding additional debug statements around that section of code as appropriate or identifying specific SQL that may be causing issues which can then be isolated outside the code and tested standalone for root cause.