1 2 Previous Next 17 Replies Latest reply: Jan 2, 2013 6:05 AM by Maydemir_Ayd RSS

    Getting error while loading flat file into table.

    pm
      I am using ODI 11 and i am trying to load data in oracle database table using source as flat .txt file .

      When i execute the interface , i am getting below errors.

      ODI-1217: Session INF_FILE2TAB (31022) fails with return code 7000.
      ODI-1226: Step INF_FILE2TAB fails after 1 attempt(s).
      ODI-1240: Flow INF_FILE2TAB fails while performing a Loading operation. This flow loads target table PARTNER.
      Caused By: org.apache.bsf.BSFException: exception from Jython:
      Traceback (most recent call last):
      File "<string>", line 44, in <module>


           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)

           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:889)

           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)

           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:204)

           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:540)

           at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)

           at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1074)

           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1466)

           at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2224)

           at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2168)

           at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:333)

           at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

           at java.lang.reflect.Method.invoke(Method.java:597)


      java.sql.SQLException: java.sql.SQLException: ORA-06564: object DAT_DIR does not exist


           at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
           at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:346)
           at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2458)
           at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:48)
           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:2906)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
           at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:540)
           at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
           at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
           at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
           at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
           at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
           at java.lang.Thread.run(Thread.java:662)

      Please let me know pointers on this.
        • 1. Re: Getting error while loading flat file into table.
          pm
          Anything i missing over here.

          Do i need to give previlege to user/directory?

          and

          In File technology which user and password i need to give?
          • 2. Re: Getting error while loading flat file into table.
            Maydemir_Ayd
            Hi pm,

            Can you test connection of file server on topology which you defined. The source file " must be accessible " by the "ODI agent" executing the interface.

            Edited by: Maydemir_Ayd on 28.Ara.2012 06:11
            • 3. Re: Getting error while loading flat file into table.
              pm
              Yes,after your sugestion i have created ODI agent and tested the file system connection in topology. It was connected successfully.

              but when i tried again for file to table load. Again got below error.

              org.apache.bsf.BSFException: exception from Jython:
              Traceback (most recent call last):
              File "<string>", line 44, in <module>


                   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)

                   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

                   at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:889)

                   at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)

                   at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:204)

                   at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:540)

                   at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)

                   at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1074)

                   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1466)

                   at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2224)

                   at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2168)

                   at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:333)

                   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

                   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

                   at java.lang.reflect.Method.invoke(Method.java:597)


              java.sql.SQLException: java.sql.SQLException: ORA-06564: object DAT_DIR does not exist


                   at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
                   at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:346)
                   at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2458)
                   at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:48)
                   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:2906)
                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
                   at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:540)
                   at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
                   at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
                   at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
                   at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
                   at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
                   at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
                   at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
                   at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
                   at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
                   at java.lang.Thread.run(Thread.java:662)

              Please suggest.
              • 4. Re: Getting error while loading flat file into table.
                Maydemir_Ayd
                Hi

                Which Knowledge Module you are using ? I think LKM file to oracle (sqlldr)..

                Ok now What is DAT_DIR ? Is it your flat file name ? If you create a data store at model for flat file, I am not sure but maybe you gave file resource wrong..

                If DAT_DIR is your file name, maybe you must give DAT_DIR.txt at model resource_name..

                Pls give me more information What did you do before loading.

                Thanks.
                • 5. Re: Getting error while loading flat file into table.
                  pm
                  Hello Maydemir,
                  I followed below steps

                  1) I have one text file, which i copied at c:\po\input path. Then i have created one table in my oracle database schema refering same structure
                  which is in file.

                  2) I have created two topologies one is for flat file and other is for database target table.
                       1) FILE: in FIle->File_Generic-> given name and properties. Then in Directory(Schema) and Directory(Work Schema) given c:/po/input path and added logical schema in context tab. Afterward added logical schema in Logical architecture.

                       2) Target Table -- Oracle ->schema details
                       
                  3) In Designer section ,created Model for File (source) and oracle(target) technology.
                  For File, created datastore and selected file name in Resource Name file.Similarly created model for target table and opted selective reverse engineering for getting metadata.

                  4) Then created project and in that imported LKM File to Oracle(EXTERNAL TABLE) and IKM Oracle Incremental Update KM's. Then created interface,in that drag file from model to source datastore and table to target datastore.
                       while doing this got automatic mapping. I have opted for that.

                  5) In Flow tab, in property inspector tab LKM Selector selected as "LKM File to Oracle(EXTERNAL TABLE)" for source and selected IKM Oracle Incremental Update for target.

                  At last i executed this interface and got the error message.
                  • 6. Re: Getting error while loading flat file into table.
                    Sutirtha Roy
                    ORA-06564: object DAT_DIR does not exist
                    means the directory oject does not exists or not accessible.

                    Can you try with LKM File to Oracle ?
                    • 7. Re: Getting error while loading flat file into table.
                      pm
                      Yes as per your suggestions .. created new project and imported LKM (sqlldr) and tried.

                      Got below error

                      ODI-1217: Session INFFILE2TAB1 (48022) fails with return code 7000.
                      ODI-1226: Step INFFILE2TAB1 fails after 1 attempt(s).
                      ODI-1240: Flow INFFILE2TAB1 fails while performing a Loading operation. This flow loads target table PARTNER.
                      Caused By: org.apache.bsf.BSFException: exception from Jython:
                      Traceback (most recent call last):
                      File "<string>", line 33, in <module>
                      Load Error: See c:/PO/INPUT/PartnersFile.log for details

                           at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
                           at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:346)
                           at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
                           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2458)
                           at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:48)
                           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:2906)
                           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
                           at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:540)
                           at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
                           at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
                           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
                           at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
                           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
                           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
                           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
                           at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
                           at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
                           at java.lang.Thread.run(Thread.java:662)

                      when i checked log file .. it contains


                      SQL*Loader: Release 11.2.0.1.0 - Production on Tue Jan 1 14:21:37 2013

                      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                      SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
                      ORA-12560: TNS:protocol adapter error
                      • 8. Re: Getting error while loading flat file into table.
                        Sutirtha Roy
                        pm wrote:
                        Yes as per your suggestions .. created new project and imported LKM (sqlldr) and tried.

                        Got below error

                        SQL*Loader: Release 11.2.0.1.0 - Production on Tue Jan 1 14:21:37 2013

                        Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                        SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
                        ORA-12560: TNS:protocol adapter error
                        May be your database is not up ?
                        http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:431775600346873836

                        Any way I asked you to use LKM File to Oracle
                        It does not involve Sql Loader or External table.
                        • 9. Re: Getting error while loading flat file into table.
                          pm
                          Hi Sutirtha,

                          I am trying to import LKM File to Oracle, but only LKM File to Oracle knowledge module is not available in list.
                          • 10. Re: Getting error while loading flat file into table.
                            Sutirtha Roy
                            pm wrote:
                            Hi Sutirtha,

                            I am trying to import LKM File to Oracle, but only LKM File to Oracle knowledge module is not available in list.
                            Sorry .. my bad , actually I meant to say LKM File to SQL
                            Can you find that and try to use the same ?
                            • 11. Re: Getting error while loading flat file into table.
                              Maydemir_Ayd
                              Hello pm,

                              I see your steps , they're true.
                              If the sql loader logs has "ORA-12560: TNS:protocol adapter error" error message , You sure your db up ?
                              I think you use windows OS , can you look at oracle services up ? (start-->run-->services.msc)

                              And if there is no problem on oracle service , can you check to acces your target table from ODI? You can check like this ; at your table model --> right click then click data... Or you can check data server on topology...

                              Thanks

                              Edited by: Maydemir_Ayd on 01.Oca.2013 23:53

                              Edited by: Maydemir_Ayd on 02.Oca.2013 00:11
                              • 12. Re: Getting error while loading flat file into table.
                                pm
                                i tried using LKM File to SQL, but no luck.

                                Could you please check steps mentioned in thread and tell me , if i am doing wrong or missing anything.
                                • 13. Re: Getting error while loading flat file into table.
                                  Sutirtha Roy
                                  Steps looks fine to me.
                                  Can you share what is the exact error you are getting and what was the code generated by ODI for that step ?
                                  • 14. Re: Getting error while loading flat file into table.
                                    pm
                                    Hi Maydemir,

                                    As you mentioned , i have checked database connection using tnsping <machinename> and lsnrctl status on server. Everthing looks fine.Also i checked table in model , and i can see the structure, as there data is not loaded yet.

                                    So after that i am checking path of file .. i think there seems to be problem, i have given path like c:\PO\INPUT in directory schema of file technology.

                                    When i execute it , it stops at "Call sqlldr" step.

                                    Here is the generated CTL file.

                                    OPTIONS (
                                         SKIP=0,
                                         ERRORS=0,
                                         DIRECT=FALSE
                                         )
                                    LOAD DATA
                                    INFILE "c:\PO\INPUT/partners1.txt" "STR x'0D0A'"
                                    BADFILE "c:\PO\INPUT/PartnersFile.bad"
                                    DISCARDFILE "c:\PO\INPUT/PartnersFile.dsc"
                                    DISCARDMAX 1
                                    INTO TABLE SCOTT.TC$_0PARTNER

                                    (
                                         PARTNER_ID     POSITION(1:5)     DECIMAL EXTERNAL,
                                         COMPANY_NAME     POSITION(6:20)     ,
                                         CITY     POSITION(21:36)     ,
                                         COUNTRY_ID     POSITION(37:37)     DECIMAL EXTERNAL,
                                         DATE_ENLISTED     POSITION(38:38)     DATE "DD-MON-YYYY"
                                    )

                                    If you look at the path .. it is generated wrongly.if i changed this path like
                                    c:\PO\INPUT\partners1.txt and executed sql loader script from cmd prompt then it works fine.

                                    sqlldr SCOTT/tiger@10.44.235.63:1521/orcl control='C:\PO\INPUT\PartnersFile.ctl' log='logfile.log'.

                                    Can you please tell me where i have to make chnages to generate path properly.

                                    - PM
                                    1 2 Previous Next