ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jan Vissers <Jan.Viss...@cumquat.nl>
Subject Re: [2.2.0] Support for Oracle Ref Cursor
Date Sun, 27 Aug 2006 13:55:22 GMT
Well, this seems to work out of the box. A small example show below.

In Oracle:

    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;
    /

Your mapping file:

    <?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="Ref" type="refs.Ref" />

        <resultMap class="Ref" id="Refs-map">
            <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="Refs-map"/>
        </parameterMap>

        <procedure id="getRefs" parameterMap="output">{ ? = call
    refs_pck.get_refs }</procedure>
    </sqlMap>


The test code:

    public static void main(String[] args) throws Exception {
            String resource;
            Reader reader;
            SqlMapClient sqlMap;
            resource = "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"));
        }


Jan Vissers wrote:
> From the release notes:
>
>     o Added support for ResultSet OUT params from stored procs (e.g. 
> Oracle Ref Cursor)
>
> My question; is this out of the box or do we (still) have to create 
> our own TypeHandler?
> If out of the box - who can provide us with an example?
>
>

-- 
Cumquat Information Technology
De Dreef 19
3706 BR Zeist
T +31 (0)30 - 6940490
F +31 (0)30 - 6940499
http://www.cumquat.nl

Jan.Vissers@cumquat.nl
M +31 6 51 169 556



Mime
View raw message