<style>
/* Changing the layout to use less space for mobiles */
@media screen and (max-device-width: 480px), screen and (-webkit-min-device-pixel-ratio: 2) {
#email-body { min-width: 30em !important; }
#email-page { padding: 8px !important; }
#email-banner { padding: 8px 8px 0 8px !important; }
#email-avatar { margin: 1px 8px 8px 0 !important; padding: 0 !important; }
#email-fields { padding: 0 8px 8px 8px !important; }
#email-gutter { width: 0 !important; }
}
</style>
<div id="email-body">
<table id="email-wrap" align="center" border="0" cellpadding="0" cellspacing="0" style="background-color:#f0f0f0;color:#000000;width:100%;">
<tr valign="top">
<td id="email-page" style="padding:16px !important;">
<table align="center" border="0" cellpadding="0" cellspacing="0" style="background-color:#ffffff;border:1px solid #bbbbbb;color:#000000;width:100%;">
<tr valign="top">
<td bgcolor="#3e4c4e" style="background-color:#3e4c4e;color:#ffffff;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;line-height:1;"><img src="https://www.jboss.org/dms/hibernate/images/jira/jiraheader_hibernate.png" alt="" style="vertical-align:top;" /></td>
</tr><tr valign="top">
<td id="email-banner" style="padding:32px 32px 0 32px;">
<table align="left" border="0" cellpadding="0" cellspacing="0" width="100%" style="width:100%;">
<tr valign="top">
<td style="color:#505050;font-family:Arial,FreeSans,Helvetica,sans-serif;padding:0;">
<img id="email-avatar" src="https://hibernate.onjira.com/secure/useravatar?avatarId=10162" alt="" height="48" width="48" border="0" align="left" style="padding:0;margin: 0 16px 16px 0;" />
<div id="email-action" style="padding: 0 0 8px 0;font-size:12px;line-height:18px;">
<a class="user-hover" rel="pierrepost" id="email_pierrepost" href="https://hibernate.onjira.com/secure/ViewProfile.jspa?name=pierrepost" style="color:#6c797f;">Pierre Post</a>
created <img src="https://hibernate.onjira.com/images/icons/bug.gif" height="16" width="16" border="0" align="absmiddle" alt="Bug"> <a style='color:#6c797f;text-decoration:none;' href='https://hibernate.onjira.com/browse/HHH-7984'>HHH-7984</a>
</div>
<div id="email-summary" style="font-size:16px;line-height:20px;padding:2px 0 16px 0;">
<a style='color:#6c797f;text-decoration:none;' href='https://hibernate.onjira.com/browse/HHH-7984'><strong>Prepared statement for callable returning cursor not closed</strong></a>
</div>
</td>
</tr>
</table>
</td>
</tr>
<tr valign="top">
<td id="email-fields" style="padding:0 32px 32px 32px;">
<table border="0" cellpadding="0" cellspacing="0" style="padding:0;text-align:left;width:100%;" width="100%">
<tr valign="top">
<td id="email-gutter" style="width:64px;white-space:nowrap;"></td>
<td>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Issue Type:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<img src="https://hibernate.onjira.com/images/icons/bug.gif" height="16" width="16" border="0" align="absmiddle" alt="Bug"> Bug
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Affects Versions:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
4.1.9 </td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Assignee:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
Unassigned </td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Attachments:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
BOTEMPLATE.PKG_BOT_TESTS.pck, BOT_TEST_NUMVALUE.sql, CursorFromCallableTest.java, CursorFromCallableTest.log, NumValue.java, persistence.xml </td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Components:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
entity-manager </td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Created:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
11/Feb/13 9:02 AM
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Description:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<p style='margin-top:0;margin-bottom:10px;'>After migrating from Hibernate 3.6 to 4.1.9, the following use case fails with ORA-01000: maximum open cursors exceeded.</p>
<p style='margin-top:0;margin-bottom:10px;'>We have a PL/SQL package <tt>PKG_BOT_TESTS</tt> with a simple function <tt>f_test_ReturnNonEmptyCursor</tt> returning a cursor with 2 lines where the columns can be mapped to the table <tt>BOT_TEST_NUMVALUE</tt>:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeHeader panelHeader" style="border-bottom-width: 1px;"><b>BOTEMPLATE.PKG_BOT_TESTS.pck</b></div><div class="codeContent panelContent">
<pre class="code-sql">CREATE OR REPLACE PACKAGE PKG_BOT_TESTS IS
FUNCTION f_test_ReturnNonEmptyCursor RETURN SYS_REFCURSOR;
END PKG_BOT_TESTS;
/
CREATE OR REPLACE PACKAGE BODY PKG_BOT_TESTS IS
FUNCTION f_test_ReturnNonEmptyCursor RETURN SYS_REFCURSOR IS
l_Cursor SYS_REFCURSOR;
BEGIN
OPEN l_Cursor FOR
<span class="code-keyword">SELECT</span> 1 AS BOT_NUM,
'Line 1' AS BOT_VALUE
<span class="code-keyword">FROM</span> DUAL
UNION
<span class="code-keyword">SELECT</span> 2 AS BOT_NUM,
'Line 2' AS BOT_VALUE
<span class="code-keyword">FROM</span> DUAL;
RETURN(l_Cursor);
END f_test_ReturnNonEmptyCursor;
END PKG_BOT_TESTS;
/</pre>
</div></div>
<div class="code panel" style="border-width: 1px;"><div class="codeHeader panelHeader" style="border-bottom-width: 1px;"><b>BOT_TEST_NUMVALUE.sql</b></div><div class="codeContent panelContent">
<pre class="code-sql">create table BOT_TEST_NUMVALUE
(
bot_num NUMBER(16) not null,
bot_value VARCHAR2(255)
)
alter table BOT_TEST_NUMVALUE
add constraint PK_NUM primary key (BOT_NUM);</pre>
</div></div>
<p style='margin-top:0;margin-bottom:10px;'>The data class that maps this table looks like this:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeHeader panelHeader" style="border-bottom-width: 1px;"><b>NumValue.java (excerpt)</b></div><div class="codeContent panelContent">
<pre class="code-java">@Entity
@Table(name=<span class="code-quote">"BOT_NUMVALUE"</span>)
@NamedNativeQueries({
@NamedNativeQuery(name=<span class="code-quote">"NumValue.getSomeValues"</span>,
query = <span class="code-quote">"{ ? = call BOTEMPLATE.PKG_BOT_TESTS.f_test_ReturnNonEmptyCursor() }"</span>,
resultClass=NumValue.class, hints={@QueryHint(name=<span class="code-quote">"org.hibernate.callable"</span>, value=<span class="code-quote">"<span class="code-keyword">true</span>"</span>)})
})
<span class="code-keyword">public</span> class NumValue {
@Id
@Column(name=<span class="code-quote">"BOT_NUM"</span>, nullable=<span class="code-keyword">false</span>)
<span class="code-keyword">private</span> <span class="code-object">long</span> num;
@Column(name=<span class="code-quote">"BOT_VALUE"</span>)
<span class="code-keyword">private</span> <span class="code-object">String</span> value;
<span class="code-keyword">public</span> <span class="code-object">long</span> getNum() {
<span class="code-keyword">return</span> num;
}
<span class="code-keyword">public</span> void setNum(<span class="code-object">long</span> num) {
<span class="code-keyword">this</span>.num = num;
}
<span class="code-keyword">public</span> <span class="code-object">String</span> getValue() {
<span class="code-keyword">return</span> value;
}
<span class="code-keyword">public</span> void setValue(<span class="code-object">String</span> value) {
<span class="code-keyword">this</span>.value = value;
}
}</pre>
</div></div>
<p style='margin-top:0;margin-bottom:10px;'>As mentioned, the following test works fine with Hibernate 3.6 but fails with Hibernate 4.1.9 (<tt>ORA-01000: maximum open cursors exceeded</tt>):</p>
<div class="code panel" style="border-width: 1px;"><div class="codeHeader panelHeader" style="border-bottom-width: 1px;"><b>CursorFromCallableTest.java</b></div><div class="codeContent panelContent">
<pre class="code-java"><span class="code-keyword">public</span> class CursorFromCallableTest {
@Test
<span class="code-keyword">public</span> void testGetObjectListFromCallableCursor_X500() <span class="code-keyword">throws</span> Exception {
Properties emfProps = <span class="code-keyword">new</span> Properties();
emfProps.setProperty(<span class="code-quote">"hibernate.connection.url"</span>, <span class="code-quote">"..."</span>);
emfProps.setProperty(<span class="code-quote">"hibernate.connection.username"</span>, <span class="code-quote">"..."</span>);
emfProps.setProperty(<span class="code-quote">"hibernate.connection.password"</span>, <span class="code-quote">"..."</span>);
EntityManagerFactory emf = Persistence.createEntityManagerFactory(<span class="code-quote">"CursorFromCallableTest"</span>, emfProps);
EntityManager context = <span class="code-keyword">null</span>;
<span class="code-keyword">try</span> {
context = emf.createEntityManager();
<span class="code-keyword">for</span> (<span class="code-object">int</span> i = 0; i < 500; i++) {
Assert.assertEquals(2, getObjectListFromCalleableCursor(context).size());
}
} <span class="code-keyword">finally</span> {
<span class="code-keyword">if</span> (context != <span class="code-keyword">null</span>) {
context.close();
}
emf.close();
}
}
<span class="code-keyword">private</span> List<NumValue> getObjectListFromCalleableCursor(EntityManager context) {
TypedQuery<NumValue> query = context.createNamedQuery(<span class="code-quote">"NumValue.getSomeValues"</span>, NumValue.class);
<span class="code-keyword">return</span> query.getResultList();
}
}</pre>
</div></div>
<p style='margin-top:0;margin-bottom:10px;'>This is the JUnit stack trace:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">javax.persistence.QueryTimeoutException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1339)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:273)
at CursorFromCallableTest.getObjectListFromCalleableCursor(CursorFromCallableTest.java:40)
at CursorFromCallableTest.testGetObjectListFromCalleableCursor_X500(CursorFromCallableTest.java:27)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:76)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.hibernate.QueryTimeoutException: could not execute query
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:151)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.loader.Loader.doList(Loader.java:2519)
at org.hibernate.loader.Loader.doList(Loader.java:2502)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
at org.hibernate.loader.Loader.list(Loader.java:2327)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1783)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:231)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:157)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:264)
... 24 more
Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
ORA-06512: at <span class="code-quote">"BOTEMPLATE.PKG_BOT_TESTS"</span>, line 37
ORA-06512: at line 1
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1041)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.execute(NewProxyCallableStatement.java:3044)
at org.hibernate.dialect.Oracle8iDialect.getResultSet(Oracle8iDialect.java:516)
at org.hibernate.engine.jdbc.internal.proxy.CallableStatementProxyHandler.executeQuery(CallableStatementProxyHandler.java:56)
at org.hibernate.engine.jdbc.internal.proxy.CallableStatementProxyHandler.continueInvocation(CallableStatementProxyHandler.java:52)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at $Proxy13.executeQuery(Unknown Source)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2031)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
at org.hibernate.loader.Loader.doQuery(Loader.java:899)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
at org.hibernate.loader.Loader.doList(Loader.java:2516)
... 32 more</pre>
</div></div>
<p style='margin-top:0;margin-bottom:10px;'>The test may depend on your Oracle configuration of the maximum number of cursors. The number of iterations should be greater than the configured limit.</p>
<p style='margin-top:0;margin-bottom:10px;'>Please also note the attached <tt>CursorFromCalleable.log</tt> file. The information from c3p0 about the multiple prepared statement indicates Hibernate has not closed the prepared statement from the previous call as noted in <tt><a href="https://forum.hibernate.org/viewtopic.php?f=1&t=942656">https://forum.hibernate.org/viewtopic.php?f=1&t=942656</a></tt>.</p>
<p style='margin-top:0;margin-bottom:10px;'>I may supply additional information if needed. Thank you!</p>
</td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Environment:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
Hibernate 4.1.9 Final
<br/>
Oracle JDBC Thin Driver 11.2.0.3
</td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Project:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<a style="color:#6c797f;" href="https://hibernate.onjira.com/browse/HHH">Hibernate ORM</a>
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Labels:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
query
</td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Priority:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<img src="https://hibernate.onjira.com/images/icons/priority_critical.gif" height="16" width="16" border="0" align="absmiddle" alt="Critical"> Critical
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Reporter:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<a class="user-hover" rel="pierrepost" id="email_pierrepost" href="https://hibernate.onjira.com/secure/ViewProfile.jspa?name=pierrepost" style="color:#6c797f;">Pierre Post</a>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td><!-- End #email-page -->
</tr>
<tr valign="top">
<td style="color:#505050;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:10px;line-height:14px;padding: 0 16px 16px 16px;text-align:center;">
This message is automatically generated by JIRA.<br />
If you think it was sent incorrectly, please contact your JIRA administrators<br />
For more information on JIRA, see: <a style='color:#6c797f;' href='http://www.atlassian.com/software/jira'>http://www.atlassian.com/software/jira</a>
</td>
</tr>
</table><!-- End #email-wrap -->
</div><!-- End #email-body -->