Thursday, January 21, 2016

RDBMS - sqlldr errors caused by CR and LF characters

Recently faced an issue in an EBS customer. It was related with sql loader program that was executing from EBS concurrent processing tier.
The sqlldr was trying to import the datafiles which were transffered from Windows to Linux, but it was failing because the data that it was trying to import, was not able to fit the columns residing in the relevant tables of the EBS database.
Actually, the solution was pretty easy for this. All that needs to be done was using an ascii mode transfer for copying these datafiles from Windows Linux. On the other hand, it was not possible in this customer site. (dont ask why :))
So we had to deal with it using a different solution.

What was the case?
EBS was executing sqlldr, so we can not manipulate it.
Datafiles transferred from Windows to Linux were in Windows format, having CR and LF characters.
Sqlloader was encountring errors because of the extra characters.

What I have did?
I have seen that, sqlldr in EBS is executed from $ORACLE_HOME (10.1.2 Oracle Home)
So I moved the sqlldr as sqlldr_original
cd $ORACLE_HOME/bin
mv sqlldr sqlldr.original

Then, recreated the sqlldr as a script that uses dos2unix to convert the datafiles to unix format and then executes the sqlldr with all the arguments supplied to it.

[applmgr@ermanserver bin]$ vi sqlldr
dos2unix /sqlldr_datafiles_location/*.txt
sqlldr_original $*

:) well, yes it is not supported and it is in danger to be overwritten by relinking the Oracle Home  :), but still it is unique and saves the day:)
Long time ago, we once have done it for ar60run executable as well. We have used this method(this wrapper method) to set the NLS_SORT parameter before executing the ar60run executable in EBS 11i production environment.

No comments :

Post a Comment