1 Reply Latest reply on Mar 23, 2013 12:06 AM by Ayush Ganeriwal-Oracle

    Can't execute SQLLDR through LKM File to Oracle (SQLLDR) in AIX

    salzo
      Hi.

      I have a new ODI 11.1.1.6 installation with a Java EE (WebLogic) agent deployed in AIX 7.1 using IBM Java 7. I'm importing some projects from a ODI 11.1.1.5 installation. One of them use the "LKM File to Oracle (SQLLDR)" KM with the default options. When we run one of the interfaces that use that KM, we get the following error in the "Call sqlldr" step.

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------
      org.apache.bsf.BSFException: exception from Jython:
      Traceback (most recent call last):
      File "<string>", line 34, in <module>
      Load Error: See /flatfiles/logs/ACME.log for details

      at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
      at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
      at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
      at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
      at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
      at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
      at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
      at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
      at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
      at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
      at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
      at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
      at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
      at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
      at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
      at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
      at java.lang.Thread.run(Thread.java:777)
      Caused by: Traceback (most recent call last):
      File "<string>", line 34, in <module>
      Load Error: See /flatfiles/logs/MOV_DIARIO.log for details

      at org.python.core.PyException.fillInStackTrace(PyException.java:70)
      at java.lang.Throwable.<init>(Throwable.java:68)
      at org.python.core.PyException.<init>(PyException.java:46)
      at org.python.core.PyException.doRaise(PyException.java:219)
      at org.python.core.Py.makeException(Py.java:1166)
      at org.python.core.Py.makeException(Py.java:1170)
      at org.python.pycode._pyx9.f$0(<string>:37)
      at org.python.pycode._pyx9.call_function(<string>)
      at org.python.core.PyTableCode.call(PyTableCode.java:165)
      at org.python.core.PyCode.call(PyCode.java:18)
      at org.python.core.Py.runCode(Py.java:1204)
      at org.python.core.Py.exec(Py.java:1248)
      at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
      at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
      ... 19 more
      ----------------------------------------------------------------------------------------------------------------------------------------------------------------

      The /flatfiles/logs/ACME.log log refered in the stack trace is created but is empty (I don't see also any related messages in the Java EE managed server logs or oracledi log).

      Doing a "while [ true ]; do ps -fea | grep sqlldr; grep -v grep" I managed to get the sqlldr S.O order executed by ODI; is the following:

      /bin/sh -c sqlldr USERX/PASSX@DBX control="/flatfiles/logs/ACME.ctl" log="/flatfiles/logs/ACME.log" > "/flatfiles/logs/ACME.out"

      If I execute that order manually, I can see that the sqlldr program is executed, but doesn't receive any parameters. The /flatfiles/logs/ACME.out gets the same content it'll get if I'd execute the sqlldr without any params.

      The generated code is as follows:

      -------------------------------------------------------------------------------------------------------
      import java.lang.String
      import java.lang.Runtime as Runtime
      from jarray import array
      import java.io.File
      import os
      import re
      import javaos



      def reportnbrows():     

      f = open(r"/flatfiles/logs/ACME.log", 'r')
      try:
      for line in f.readlines():
      if line.find("MAXIMUM ERROR COUNT EXCEEDED")>=0 :
      raise line
      finally:
      f.close()



      ctlfile = r"""/flatfiles/logs/ACME.ctl"""
      logfile = r"""/flatfiles/logs/ACME.log"""
      outfile = r"""/flatfiles/logs/ACME.out"""
      oracle_sid=''
      if len('DBLSTAGE')>0: oracle_sid = '@'+'DBLSTAGE'

      loadcmd = r"""sqlldr DBUSR/<@=snpRef.getInfo("DEST_PASS") @>%s control="%s" log="%s" > "%s" """ % (oracle_sid,ctlfile, logfile, outfile)

      rc = os.system(loadcmd)

      if rc <> 0 and rc <> 2:
      raise "Load Error", "See %s for details" % logfile

      if rc==2:
      reportnbrows()
      -------------------------------------------------------------------------------------------------------

      Any idea will be much appreciated.

      Thank you very much.