Dashboard > iBATIS DataMapper > Home > Frequently Asked Questions > How do I use an Oracle Ref Cursor?
How do I use an Oracle Ref Cursor?
Added by Jan Vissers, last edited by Jan Vissers on Sep 04, 2006  (view change)
Labels: 
(None)


iBATIS DataMapper version 2.2.0.638

This FAQ is based on iBATIS DataMapper (Java) 2.2.0.638

Release 2.2.0 of iBATIS DataMapper natively supports Oracle REF Cursors - without the need to create a custom type handler. This FAQ shows a very small example on how to use it.

We start with the (Oracle) database related stuff. For this example we've created an Oracle user ibatis. In SQL*Plus (or whichever tool you use) log in as this user and run the commands shown below.

REF_CURSOR_TEST
CREATE TABLE REFS (
    ID   NUMBER       NOT NULL PRIMARY KEY
    , NAME VARCHAR2(50) NOT NULL
    );
    
    CREATE OR REPLACE PACKAGE REFS_PCK AS
    TYPE      REF_CURSOR_T IS REF CURSOR;
    FUNCTION  GET_REFS RETURN REF_CURSOR_T;
    END REFS_PCK;
    /
    
    CREATE OR REPLACE PACKAGE BODY REFS_PCK IS
    FUNCTION GET_REFS RETURN REF_CURSOR_T
    IS
    L_CURSOR REF_CURSOR_T;
    BEGIN
    OPEN L_CURSOR FOR SELECT * FROM REFS;
    RETURN L_CURSOR;
    END GET_REFS;
    END REFS_PCK;
    /
    
    insert into refs values(1,'Jan');
    insert into refs values(2,'Danielle');
    insert into refs values(3,'Tessa');

We create a simple Java bean class to hold REFS' records.

com.cumquatit.examples.ibatis.refs.Ref
package com.cumquatit.examples.ibatis.refs;
    
    public class Ref {
    private int id;
    
    private String name;
    
    public int getId() {
    return id;
    }
    
    public void setId(int id) {
    this.id = id;
    }
    
    public String getName() {
    return name;
    }
    
    public void setName(String name) {
    this.name = name;
    }
    
    public String toString() {
    return ("id=" + id + ", name=" + name);
    }
    }

Next create a mapping file for the REFS table.

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
    <sqlMap>
    
    <typeAlias alias="Ref" type="com.cumquatit.examples.ibatis.refs.Ref" />
    
    <resultMap class="Ref" id="ref-mapping">
    <result property="id" column="ID" />
    <result property="name" column="NAME" />
    </resultMap>
    
    <parameterMap id="output" class="map">
    <parameter property="o" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT" resultMap="ref-mapping" />
    </parameterMap>
    
    <procedure id="getRefs" parameterMap="output">{ ? = call refs_pck.get_refs }</procedure>
    
    </sqlMap>

Also we need a sqlMapConfig file. Below an example of this file.

<?xml version="1.0"?>
    <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
    <sqlMapConfig>
    <transactionManager type="JDBC" commitRequired="false">
    <dataSource type="SIMPLE">
    <property name="JDBC.Driver" value="oracle.jdbc.OracleDriver" />
    <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@//flash.cumquat.office:1524/neon" />
    <property name="JDBC.Username" value="ibatis" />
    <property name="JDBC.Password" value="ibatis" />
    </dataSource>
    </transactionManager>
    <sqlMap resource="com/cumquatit/examples/ibatis/refs/Ref.xml" />
    </sqlMapConfig>

And to finish up, a small tester program.

com.cumquatit.examples.ibatis.refs.RefTester
package com.cumquatit.examples.ibatis.refs;
    
    import java.io.Reader;
    import java.util.HashMap;
    import java.util.Map;
    
    import com.ibatis.common.resources.Resources;
    import com.ibatis.sqlmap.client.SqlMapClient;
    import com.ibatis.sqlmap.client.SqlMapClientBuilder;
    
    public class RefTester {
    public static void main(String[] args) throws Exception {
    String resource;
    Reader reader;
    SqlMapClient sqlMap;
    resource = "com/cumquatit/examples/ibatis/refs/SqlMapConfig.xml";
    reader = Resources.getResourceAsReader(resource);
    sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
    Map map = new HashMap();
    sqlMap.queryForObject("getRefs", map);
    System.out.println(map.get("o"));
    }
    }

That's all folks.


Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators