I've previously been told when I was asking about Oracle REF Cursors that I should be using "update" instead of "queryForList". Technically "queryForList" doesn't fully work correctly with REF Cursors since the return list isn't being populated even though the map passed into "queryForList" does seem to get populated.
Maybe you can give try switching to using "update" and see if the problems still arise. Thanks, Jesse -----Original Message----- From: Eugene Dvorkin [mailto:[EMAIL PROTECTED] Sent: Friday, May 16, 2008 10:30 AM To: [email protected] Subject: Exhausted resultset exception when use Oracle REF cursor type as output parameter from Stored Procedure Hi, We developed application with Spring, Hibernate and Ibatis. We use Oracle Application Server and configured database Connection Pool provided by Application Server. Then we start stress test our application and discovered problem that appears only under load and only in a call to Stored Procedures than returns REF Cursor. Caused by: com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in org/artstor/pojo/CategoryThumbnail.xml. --- The error occurred while applying a result map. --- Check the CategoryThumbnail.result. --- Check the result mapping for the 'thumbnailImgUrl' property. --- Cause: java.sql.SQLException: Exhausted Resultset at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery WithCallback(GeneralStatement.java:185) when we use plain select statement, it works fine. The way we call stored procedures is: <parameterMap id="searchCategoryParameters" class="map"> <parameter property="o" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT" resultMap="searchResult" /> <parameter property="keywords" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" resultMap="searchResult"/> <parameter property="objTypeId" jdbcType="NUMBER" javaType="int" mode="IN" resultMap="searchResult"/> <parameter property="orderBy" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" resultMap="searchResult"/> <parameter property="outLength" jdbcType="NUMBER" javaType="int" mode="IN" resultMap="searchResult"/> <parameter property="categoryId" jdbcType="NUMBER" javaType="long" mode="IN" resultMap="searchResult"/> <parameter property="thumbnailOnly" jdbcType="NUMBER" javaType="int" mode="IN" resultMap="searchResult"/> <parameter property="start_pos_in" jdbcType="NUMBER" javaType="int" mode="IN" resultMap="searchResult"/> <parameter property="page_length_in" jdbcType="NUMBER" javaType="int" mode="IN" resultMap="searchResult"/> </parameterMap> <procedure id="searchAllCollection " parameterMap="searchParameters"> {?=call pkg_object_adv_search_new.do_search_v3 (?,?,?,?,?,?,?,?,?,?,?,?,?,?)} </procedure> In java : HashMap<Object, Object> parameters = new HashMap<Object, Object>(); SqlMapClientTemplate client = getSqlMapClientTemplate(); . . client.queryForList("searchAllCollections ", parameters); result = (List) parameters.get("o"); Stored Procedure declaration: FUNCTION search_collections ( keyword_words_in IN VARCHAR2, obj_type_id_in IN NUMBER, order_by_in IN VARCHAR2 DEFAULT ' sco1 desc, sco2 desc', outlength_in IN NUMBER DEFAULT 501, collection_ids_in IN VARCHAR2, thumbnail_only_in IN BOOLEAN ) RETURN pkg_object_search_cursor.return_cur; Where return type is TYPE object_cur IS REF CURSOR When we accessing result from stored procedure, we got problem. Please help Sincerely, eugene
