ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jesse Reimann" <jreim...@ctigroup.com>
Subject RE: Exhausted resultset exception when use Oracle REF cursor type as output parameter from Stored Procedure
Date Mon, 19 May 2008 13:44:29 GMT
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:Eugene.Dvorkin@artstor.org] 
Sent: Friday, May 16, 2008 10:30 AM
To: user-java@ibatis.apache.org
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


Mime
View raw message