LKM File to Oracle SQLLDR ODI

One of the most common problem people are facing while using Oracle Data Integrator's LKM File to Oracle (SQLLDR) is that the step which invokes the OS based sqlldr results in an error even if LOA_ERRORS is set to non-zero number.

For eg. you have a file that contains 1000 records and 10 of them are erroneous (bad), then ODI will create a .bad file and move the records in there. Theoretically, you will expect that setting up the LKM parameter LOA_ERRORS should solve the problem. So, if you want a maximum of 20 records to error out, you will set LOA_ERRORS=20.
But, even if the number of actual errors is less than 20, ODI will indicate failure of that step.

This is a known bug 8560194 with ODI LKM File to Oracle (SQLLDR) and it has not been resolved yet.
Lets get into the details as to why this is a bug. This has to do with the way sqlldr returns the code when it terminates. Following is the list of codes that sqlldr returns for different conditions:

1. SQLLDR if successful returns 0
2. SQLLDR if unsuccessful returns 1
3. SQLLDR if successful but even 1 record erred out to .bad or .dsc file returns 2

For ODI anything that is not 0 is an error. And that will be true for any system that is dependent on the return codes

To resolve this bug, you will have to customize this KM and change the step "Invoke SQLLDR" to selectively handle each error condition.
Follow the steps:

1. Duplicate the KM.
2. Goto the step Call sqlldr. and change the technology to Jython.
3. Replace the existing code with the following:

import os
retVal = os.system(r'sqlldr control=<%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].ctl", "", "")%> log=<%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].log", "", "")%> userid=<%=snpRef.getInfo("DEST_USER_NAME")%>/<%=snpRef.getInfo("DEST_PASS")%>@<%=snpRef.getInfo("DEST_DSERV_NAME")%> > <%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].out", "", "")%>')

if retVal == 1 or retVal > 2:
----->raise 'SQLLDR failed. Please check the <%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].log", "", "")%> for details '

Replace the -----> with spaces or tab. You can enhance the above code by putting variables and further checking for the .bad, .dsc and .log files.