I will take a closer look at this during the week-end. At first sight
(and with a lot of speculation... it's getting late) it looks you're
trying to use something which is not yet in iBATIS... What you can do
with iBATIS/Oracle is to get a cursor back (without mapping) it... look
for example at JIRA IBATIS-53. What you seem to be doing is trying to
get the out parameter to map to the ResultMap... and this works because
you're very lucky.... the first out ResultSet iBATIS finds will probably
be used as return ResultSet.
I would also expect the cursor to be closed if iBATIS sees the out
parameter as the actual return ResultSet. But it's not "intended use" of
iBATIS.
So next question... which query do you use to see how many cursors are
open on Oracle side. There are some "cursor tables" in Oracle which show
a cursor as open while in fact it's just lingering.
And if all my assumptions in the first alinea are correct, then what you
really want to do is not yet in iBATIS (the clean way anyway). And may
not be in iBATIS for the next weeks/months ... in a clean way.
Regards,
Sven
[EMAIL PROTECTED] wrote:
Hai,
My code goes like this...
<sqlMap>
<typeAlias alias="Employee" type="test.Employee" />
<resultMap id="employee-map" class="Employee">
<result property="name" column="ENAME" />
<result property="employeeNumber" column="EMPNO" />
<result property="departmentNumber" column="DEPTNO" />
</resultMap>
<parameterMap id="single-rs" class="Employee" >
<parameter property="in1" jdbcType="int"
javaType="java.lang.Integer" mode="IN"/>
<parameter property="output1" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT"/>
</parameterMap>
<procedure id="GetSingleEmpRs" parameterMap="single-rs"
resultMap="employee-map">
{ call GetSingleEmpRS(?, ?) }
</procedure>
</sqlMap>
Java:
--------
package test;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class Main {
public static void main(String arg[]) throws Exception {
String resource;
Reader reader;
List list;
SqlMapClient sqlMap;
resource = "test/SqlMapConfig.xml";
reader = Resources.getResourceAsReader (resource);
sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
Employee emp = new Employee();
emp.setIn1(new Integer(10));
// use queryForList because the procedure map defines a
resultmap
// for the statement
list = sqlMap.queryForList("GetSingleEmpRs", emp);
System.out.println("--------------------");
System.out.println( list );
System.out.println("--------------------");
}
}
SP:
---
CREATE OR REPLACE PROCEDURE sp_GetSingleEmpRS (
p_in IN VARCHAR2,
p_cursor_o OUT sys_refcursor
)
IS
BEGIN
OPEN p_cursor_o FOR
SELECT name,
employeeNumber,
departmentNumber,
FROM employee;
END;
When i print the List items it list out all the selected employee infor
from DB. My problem is the Cursor opened at SP is not getting closed. Each
time when i execute this Java Class, one Cursor is getting incremented in
DB Side.
Do you have any solution for closing the Ref Cursors both from iBatis side
and application end.
I got a reply from Sven saying that, iBatis will close the Cursor,
Application has to close the Result Cursor. In the above application, the
Java class doesnot have the hold of the Result Cursor. Can you suggest me
with a sample code to over come this issue.
regards,
Clement
