3 Replies Latest reply on Sep 21, 2015 3:57 PM by RaviPorwal

    sqlldr km issue

    3021785

      Hello Guys,

       

      I'm using ODI 11.1.1.9 version.

      I'm trying to load the target relational table from a file using LKM File to Sql(Sqlldr).

      My DB server is on remote location, hence I have DB client installed on my local. I'm able to execute sqlldr command through cmd prompt successfully and load table.

      But when invoked through odi, LKM is failing at step 6. Call sqlldr.

       

      Below is the code generated for the same step:

       

      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:\Users\vjh5kor\Project\ODI\Files/Store_Details.ctl"""

      logfile = r"""C:\Users\vjh5kor\Project\ODI\Files/Store_Details.log"""

      outfile = r"""C:\Users\vjh5kor\Project\ODI\Files/Store_Details.out"""

      oracle_sid=''

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

       

       

       

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

       

      # 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"""SISIR.TC$_0TEMP_STORE:""".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

       

      Below is the error message:

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

      Traceback (most recent call last):

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

      Load Error: See C:\Users\vjh5kor\Project\ODI\Files/Store_Details.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:2473)

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

          at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2637)

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

          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:380)

          at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:312)

          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:304)

          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:874)

          at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:133)

          at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:84)

          at java.lang.Thread.run(Thread.java:745)

      Caused by: Traceback (most recent call last):

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

      Load Error: See C:\Users\vjh5kor\Project\ODI\Files/Store_Details.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._pyx23.f$0(<string>:60)

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

       

       

      Can somebody please help on the same.