jstl sql maximum open cursors exceeded

[homecurr]

Hi, here is my jsp:

....

<sql:transaction dataSource="jdbc/myds">
<c:catch var="error">
<c:forEach begin="1" end="somenumber">
<sql:update>
...INSERT something into table...

</sql:update>
</c:forEach>
</c:catch>
</sql:transaction>
myds is a datasource configured as:

<ResourceParams name="jdbc/myds">
<parameter>
<name>maxWait</name>
<value>5000</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>4</value>
</parameter>
<parameter>
<name>password</name>
<value>****</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbcracle:thinari:1521:ckdev</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.driver.OracleDriver</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>username</name>
<value>****</value>
</parameter>
</ResourceParams>

WHen I run this page, I got "ORA-01000: maximum open cursors exceeded
..". It happened after inserting about 300 rows.

Why? How can I fix it?

 

[Murray]

Hi, here is my jsp:

...

<sql:transaction dataSource="jdbc/myds">
<c:catch var="error">
<c:forEach begin="1" end="somenumber">
<sql:update>
...INSERT something into table...

</sql:update>
</c:forEach>
</c:catch>
</sql:transaction>
myds is a datasource configured as:

<ResourceParams name="jdbc/myds">
<parameter>
<name>maxWait</name>
<value>5000</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>4</value>
</parameter>
<parameter>
<name>password</name>
<value>****</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbcracle:thinari:1521:ckdev</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.driver.OracleDriver</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>username</name>
<value>****</value>
</parameter>
</ResourceParams>

WHen I run this page, I got "ORA-01000: maximum open cursors exceeded
.". It happened after inserting about 300 rows.

Why? How can I fix it?

It's because the sql:update tag isn't closing the PreparedStatement after
each iteration of your loop. You'll end up with hundreds of open statements
which is not good. I don't know if it's a bug/oversight in the JSTL code, or
perhaps they never intended it to be used in the way you're trying. I had a
look at the source code for UpdateTagSupport and confirmed that the
Statement itself is never closed! It is closed indirectly when it closes the
Connection, but this never happens if the update is inside a sql:transaction
block

Solutions:
- Increase the maximum number of open cursors in Oracle. Not really a
good solution, especially if you don't know how many you'll need
- Modify the UpdateTagSupport code so that it closes the
PreparedStatement. You'd probably only need to add a line or two to the
following block of code in doEndTag()

try {
PreparedStatement ps = conn.prepareStatement(sqlStatement);
setParameters(ps, parameters);
result = ps.executeUpdate();
}
catch (Throwable e) {
throw new JspException(sqlStatement + ": " + e.getMessage(), e);
}