9 Replies Latest reply on Sep 14, 2010 3:11 PM by 747119

    Need help in resolving the below error - SQL statement to execute cannot be

    747119
      Here is my CO as below , which creates a callable statement.

      try {
      OAApplicationModule oaapplicationmodule = pageContext.getApplicationModule(webBean);
      OADBTransactionImpl t = (OADBTransactionImpl)oaapplicationmodule.getOADBTransaction();
      OracleCallableStatement proc = (OracleCallableStatement)t.createCallableStatement(lquery, -1);
      proc.execute();
      t.commit();


      }
      catch(SQLException sqlexception)
      {
      throw OAException.wrapperException(sqlexception);
      }




      After running the page , getting the below error ... ( Please find the below error stack )

      I reffered the developement guide but did not get helpful things.

      Please provide me any clues on the same.

      Regards Raghu

      -- Error Stack ---------------------------------------------------------

      Error Page


      Exception Details.

      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27123: SQL error during call statement preparation. Statement: null
           at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:888)
           at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
           at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1408)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2637)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1659)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:497)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:418)
           at OA.jspService(OA.jsp:40)
           at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
           at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
           at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
           at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
           at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
           at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
           at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
           at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
           at java.lang.Thread.run(Thread.java:534)
      ## Detail 0 ##
      java.sql.SQLException: SQL statement to execute cannot be empty or null
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
           at oracle.jdbc.driver.OracleConnection.privatePrepareCall(OracleConnection.java:1138)
           at oracle.jdbc.driver.OracleConnection.prepareCall(OracleConnection.java:1054)
           at oracle.jbo.server.DBTransactionImpl.createCallableStatement(DBTransactionImpl.java:3033)
           at cisco.oracle.apps.xxchr.element.server.webui.XXCHRElementSetSearchCO.processFormRequest(XXCHRElementSetSearchCO.java:343)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:799)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1118)
           at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:995)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:961)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:816)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:995)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:961)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:816)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2633)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1659)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:497)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:418)
           at OA.jspService(OA.jsp:40)
           at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
           at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
           at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
           at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
           at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
           at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
           at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
           at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
           at java.lang.Thread.run(Thread.java:534)
      java.sql.SQLException: SQL statement to execute cannot be empty or null
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
           at oracle.jdbc.driver.OracleConnection.privatePrepareCall(OracleConnection.java:1138)
           at oracle.jdbc.driver.OracleConnection.prepareCall(OracleConnection.java:1054)
           at oracle.jbo.server.DBTransactionImpl.createCallableStatement(DBTransactionImpl.java:3033)
           at cisco.oracle.apps.xxchr.element.server.webui.XXCHRElementSetSearchCO.processFormRequest(XXCHRElementSetSearchCO.java:343)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:799)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1118)
           at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:995)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:961)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:816)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:995)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:961)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:816)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2633)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1659)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:497)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:418)
           at OA.jspService(OA.jsp:40)
           at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
           at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
           at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
           at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
           at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
           at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
           at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
           at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
           at java.lang.Thread.run(Thread.java:534)

      Edited by: Raghu on Sep 14, 2010 2:58 AM
        • 1. Re: Need help in resolving the below error - SQL statement to execute cannot be
          AnilSharma
          Use this code snippet and change your code accordingly
          String insertStmt = "BEGIN INSERT_RECORD( " +
          "PARAM1 => :1, " +
          "PARAM2 => :2, " +
          "PARAM3 => :3, " +
          "PARAM4 => :4); END;";
          
          DBTransaction trxn = getDBTransaction();
          
          CallableStatement insertStmt = trxn.createCallableStatement( insertStmt , 1 );
          // Rebind parameters
          insertStmt.setString(1, getFirstName());
          insertStmt.setString(2, getLastName());
          insertStmt.setString(3, getAddress1());
          insertStmt.setString(4, getAddress2());
          Thanks
          --Anil
          http://oracleanil.blogspot.com/
          • 2. Re: Need help in resolving the below error - SQL statement to execute cannot be
            747119
            Hi Anil ,

            I am, not calling any procedure in my code. Instead , I am trying to execute a query as below ( please find the query in the varibale "lquery" )

            I have prepared a query as below which is to be called when I click on the search button on my page.

            After clicking on the search I am getting the above error stack.

            Raghu


            -- CO Code

            public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
            {

            super.processFormRequest(pageContext, webBean);
            OAApplicationModule am =
            (OAApplicationModule)pageContext.getApplicationModule(webBean);

            String lquery = null;



            if (pageContext.getParameter("ES_SearchBT")!= null )
            {
            lquery = lquery + "select * from XXCHR_ELEMENT_SET_TABLE";
            // System.out.println (" Query after radio Button 1 " + lquery);
            }


            try {
            OAApplicationModule oaapplicationmodule = pageContext.getApplicationModule(webBean);
            OADBTransactionImpl t = (OADBTransactionImpl)oaapplicationmodule.getOADBTransaction();

            OracleCallableStatement proc = (OracleCallableStatement)t.createCallableStatement(lquery, -1);
            proc.execute();
            t.commit();



            }
            catch(SQLException sqlexception)
            {
            throw OAException.wrapperException(sqlexception);
            }

            .....
            ....
            ...
            • 3. Re: Need help in resolving the below error - SQL statement to execute cannot be
              AnilSharma
              Then do not use Callable Statement. User Prepared Statement for simple SQL
              import java.sql.PreparedStatement;
              import java.sql.ResultSet;
              
              String xcode = "";
              String xname = "";
              
              String ci_type_id = pageContext.getParameter("CIItemCreate");
              
              Connection conn = oaapplicationmodule.getOADBTransaction().getJdbcConnection();
                         try
                         {
                           String Query = "SELECT SHORT_NAME,NAME FROM XX_CI_TYPES_TL WHERE ci_type_id =? ";
                           PreparedStatement stmt = conn.prepareStatement(Query);
                           stmt.setInt(1, ici_type_id);
                           for(ResultSet resultset = stmt.executeQuery(); resultset.next();)
                           {
                             xcode  = resultset.getString(1);
                             xname = resultset.getString(2);
                           }
                           stmt.close();
                         }
                         catch(Exception e)
                         {
                           throw OAException.wrapperException(e);
                         }
                       
              Thanks
              --Anil
              http://oracleanil.blogspot.com/
              • 4. Re: Need help in resolving the below error - SQL statement to execute cannot be
                AnilSharma
                http://oracleanil.blogspot.com/2009/04/serialqueryvoxml.html

                Thanks
                --Anil
                http://oracleanil.blogspot.com
                • 5. Re: Need help in resolving the below error - SQL statement to execute cannot be
                  781651
                  Hi,

                  As per the error message
                  java.sql.SQLException: SQL statement to execute cannot be empty or null
                  lquery value is still null.

                  Try the below code:
                  -- CO Code 
                  
                  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
                  {
                  
                  super.processFormRequest(pageContext, webBean);
                  OAApplicationModule am =
                  (OAApplicationModule)pageContext.getApplicationModule(webBean);
                  
                  String lquery = "";
                  
                  
                  
                  if (pageContext.getParameter("ES_SearchBT")!= null ) 
                  {
                  lquery =" BEGIN select * from XXCHR_ELEMENT_SET_TABLE;  END;";
                  // System.out.println (" Query after radio Button 1 " + lquery);
                  
                  try {
                  OAApplicationModule oaapplicationmodule = pageContext.getApplicationModule(webBean);
                  OADBTransactionImpl t = (OADBTransactionImpl)oaapplicationmodule.getOADBTransaction();
                  
                  
                  OracleCallableStatement proc = (OracleCallableStatement)t.createCallableStatement(lquery, 1);
                  proc.execute();
                  t.commit();
                  }
                  catch(SQLException sqlexception)
                  {
                  throw OAException.wrapperException(sqlexception);
                  }
                  
                  } // end of if statement
                  One suggestion:

                  Since you want to execute a query only, using VO is recommended. Good practice is not to use
                  select *
                  instead mention all columns you want in the select clause.

                  regards,
                  Anand
                  • 6. Re: Need help in resolving the below error - SQL statement to execute cannot be
                    747119
                    Hi Anil ,

                    Thanks for quick suggestion. But a small doubt ....

                    What need to be imported for type Connection and PreparedStatement fro the below code snippet.

                    Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();
                    PreparedStatement stmt = conn.prepareStatement(lquery);

                    Also I am getting this below error

                    "Error(148,39): non-static method getOADBTransaction() cannot be referenced from a static context"

                    Does it impact anything ?

                    Raghu
                    • 7. Re: Need help in resolving the below error - SQL statement to execute cannot be
                      AnilSharma
                      import java.sql.Connection;
                      import java.sql.PreparedStatement;

                      Thanks
                      --Anil                                                                                                                                                                               
                      • 8. Re: Need help in resolving the below error - SQL statement to execute cannot be
                        AnilSharma
                        check the code pasted by me again
                        Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();
                        Connection conn = oaapplicationmodule.getOADBTransaction().getJdbcConnection(); //Right one
                        Thanks
                        --Anil                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                        • 9. Re: Need help in resolving the below error - SQL statement to execute cannot be
                          747119
                          Hi Anil,

                          It works fine ! Thanks a lot :-)

                          Raghu