2:48 p.m.
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#...
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...