0 Replies Latest reply on Mar 10, 2008 3:48 PM by Jonathan Winterflood

    Oracle prepared statement with cursors fails with Datasource

    Jonathan Winterflood Newbie

      From a servlet in JBoss 4.05GA, we are trying to access an Oracle 10g database and execute a PreparedStatement that uses cursors for sub-results.
      The point is to use this result to build an xml document through an OracleXMLQuery.
      The libs are ojdbc14, xsu12, xmlparserv2, versions 10.2.0.2

      The query goes like so (sanitized version, see full below):

      select
       field,
       cursor(
       select *
       from other
       where other.field = table.field
       ) sub
      from
       table
      


      If the connection to the database is created with a DriverManager, all is fine, and we get the expected result, that is:

      <ROWSET>
       <ROW num="1">
       <field>data</field>
       <sub>
       <sub_row num="1">
       --sub fields--
       </sub_row>
      et caetera
      


      However, using a DataSource does not: the result is (translated):
      <ERROR>oracle.xml.sql.OracleXMLSQLException: ORA-00604: error occurred at recursive SQL level 1
      ORA-01000: maximum open cursors exceeded
      </ERROR>
      


      I have had a look around the net as to the use of cursors, oracle and datasources, but all i found was problems with unclosed statements &c. building up to cause the problem. In this case the problem is immediate and ever-present in the Datasource case and never in the DriverManager. I also found a case about using a prepared call that retuns a REF Cursor, but I don't think it applies since this is a prepared statement.

      The source of the minimal servlet used to reproduce the problem follows:

      /*package & imports*/
      
      public class StatementServlet extends HttpServlet {
      
       String query = "select platform_code, cursor(select * from cycle_voyage where cycle_voyage.platform_code = platform.platform_code) cycles from platform where platform.description = 'Japan'";
      
       @Override
       protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
       boolean ds = Boolean.parseBoolean(req.getParameter("ds"));
       resp.setContentType("text/plain");
       try {
       String result = (ds? workDS(): workDM());
       resp.getOutputStream().print(result);
       } catch (Exception e) {
       e.printStackTrace(new PrintStream(resp.getOutputStream()));
       e.printStackTrace();
       }
       }
      
       protected String workDM() throws Exception {
       DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); // NOT oracle.jdbc.driver.OracleDriver
       Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@--host--:--port--:--service--","--user--","--pwd--");
      
       PreparedStatement ps = connection.prepareStatement(query);
       ResultSet resultSet = ps.executeQuery();
      
       //get XML result
       OracleXMLQuery oxq = new OracleXMLQuery(connection, resultSet);
       oxq.useNullAttributeIndicator(false);
      
       String result = oxq.getXMLString();
       resultSet.close(); ps.close(); connection.close();
       return result;
       }
      
       protected String workDS() throws Exception {
       Context envContext = (Context)new InitialContext().lookup("java:");
       DataSource ds = (DataSource) envContext.lookup("--ourDs--");
       Connection connection = ds.getConnection();
      
       PreparedStatement ps = connection.prepareStatement(query);
       ResultSet resultSet = ps.executeQuery();
      
       //get XML result
       OracleXMLQuery oxq = new OracleXMLQuery(connection, resultSet);
       oxq.useNullAttributeIndicator(false);
      
       String result = oxq.getXMLString();
      
       resultSet.close(); ps.close(); connection.close();
       return result;
       }
      }
      


      The DataSource is defined in it's own -ds.xml file thus:
      <datasources>
       <local-tx-datasource>
       <jndi-name>ourDS</jndi-name>
       <connection-url>jdbc:oracle:thin:@--host--:--port--:--service--</connection-url>
       <driver-class>oracle.jdbc.OracleDriver</driver-class>
       <user-name>--user--</user-name>
       <password>--pwd--</password>
       <min-pool-size>5</min-pool-size>
       <max-pool-size>100</max-pool-size>
       <!-- Checks the Oracle error codes and messages for fatal errors -->
       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
       </local-tx-datasource>
      </datasources>
      


      I'm stuck on this one... I don't think I'm asking too much from the Datasource... more likely I'm missing some config parameter.

      Thanks in advance for any pointers.
      Jonathan