7 Replies Latest reply on Aug 19, 2013 2:53 PM by ShrikantB

    ODI error when trying to import Excel


      I would really appreciate if somebody could help me with my problem. anyway to get to the point

      this is error that i get when i execute interface:

      java.sql.SQLException: java.sql.SQLException: Invalid Fetch Size

           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)

      I created data server to odbc driver with value for fetched rows equal to 1;

      in interface i use SUNOPSIS_MEMORY_ENGINE for staging area

      in flow set0 which is excel data - lkm is set to LKM SQL TO SQL (row by row)
      (well what ever i try , I get the same error)

      and from staging area it is IKM SQL control append

      target is oracle table

        • 1. Re: ODI error when trying to import Excel
          Hi Igor,

          Please follow the steps given in the weblink below:

          I am really not sure if you need to put SUNOPSIS_MEMORY_ENGINE as your staging area. In your case, since target is your relation DB which is oracle in your case, you can just use the staging is same as target. It should work for you.
          However, make sure you have done all the steps give in the website

          • 2. Re: ODI error when trying to import Excel
            Thank you for your answer, but unfortunately i already tried that, i get the same error(thats why I tried to load it into memory first). I also read oracle documentation concerning excel import.
            I followed that tutorial word for word , but i get the same eror.

            I also forgot to mention i am using version of ODI. It is so frustrating.
            I also tried on different machine to make shore that it is not problem with something on my computer.

            I can see the data when I right click on model and chose "view data".

            In status ,

            it goes trough first two stages

            it drops the temp table,
            then creates temp table
            and when it comes to load data it chokes.

            when i click on it, and chose code and execute sql statement i get the data back.

            • 3. Re: ODI error when trying to import Excel
              Could you get your issue resolved ? I am facing the sae rror , only thing is my source is MS Access ..

              Appreciate any help to resolve the issue

              • 4. Re: ODI error when trying to import Excel

                I just found couse of the error , like 20 min ago.

                It is bug in odbc-jdbc bridge - bug 4495457


                Now i have to figure out how to resolve it.

                • 5. Re: ODI error when trying to import Excel
                  I modified the fetcharray size and batchupload size to 0 on the DataServer page. It worked for me.
                  • 6. Re: ODI error when trying to import Excel


                    I am also facing same problem when integrating MS Access with Oracle 11g table


                    ODI-1227: Task SrcSet0 (Loading) fails on the source MICROSOFT_ACCESS connection Access_Oracle_Phy.

                    Caused By: java.sql.SQLException: Invalid Fetch Size


                    As you mentioned above you have modified the fetcharray size how do u done that...Please let me know.....

                    • 7. Re: ODI error when trying to import Excel

                      Hi All,


                      I have solved the problem.

                      1. Modified the existing IKM SQL to File append -> Command Insert New Rows -> Command on Source as follows

                      Please check I have included keyword "as" (BOLD)


                      <%for (int i=0; i < odiRef.getDataSetCount(); i++){%>

                      <%=odiRef.getDataSet(i, "Operator")%>

                      select <%=odiRef.getPop("DISTINCT_ROWS")%>

                        <%=odiRef.getColList(i,"", "[EXPRESSION]\t[ALIAS_SEP] as  [CX_COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>

                      from <%=odiRef.getFrom(i)%>

                      where <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %>

                        JRN_FLAG <> 'D'

                        <%} else {%>


                        <% } %>








                      2. In Topology Architecture -> Physical Architechture -> MS Access -> Open server which you created and make Fetch array size to 0 (zero).


                      And here we go......It's done....