How to Debug ORA-01438 errors with ODI

Hello Everybody,

When you work in large enterprises where source systems sometimes forget to inform the datawarehouse about changes on data format / size or you are aggregating  a continuously enlarging data, Getting an ORA-01438 becomes routine in once in a month.

Within this post i'll try to explain a practical method to make your ODI scenarios debug themselves .

ORA-01438: value larger than specified precision allows for this column

Without ODI:

When dealing with sql statements we can use the benefits of DBMS_ERRLOG package.
[doc. http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_errlog.htm#ARPLS680]

if you have a huge Insert statement which throws an ORA-01438 you can simply create a log table  via

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('<TARGET_TABLE_SCHEMA>.<TARGET_TABLE_NAME>','<LOG_TABLE_NAME>');
/*LOG_TABLE_NAME is usually named with a convention like ERR_<TARGET_TABLE_NAME>*/


then all you have to do is call your  Insert statement by adding "LOG ERRORS INTO ...."
as shown below to log all the erroneous records.

 INSERT INTO <TARGET_TABLE_SCHEMA>.<TARGET_TABLE_NAME> (............)
   SELECT ...........
   FROM <SOURCE_TABLE_SCHEMA>.<SOURCE_TABLE_NAME>
LOG ERRORS INTO <TARGET_TABLE_SCHEMA>.<LOG_TABLE_NAME> ('Label to specify for which run the records are logged') REJECT LIMIT <Number of rejected records>;

Then you can examine the log to specify the problem.

But it is important to remember that running all insert statements with LOG ERRORS  adds an expense at the moment of inserting. This method should be used only for detecting the problem.

With ODI:
So we can not just embed the phrases into a KM and wait for the error happen because  of the performance. But we can integrate this facility into our KM's and use parameter to invoke or revoke error logging on demand.

But how?
Simplest way is adding a declare type of variable to your packages to manage on demand run type. Then pass this variable to KM through an option. When we edit the KM in the proper way then we are done.

Below are this steps in detail:

1. Create the variable:

 

Select The KM to edit. I am editing the basic IKM SQL Control Append. Don not forget duplicating the KM and renaming it to make your remember its function.

I renamed it in order to make it understandable that this KM has Oracle Specific template.

Add an option with Value Type and set the default value as the variable:

Add a new detail to the KM for creating the log tables if necessary  This detail must be added to the first order:

BEGIN
<@ if ("1".equals("<%=odiRef.getOption("DEBUG_MODE")%>") ) { @> 
            DBMS_ERRLOG.CREATE_ERROR_LOG('<%=odiRef.getTable("L","TARG_NAME","A")%>','ERR_<%=odiRef.getTargetTable("RES_NAME")%>');
<@ } @>
      NULL;
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE = -955 THEN
          NULL;
      END IF;
      NULL;

END;

Modify "insert new rows" detail the way the logging statement will be added to the regular insert statement when requested via #DEBUG_MODE=1 

<@ if ("1".equals("<%=odiRef.getOption("DEBUG_MODE")%>") ) { @> 
        LOG ERRORS INTO <%=odiRef.getTargetTable("SCHEMA")%>.ERR_<%=odiRef.getTargetTable("RES_NAME")%>('<%=odiRef.getSession("SESS_NO")%>')  REJECT LIMIT 10

<@ }  @> 

Caution! Once you add the variable and KM to your Packages do not forget to send  #DEBUG_MODE=0 to the scenarios on regular runs!.