Oracle – Find A Needle In The Hay Stack – How To Pinpoint Data Errors Like “ORA-12899: value too large for column”

Nasty data-related errors:

If you are looking to resolve one of these types of errors, you are in the right place. Please keep reading.

ORA-01400: cannot insert NULL into (string)
ORA-01401: inserted value too large for column
ORA-02290: check constraint (string.string) violated
ORA-02291: integrity constraint (string.string) violated – parent key not found
ORA-02292: integrity constraint (string.string) violated – child record found
ORA-02293: cannot validate (string.string) – check constraint violated
ORA-01406: fetched column value was truncated
ORA-12899: value too large for column string (actual: string, maximum: string)

9 out of 10 Oracle professionals did not know this:

Below is a typical Oracle interview question we ask – 9 out of 10 Oracle professionals interviewed did not know this. Knowing this could mean the difference between minutes and hours of frustration.

Question:

You take a million rows from a staging table and “INSERT INTO” a real table.  You ran into an error because, there is one column in one row that has a bad value.

As you know well, Oracle does not give you the row and the column information as part of the error message to help you out.  It may simply be “ORA-01401: inserted value too large for column”.

How do you pin point the row and column causing the error?

Clarifications:

Can you define what you mean by bad value(s)?

A bad value is any value in any column and/or row that causes an error when INSERTing from source to the destination. It could be because of any one or more of the following:

  • the data length of the columns are slightly different between source and destination
  • the data types are somewhat different but not enough to make the INSERT fail at compile time
  • check constraints are different and enforced in destination
  • foreign key references are enforced and inserted values do not conform
  • there are triggers that do validation and raise an error for certain business-specific data conditions
  • etc…

Answers received:

  • I will insert row by row and wait for the loop to hit the row(s) with the error.

Sure, it will get the job done but certainly not efficiently. Would the approach be same if it was 10 million rows and multiple rows had errors?

  • I will divide the data into two sets, and try the insert with the two halves and continue to divide them until I get the row with the error

Tedious, but it works. At this point, you are reactive, not proactive. The data you wanted to work with may have vanished in the meantime or unavailable to query.

  • I will compare the data lengths of the source to the data lengths of the destination and determine the value with the issue

Again, it is very laborious and it will only catch one class of errors like “ORA-01401: inserted value too large for column”

  • ….other methods that are really inefficient or cumbersome to list here

Looking for the right answer

I cannot deny that I have used all of the techniques above but that is sometimes not the best way to find the issues in Oracle.

Question:

Do you know of the most efficient way to do it in Oracle?

Answer:

No.

As the saying goes, there are a thousand ways to skin a cat but there is only one efficient way to do this in Oracle when using INSERT INTO. There are other efficient ways like using SQL*Loader, external tables etc. when the source of data is different – for example a text file (by logging errors to an error log file). There is only one efficient way (that I know of) when working with INSERT INTO: using “LOG ERRORS INTO” clause of an INSERT statement

Please let me know if you know of other efficient ways to do this in Oracle 11g or below when using an INSERT INTO statement. The SAVE EXCEPTIONS clause of a FORALL is another option which is slightly different but solves the same issue. We will discuss that in a future tip.

Typical errors that one may run into include the following (this is not a complete list):

ORA-01400: cannot insert NULL into (string)

Cause: An attempt was made to insert NULL into previously listed objects.

Action: These objects cannot accept NULL values.

ORA-01401: inserted value too large for column

Cause: The value inserted was too large for the given column.

Action: Do not insert a value greater than what the column can hold.

ORA-02290: check constraint (string.string) violated

Cause: The values being inserted do not satisfy the named check constraint.

Action: do not insert values that violate the constraint.

ORA-02291: integrity constraint (string.string) violated – parent key not found

Cause: A foreign key value has no matching primary key value.

Action: Delete the foreign key or add a matching primary key.

ORA-02292: integrity constraint (string.string) violated – child record found

Cause: attempted to delete a parent key value that had a foreign key dependency.

Action: delete dependencies first then parent or disable constraint.

ORA-02293: cannot validate (string.string) – check constraint violated

Cause: an alter table operation tried to validate a check constraint to a populated table that had nocomplying values.

Action: Obvious

ORA-01406: fetched column value was truncated

Cause: The fetched column values were truncated.

Action: Use the right data types to avoid truncation.

ORA-12899: value too large for column string (actual: string, maximum: string)

Cause: An attempt was made to insert or update a column with a value which is too wide for the width of the destination column. The name of the column is given, along with the actual width of the value, and the maximum allowed width of the column. Note that widths are reported in characters if character length semantics are in effect for the column, otherwise widths are reported in bytes.

Action: Examine the SQL statement for correctness. Check source and destination column data types. Either make the destination column wider, or use a subset of the source column (i.e. use substring).

The best way to pin-point errors – LOG ERRORS INTO

Let us illustrate the magic that “LOG ERRORS INTO” clause does with an example.

In this example, we will create two identical tables. We will then alter the second table to reduce the column width and try to insert data from the first to the second expecting to capture details of the data related errors into an error log table an error log table TAB_B_ERRLOG – created by running EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(‘TAB_B’, ‘TAB_B_ERRLOG’);.

-- Create a table TAB_A that will act as source of data for INSERT INTO
DROP TABLE TAB_A;

CREATE TABLE TAB_A AS
SELECT *
FROM all_objects
WHERE
    owner IN ('SYS','SYSTEM')
    AND rownum < 10000; 

-- Create an empty table TAB_B that will act as the destination of data for the INSERT INTO
DROP TABLE TAB_B;

CREATE TABLE TAB_B AS
SELECT * FROM TAB_A
WHERE 0=1; 

--At this point TAB_A and TAB_B look identical..
--  except TAB_A has data and TAB_B is empty
DESC TAB_B;

CREATE TABLE TAB_B
(
  OWNER           VARCHAR2(30 BYTE)             NOT NULL,
  OBJECT_NAME     VARCHAR2(30 BYTE)             NOT NULL,
  SUBOBJECT_NAME  VARCHAR2(30 BYTE),
  OBJECT_ID       NUMBER                        NOT NULL,
  DATA_OBJECT_ID  NUMBER,
  OBJECT_TYPE     VARCHAR2(18 BYTE),
  CREATED         DATE                          NOT NULL,
  LAST_DDL_TIME   DATE                          NOT NULL,
  TIMESTAMP       VARCHAR2(19 BYTE),
  STATUS          VARCHAR2(7 BYTE),
  TEMPORARY       VARCHAR2(1 BYTE),
  GENERATED       VARCHAR2(1 BYTE),
  SECONDARY       VARCHAR2(1 BYTE)
)

--Let us alter TAB_B so that its OWNER column is only 10 bytes instead of the original 30
ALTER TABLE TAB_B
MODIFY(OBJECT_NAME VARCHAR2(10 BYTE));

--We know for a fact that there are object_name's that are more than 10 bytes...
--  We expect this INSERT INTO to fail
INSERT INTO TAB_B
SELECT * FROM TAB_A;

--Oracle 9i produces this error:
ORA-01401: inserted value too large for column

--Oracle 11g produces this error:
ORA-12899: value too large for column "MY_USER_ID"."TAB_B"."OBJECT_NAME" (actual: 14, maximum: 10)

--Let us create an error log table to hold errors resulting from the INSERT INTO
--  We tell DBMS_ERRLOG package that the error log table will be called TAB_B_ERRLOG and it will hold
--  errors for table TAB_B
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('TAB_B', 'TAB_B_ERRLOG');

--Pay attention to the fact that the first 5 columns are error information columns
--  Also, notice that the rest of the columns were turned into VARCHAR(4000 BYTE) irrespective of their data type!
DESC TAB_B_ERRLOG;

CREATE TABLE TAB_B_ERRLOG
(
  ORA_ERR_NUMBER$  NUMBER,
  ORA_ERR_MESG$    VARCHAR2(2000 BYTE),
  ORA_ERR_ROWID$   UROWID(4000),
  ORA_ERR_OPTYP$   VARCHAR2(2 BYTE),
  ORA_ERR_TAG$     VARCHAR2(2000 BYTE),
  OWNER            VARCHAR2(4000 BYTE),
  OBJECT_NAME      VARCHAR2(4000 BYTE),
  SUBOBJECT_NAME   VARCHAR2(4000 BYTE),
  OBJECT_ID        VARCHAR2(4000 BYTE),
  DATA_OBJECT_ID   VARCHAR2(4000 BYTE),
  OBJECT_TYPE      VARCHAR2(4000 BYTE),
  CREATED          VARCHAR2(4000 BYTE),
  LAST_DDL_TIME    VARCHAR2(4000 BYTE),
  TIMESTAMP        VARCHAR2(4000 BYTE),
  STATUS           VARCHAR2(4000 BYTE),
  TEMPORARY        VARCHAR2(4000 BYTE),
  GENERATED        VARCHAR2(4000 BYTE),
  SECONDARY        VARCHAR2(4000 BYTE),
  NAMESPACE        VARCHAR2(4000 BYTE),
  EDITION_NAME     VARCHAR2(4000 BYTE)
)

--Let us attempt the same insert again but
--    we are going to log the errors into a log table
--  We use the current DATETIME as tag for this instance
--    of the error capture..
--  We could use a different tag to identify errors
--    captured in the past or in the future for this same insert statement
--    but using the timestamp allows us to get a sense of
--    when this error information was captured
--We want to capture only the first 10 errors and
--    then the statement will fail - using REJECT LIMIT clause
INSERT INTO TAB_B
SELECT * FROM TAB_A
LOG ERRORS INTO TAB_B_ERRLOG (TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')) REJECT LIMIT 10;

--Still get
ORA-12899: value too large for column "MY_USER_ID"."TAB_B"."OBJECT_NAME" (actual: 13, maximum: 10)

--Did the insert work for the rows that had no data issues?
SELECT * FROM TAB_B;
--No data

--Let us look at the error log for the actual values that had issues
SELECT * FROM TAB_B_ERRLOG

Output – Detailed Capture of Errors

ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS C_FILE#_BLOCK# 8 8 CLUSTER 28-Jul-04 28-Jul-04 2004-07-28:21:21:10 VALID N N N 5
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS I_FILE#_BLOCK# 9 9 INDEX 28-Jul-04 28-Jul-04 2004-07-28:21:21:10 VALID N N N 4
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS I_USER_MIG1 41 41 INDEX 28-Jul-04 28-Jul-04 2004-07-28:21:21:10 VALID N N N 4
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS BOOTSTRAP$MIG 53 53 TABLE 28-Jul-04 28-Jul-04 2004-07-28:21:21:10 VALID N N N 1
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS TYPED_VIEW$ 60 60 TABLE 28-Jul-04 22-Jan-05 2005-01-22:12:28:05 VALID N N N 1
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS RESOURCE_PLAN$ 71 71 TABLE 28-Jul-04 1-Jun-12 2012-06-01:14:07:03 VALID N N N 1
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS RESOURCE_CONSUMER_GROUP$ 72 72 TABLE 28-Jul-04 1-Jun-12 2012-06-01:14:07:03 VALID N N N 1
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS RESOURCE_PLAN_DIRECTIVE$ 73 73 TABLE 28-Jul-04 1-Jun-12 2012-06-01:14:07:19 VALID N N N 1
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS TRIGGERCOL$ 75 75 TABLE 28-Jul-04 28-Jul-04 2004-07-28:21:21:11 VALID N N N 1
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS TRIGGERJAVAF$ 78 78 TABLE 28-Jul-04 28-Jul-04 2004-07-28:21:21:11 VALID N N N 1
12899 ORA-12899: value too large for column “MY_USER_ID”.”TAB_B”.”OBJECT_NAME” (actual: 14, maximum: 10) I 20130130080027 SYS TRIGGERJAVAS$ 79 79 TABLE 28-Jul-04 28-Jul-04 2004-07-28:21:21:11 VALID N N N 1

**ORA_ERR_ROWID$ is only populated for UPDATEs and DELETEs

Suggestions

It is always a good idea to use a standard naming convention when creating error log tables as they tend to get mixed up with actual table names if everyone decided to use their own names for error log tables.

A good suggestion is an _EL suffix to the table for which the errors are being logged in the format – “[Actual table name]_EL”. This naming convention helps since it is concise especially in cases where the existing table names are already pushing the 30 character limit for object names in Oracle.

Eg: A table named CUST_MAST will have a corresponding error log table named CUST_MAST_EL

Also, a cleanup strategy should be considered if the REJECT LIMIT is too high as the error log can fill up quickly.

By the way, it works equally well with the MERGE statement where the intent is identical.

Alternate solutions:

If you are already doing BULK operations using FORALL, please check the SAVE EXCEPTIONS clause of the FORALL statement.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s