7 Replies Latest reply: Aug 19, 2013 9:53 AM by ShrikantB RSS

    ODI error when trying to import Excel

    873330
      Hello,

      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

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

          Please follow the steps given in the weblink below:
          http://blogs.oracle.com/dataintegration/entry/using_microsoft_excel_as_a_sou

          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

          Regards,
          KK
          • 2. Re: ODI error when trying to import Excel
            873330
            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 11.1.1.05 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.

            Regards
            Igor
            • 3. Re: ODI error when trying to import Excel
              851628
              Igor,
              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

              Thanks
              • 4. Re: ODI error when trying to import Excel
                873330
                hi,

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

                It is bug in odbc-jdbc bridge - bug 4495457

                http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4495457

                Now i have to figure out how to resolve it.

                Regards
                Igor
                • 5. Re: ODI error when trying to import Excel
                  Geet
                  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
                    ShrikantB

                    Hi,

                    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
                      ShrikantB

                      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 {%>

                        (1=1)

                        <% } %>

                      <%=odiRef.getJoin(i)%>

                      <%=odiRef.getFilter(i)%>

                      <%=odiRef.getJrnFilter(i)%>

                      <%=odiRef.getGrpBy(i)%>

                      <%=odiRef.getHaving(i)%>

                      <%}%>

                       

                      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....