Search code examples
oracle-databasejythonwindows-shellsql-loaderoracle-data-integrator

ODI - Call SQLLDR via Jython on Windows Shell

I'm testing some interfaces with Oracle Data Integrator 11g on Windows 7. All the interfaces use the LKM MSSQL to Oracle (BCP/SQLLDR), while running them I got an error on the "Call SQLLDR via Jython" command. After some invesetigation I found that the root of the problem was the following line of code:

exitCode =  os.system(sqlldr + " control=" + tempSessionFilePrefix + ".ctl log=" + tempSessionFilePrefix + ".log " + "userid=" + "<% out.print(odiRef.getInfo("DEST_USER_NAME")); %>" + "/" + "<% out.print(odiRef.getInfo("DEST_PASS")); %>" + tnsnameOption + " > " + tempSessionFilePrefix +".out" );

It should run on the Windows Shell a string in the form of:

sqlldr control=control_file.ctl log=log_file.log userid=ODI_STAGE/ODI_STAGE > shell_output.out

I did run the string generated directly on the command prompt and it worked without any problem.

So after playing a bit with the code, I couldn't make the os.system working so I replaced it with subprocess.call. I also have to remove the last part of the string where it attempts to save the ouput of the command prompt (> shell_output.out) to make the whole thing work:

exitCode =  subprocess.call([sqlldr,  "control=" + tempSessionFilePrefix + ".ctl",  "log=" + tempSessionFilePrefix + ".log", "userid=" + "<% out.print(odiRef.getInfo("DEST_USER_NAME")); %>" + "/" + "<% out.print(odiRef.getInfo("DEST_PASS")); %>" + tnsnameOption], shell=True);

This one works smoothly.

Regarding the shell output, I suspect that the problem is the string part that starts with the '>' charcater that is parsed as part of the arguments of SQLLDR instead of a command to the prompt. Now, while I can live without it, I would like to ask if someone knows any simple workaround to get also the shell output.

Solution

  • Ok I was finally able to get also the shell output. I edited the "Call SQLLDR via Jython" command with the following:

    from __future__ import with_statement
import subprocess

...

with open(tempSessionFilePrefix +  ".out", "w") as fout:
 exitCode =  subprocess.call([sqlldr,  "control=" + tempSessionFilePrefix + ".ctl",  "log=" + tempSessionFilePrefix + ".log", "userid=" + "ODI_STAGE" + "/" + "<@=snpRef.getInfo("DEST_PASS") @>" + tnsnameOption], stdout=fout, shell=True);

    Now everything work as intended.