6 Replies Latest reply on Nov 13, 2012 8:43 PM by 651059

    Load Flat file to Oracle

      Hi Experts,

      I have a flatfile(100,000 records) that I am loading into Oracle table using LKM File to SQL and IKM SQL Incremental Update. I made truncate target table to true during the load to makesure the table is empty and does not update. so here is the problem. whenever i load the flatfile into the table, the order of records in the table does not match the order of the file records in the flatfile. How do I fix the order?

      Thanks in advance.
        • 1. Re: Load Flat file to Oracle
          In oracle it does default sorting while its fetching, before inserting into oracle.
          How the data has been sorted (on which columns) in the flat file?

          Edited by: user_sam on Nov 6, 2012 9:24 AM
          • 2. Re: Load Flat file to Oracle
            A. Drieux
            if you can create an extra column in your target table, create a "row_no" column.
            Then mapp it with the oracle analytic function "ROW_NUMBER() OVER ()" and use the order by clause in it.

            ex : row_number() over (order by your_source_column).

            You don't care about the content of this column but the data will be ordered by the source column you want.
            • 3. Re: Load Flat file to Oracle
              I don't have a sort column, thiis file is a combination of multiple files.

              Thanks for you quick response.
              • 4. Re: Load Flat file to Oracle
                Without knowing the sorting column in flat file, I am not sure how you could do this.
                Just in case if you have found the columns, then I think you need to modify the IKM for that. For and example, if you have chosen "IKM SQL control Append" , you need to modify command on target in the step "Insert flow into I$ table" or final target step.
                • 5. Re: Load Flat file to Oracle
                  A. Drieux
                  Did you try another LKM, like the "LKM file to oracle SQL LOADER" ?

                  Maybe the order won't be affected with sql loader.
                  • 6. Re: Load Flat file to Oracle
                    Hi Drieux,

                    I just thought of trying "LKM file to oralce (SQL Loader)" and ended with the same error as in the the thread https://forums.oracle.com/forums/message.jspa?messageID=10688471#10688471
                    Do you have any idea?

                    Error is org.apache.bsf.BSFException: exception from Jython:
                    Traceback (most recent call last):
                    File "<string>", line 33, in <module>
                    Load Error: See D:\projects\UAC_Integration\Data/PREF.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:2457)
                         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:2906)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
                         at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:537)
                         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:82)
                         at java.lang.Thread.run(Thread.java:662)

                    log file says ..

                    SQL*Loader: Release - Production on Tue Nov 13 15:42:52 2012

                    Copyright (c) 1982, 2005, Oracle. All rights reserved.

                    SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
                    ORA-12560: TNS:protocol adapter error

                    I have hard coded the step "call sqlldr" in LKM and and tested and got the same error. Also I have have created tns entry in the local tns file to match it.