6 Replies Latest reply: Jul 31, 2013 12:47 AM by Myvizhi Selvi RSS

    Error while exporting data into Excel Sheet

    Myvizhi Selvi
      Hi All,

      I have created a VO which is based on Query(Not based on EO) and the Query is as follows:

      select f.user_name ,
      f.description ,
      a.currency_code,
      a.amount_to ,
      a.amount_from
      from seacds.ar_approval_user_limits_nv a , seacds.fnd_user_nv f
      where f.user_id = a.user_id
      and a.document_type = 'CM'
      order by 2;


      Based on this VO I have created a search page which will search and returns data from the table and finally standard export button will export the data into excel sheet.
      In this am searching the data based on above 5 attributes. Without entering anything in the messageTextInput if i am clicking GO button, it is returning all the data into the table region. After this if i click on Export Button, data are getting exported into Excel Sheet. It is fine.

      But if i am searching the data by entering any value in any of the messageTextInput, it is returning data. But if i am clicking the Export Button then it is throwing the following error:

      Exception Details.
      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select f.user_name ,
      f.description ,
      a.currency_code,
      a.amount_to ,
      a.amount_from
      from seacds.ar_approval_user_limits_nv a , seacds.fnd_user_nv f
      where f.user_id = a.user_id
      and a.document_type = 'CM'
      order by 2) QRSLT WHERE (( UPPER(CURRENCY_CODE) like UPPER(:1) AND (CURRENCY_CODE like :2 OR CURRENCY_CODE like :3 OR CURRENCY_CODE like :4 OR CURRENCY_CODE like :5))) ORDER BY DESCRIPTION asc
           at oracle.apps.fnd.framework.OAException.wrapperException(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
           at OA.jspService(_OA.java:71)
           at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
           at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
           at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
           at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
           at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
           at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
           at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
           at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
           at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
           at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
           at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
           at java.lang.Thread.run(Thread.java:595)
      ## Detail 0 ##
      java.sql.SQLException: Invalid column type
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:240)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7895)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7572)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8183)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectAtName(OraclePreparedStatement.java:8206)

      Kindly give me any idea to clear this error.

      Thanks and Regards,
      Myvizhi
        • 1. Re: Error while exporting data into Excel Sheet
          keerthioaf
          Hi Myvizhi ,


          Did you try running the query from back end that got generated in the error trace ? see if that query is returning desired
          output .

          Also would like to know if you are using oracle standard search mode i.e result based search / auto customization search ?

          --Keerthi                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
          • 2. Re: Error while exporting data into Excel Sheet
            Myvizhi Selvi
            Hi Keerthi,

            I have given Oracle Standard auto customization criteria mode.

            Also that error trace query is not returning any data. It is throwing error.



            Thanks and Regards,
            Myvizhi
            • 3. Re: Error while exporting data into Excel Sheet
              Myvizhi Selvi
              Hi All,

              Could anyone help me in this...

              Thanks and Regards,
              Myvizhi
              • 4. Re: Error while exporting data into Excel Sheet
                Myvizhi Selvi
                Hi All,

                I resolved the issue.. I set Select All Rows property as false. So now i am able to export the data.



                Thanks and Regards,
                Myvizhi
                • 5. Re: Error while exporting data into Excel Sheet
                  r12appsuser

                  Myvizhi,

                   

                  where to set this Select All Rows property? I am running into similar issue after extending viewObject

                   

                  thanks,

                  Arun

                  • 6. Re: Error while exporting data into Excel Sheet
                    Myvizhi Selvi

                    Hi Arun,

                     

                    You have to set this Select All Rows property to false in the ExportButton property. Set it to false and check it.

                     

                    Thanks and Regards,

                    Myvizhi