[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Oracle prepared statement with cursors fails with Datasource

[jwinterf]

>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

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4135452#4135452

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4135452