Oracle REF CURSOR Solutions

This whiteboard is for the collaborative documentation and design as a start toward improving or extending current support for Oracle REF CURSOR types.

Current Working Solutions

If you have a current solution that works, please put it here.

Solution 1

...

Solution 2

...

Improving the Solutions

If you have an idea to improve one of the solutions above, but it requires a change to the iBATIS framework, put it here.

Improvement 1

I have modified the framework to allow all oracle cursors to be mapped by a statement's resultMap so you can call the queryForList() method and have the oracle cursor results returned in the list. I also added the ability to specifiy a resultMap to use with each of the oracle cursor parameters to support the queryForObject() call. I have tested this code with IBATIS 2.1.6.598

here are my sample sqlMap:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<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="map" >
        <parameter property="in1" jdbcType="int" javaType="java.lang.Integer" mode="IN"/>
        <parameter property="output1" jdbcType="ORACLECURSOR" mode="OUT"/>      
    </parameterMap>
     
    <procedure id="GetSingleEmpRs" parameterMap="single-rs" resultMap="employee-map">
         { call scott.example.GetSingleEmpRS(?, ?) }
    </procedure>
    
    <parameterMap id="double-rs" class="map" >
        <parameter property="in1" jdbcType="int" javaType="java.lang.Integer" mode="IN"/>
        <parameter property="output1" jdbcType="ORACLECURSOR" mode="OUT" resultMap="employee-map" />
        <parameter property="output2" jdbcType="ORACLECURSOR" mode="OUT" resultMap="employee-map" />        
    </parameterMap>
    
    <procedure id="GetDoubleEmpRs" parameterMap="double-rs" >
        { call scott.example.GetDoubleEmpRS(?, ?, ?) }
    </procedure>
</sqlMap>

Here is my sample code that makes use of the maps:

Main.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);
	        Map map = new HashMap();
	        map.put("in1", new Integer(10));
	        // use queryForList because the procedure map defines a resultmap
	        // for the statement
	        list = sqlMap.queryForList("GetSingleEmpRs", map); 

	        System.out.println("--------------------");
	        System.out.println( list );
	        System.out.println("--------------------");
	        
	        map = new HashMap();
	        map.put("in1", new Integer(10));
	        // use queryForObject because the procedure map does not define a 
	        // result map for the statement
	        sqlMap.queryForObject("GetDoubleEmpRs", map);
	        
	        System.out.println("--------------------");
	        System.out.println( map.get("output1"));
	        System.out.println( map.get("output2"));
	        System.out.println("--------------------");
	        
	           
	    }
}

Here is the output from Main.java

 
--------------------
[Employee[name=CLARK,id=7782,dept=10], Employee[name=KING,id=7839,dept=10], Employee[name=MILLER,id=7934,dept=10]]
--------------------
--------------------
[Employee[name=CLARK,id=7782,dept=10], Employee[name=KING,id=7839,dept=10], Employee[name=MILLER,id=7934,dept=10]]
[Employee[name=ADAMS,id=7876,dept=20], Employee[name=ALLEN,id=7499,dept=30], Employee[name=BLAKE,id=7698,dept=30], 
 Employee[name=FORD,id=7902,dept=20], Employee[name=JAMES,id=7900,dept=30], Employee[name=JONES,id=7566,dept=20], 
 Employee[name=MARTIN,id=7654,dept=30], Employee[name=SCOTT,id=7788,dept=20], Employee[name=SMITH,id=7369,dept=20],
 Employee[name=TURNER,id=7844,dept=30],Employee[name=WARD,id=7521,dept=30]]
--------------------

All test code and modified src are included in this page's attachments. Most of my PL/SQL code just returns a single ref cursor so it is nice just to define a resultMap for the statement and just have it work automatically with a queryForList() call. The second form handles the more complicated case of returning two or more cursors. I have tested this code with oracle 9i using the thin driver (ojdbc14.jar) with jdk 1.4

Warning

This implementation does not support

Paging functionality
Remappable results for parameter assigned resultMaps

Improvement 2

...

New Solutions

If you have a completely new solution that can only work by changing or extending the iBATIS framework, please put it here.

New 1

...

New 2

...

Page Operations

Browse Space