6 Replies Latest reply on Jun 25, 2015 5:01 AM by user1601529

    Call sqlldr gives error to see log files but no log file exists, same sqlldr command execute at command prompt

    user1601529

      Hi Experts,

       

      My system configuration contain

      OS: Win 7 – 64-bit

      ODI : version 11.1.1.7.0 – 64 bit

      Oracle DB: 11G – 64 – bit

           When I execute a simple Interface from File --> Oracle DB using "LKM File to Oracle (SQLLDR)" is gives error, same Interface runs successfully when "LKM File to SQL" is used.

      I am seeing and error is at 6th Step where SQLLDR is been called. Error is:

       

      "org.apache.bsf.BSFException: exception from Jython:

      Traceback (most recent call last):

        File "<string>", line 22, in <module>

      Load Error: See C:\ODI\RAW/SAMPLE.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:577)

        at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)

        at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)

        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:745)

      Caused by: Traceback (most recent call last):

        File "<string>", line 22, in <module>

      Load Error: See C:/ODI/PRODUCTSQLLDR.log for details

       

       

        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._pyx0.f$0(<string>:59)

        at org.python.pycode._pyx0.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"

       

      Below is the command executes at the back in Jython.

       

      "

      import java.lang.String

      import java.lang.Runtime as Runtime

      from jarray import array

      import java.io.File

      import os

      import re

       

      ctlfile = r"""C:\ODI\RAW/SAMPLE.ctl"""

      logfile = r"""C:\ODI\RAW/SAMPLE.log"""

      outfile = r"""C:\ODI\RAW/SAMPLE.out"""

      oracle_sid=''

      if len('xe')>0: oracle_sid = '@'+'xe'

       

      loadcmd = r"""sqlldr 'GM_ODI_WORK_1/<@=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 and " % loadcmd

       

      # Init Vars

      nbIns = 0

      nbRej = 0

      nbNull = 0

      strprt = ""

      maxAllowedError = r"""0"""

      c = 0

      flag = 0

       

      # Open log file

      f = open(logfile, "r")

      try:

        lines = f.readlines()

        for line in lines:

        if line.rstrip().upper().endswith(r"""GM_ODI_WORK_1.TC$_0SAMPLEFILE:""".upper()):

        flag = 1

        c = 0

        if flag == 1:

        if c > 0 and c <= 4:

        if c == 1 :

        nbIns = int(re.findall("\d+", line)[0])

        elif c == 2:

        nbRej = int(re.findall("\d+", line)[0])

        elif c == 4:

        nbNull = int(re.findall("\d+", line)[0])

        break

        c+=1

        strprt = "\n\tIns:\t%s\n\tReject:\t%s\n\tNullField:\t%s" % (nbIns, nbRej, nbNull)

      finally:

        f.close()

       

      # if some rows has been rejected due to invalide data, check KM option LOA_ERRORS

      if rc == 2:

        if nbRej > int(maxAllowedError):

        raise strprt

        break

      "


      When I printed the value of "loadcmd" then I found the below command and it executes successfully in the command prompt

      sqlldr 'GM_ODI_WORK_1/GM_ODI_WORK_1@xe' control='C:\ODI\RAW/SAMPLE.ctl' log='C:\ODI\RAW/SAMPLE.log' > "C:\ODI\RAW/SAMPLE.out"


      Can anyone let me understand the root cause why the above sqlldr command not executes from ODI ?



      Thanks,

      -Vikram Singh