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:
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:
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:
- SQLLDR if successful returns 0
- SQLLDR if unsuccessful returns 1
- SQLLDR if successful but even 1 record erred out to .bad or .dsc file returns 2
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:
11 comments:
Hi,
This post is very impressive and is very helpful. I am working on ODI version 10.1.3.5.6 and tried modifying LKM file to sqlldr with the code mentioned in the post.
'Call sqlldr' step fails with "AttributeError: class 'org.python.modules.os' has no attribute 'system'" error. Could you please suggest why ODI is not able to find system in the os package?
Thanks!
Akshata,
You would need to provide more details here.
Are you trying to execute the Jython code using command line Jython interpreter ?
If you are modifying the existing KM, can you paste the changes that you are trying to make ?
Does the existing LKM run fine ?
Hi Ankit,
I am not running the code through command line. I modified the existing LKM file to sqlldr and replaced the existing code on 'Call sqlldr' step 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 '
The existing LKM (with no modifications run fine).
Akshata,
What is the Java version and Jython versions are you using ?
Try to execute a similar command on your OS using jython command line and then check.
eg: if you are on windows :
cd $ODI_HOME\oracledi\bin
jython.bat
import os
os.system("dir")
And see if it gives an error ?
I am using Jython 2.1 on java 1.6.0_10
I ran the command in unix and it is throwing 2 different errors as follows:
>>> import os
>>> os.system("dir")
Traceback (innermost last):
File "", line 1, in ?
ImportError: no module named javaos
>>> import os
>>> os.system('dir')
Traceback (innermost last):
File "", line 1, in ?
AttributeError: class 'org.python.modules.os' has no attribute 'system'
I think its a Java version compatibility issue.
Can you try using Java 1.5.
Set ODI_JAVA_HOME to 1.5
and then run the commands again.
I am not sure if "dir" has been set as alias in your Unix. So, alternatively you can try with "ls"
Hi Ankit,
I just tried this on my personal machine and it is working.
I need to test it at my work. Thank you for this post. Very helpful.
Regards,
Kranthi
Hi Ankit,
Is this bug has been resolved in ODI 11g?
Regards,
Ambuj Kumar
Yes. It has been fixed in the ODI 11g version.
I have a similar error. I am getting:
org.apache.bsf.BSFException: exception from Jython:
Traceback (innermost last):
File "", line 3, in ?
OS command has signalled errors
and the description tab has :
import os
if os.system(r"sqlldr control=c:\MercuryGate\Import/XLOADS.ctl log=c:\MercuryGate\Import/XLOADS.log userid=MERCURYGATEDR/<@=snpRef.getInfo("DEST_PASS") @>@ > c:\MercuryGate\Import/XLOADS.out") <> 0 :
raise "OS command has signalled errors"
i can go to the command prompt (I'm using windows).
and suggestions?
<@=snpRef.getInfo("DEST_PASS") @>@
Please look at the last @.
It should have a SID after it.
Perhaps you need to mention that SID as a Data Server in the Topology Manager
Post a Comment