Dashboard > iBATIS DataMapper > Home > Oracle REF CURSOR Solutions > Information > Page Comparison
Oracle REF CURSOR Solutions
Version 25 by ifnu
on Jul 13, 2009 14:33.


compared with
Current by ifnu
on Jul 13, 2009 14:38.

(show comment)
 
Key
These lines were removed. This word was removed.
These lines were added. This word was added.

View page history


There are 1 changes. View first change.

 *This whiteboard is for the collaborative documentation and design as a start toward improving or extending current support for Oracle REF CURSOR types.*
  
 h2. Current Working Solutions
  
 *If you have a current solution that works, please put it here.*
  
 h3. Solution 1
 This is sample mapping for a Stored procedure which return Ref cursor in it's out parameter
 {code:xml}
 <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
  
 <sqlMap namespace="KOMUNIKA_REPORT">
  <resultMap id="BaseResultMap" class="javaapplication4.StockAreaAndWarehouse" >
  <result column="PRODUCT_CODE" property="productCode" />
  <result column="PRODUCT_NAME" property="productName" />
  <result column="INCOMING" property="incoming" />
  <result column="UNIT_SOLD" property="unitSold" />
  <result column="TOTAL_STOCK" property="totalStock" />
  </resultMap>
  <parameterMap id="resultMap" class="java.util.Map">
  <parameter property="result" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/>
  </parameterMap>
  <procedure id="selectStockAreaAndWarehouse"
  parameterMap="resultMap"
  resultMap="BaseResultMap"
  >
  { call KOMUNIKA.LP_STOCK_AREA_WAREHOUSE(?) }
  </procedure>
 </sqlMap>
 {code}
 You should aware that javaType="java.sql.ResultSet" attribute must set to be able to map result to a Class, if it not set than your effort is a waste (like mine :( )
  
 Oracle's PL/SQL Stored procedure
 {code}
 CREATE OR REPLACE PROCEDURE KOMUNIKA.LP_STOCK_AREA_WAREHOUSE(
 RESULT_CURSOR out TYPES.ref_cursor) as
 BEGIN
  OPEN RESULT_CURSOR FOR select PRODUCT_CODE, PRODUCT_NAME, INCOMING, UNIT_SOLD, TOTAL_STOCK
  from KOMUNIKA.STOCK_AREA_AND_WAREHOUSE ;
 END LP_STOCK_AREA_WAREHOUSE;
 {code}
  
 To make that PL/SQL work you should declare a package so that TYPES.ref_cursor recognized by oracle
 {code}
 CREATE OR REPLACE PACKAGE KOMUNIKA.Types
 AS
  TYPE ref_cursor IS REF CURSOR;
 END;
 {code}
  
 To test wether it works or not you need sqlMapConfig
 {code:xml}
 <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
 <sqlMapConfig>
  <settings useStatementNamespaces="true" classInfoCacheEnabled="true" />
  <transactionManager type="JDBC" >
  <dataSource type="SIMPLE">
  <property name="JDBC.Driver" value="oracle.jdbc.OracleDriver"/>
  <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
  <property name="JDBC.Username" value="YOUUSERHERE"/>
  <property name="JDBC.Password" value="YOURPASSWORDHERE"/>
  <property name="JDBC.DefaultAutoCommit" value="true" />
  </dataSource>
  </transactionManager>
  <sqlMap resource="javaapplication4/KOMUNIKA_REPORT_SqlMap.xml" />
 </sqlMapConfig>
 {code}
 oh, one more setting which is realy important is useStatementNamespaces="true", i find it out in a hardway when i want to use namespace
  At last here is Java code used to test the configuration. This java code i derived from improvement solution below,
   
 At last here is Java code used to test the configuration. This java code derived from improvement solution below,
 {code:title=Main.java}
 package javaapplication4;
  
 import com.ibatis.common.resources.Resources;
 import com.ibatis.sqlmap.client.SqlMapClient;
 import com.ibatis.sqlmap.client.SqlMapClientBuilder;
 import java.io.Reader;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
  
 /**
  *
  * @author ifnu<mailto:ifnubima@gmail.com>
  */
 public class Main {
  public static void main(String[] args) throws Exception {
  String resource;
  Reader reader;
  List list;
  SqlMapClient sqlMap;
  resource = "javaapplication4/ibatis.xml";
  reader = Resources.getResourceAsReader (resource);
  sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
  Map map = new HashMap();
  // use queryForList because the procedure map defines a resultmap
  // for the statement
  list = sqlMap.queryForList("KOMUNIKA_REPORT.selectStockAreaAndWarehouse", map);
  System.out.println(list);
  }
 }
 {code}
  
 you can download the code above : http://opensource.atlassian.com/confluence/oss/download/attachments/561/iBatisCallSpRefCursorOracle.zip
  
 h3. Solution 2
  
 ...
  
 h2. 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.*
  
 h3. 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:
  {code:xml}
 <?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" javaType="java.sql.ResultSet" 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" javaType="java.sql.ResultSet" mode="OUT" resultMap="employee-map" />
  <parameter property="output2" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="employee-map" />
  </parameterMap>
  
  <procedure id="GetDoubleEmpRs" parameterMap="double-rs" >
  { call scott.example.GetDoubleEmpRS(?, ?, ?) }
  </procedure>
 </sqlMap>
  {code}
  
 Here is my sample code that makes use of the maps:
 {code:title=Main.java|borderStyle=solid}
 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("--------------------");
  
  
  }
 }
  
  
 {code}
  
 Here is the output from Main.java
  {noformat}
 --------------------
 [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]]
 --------------------
  {noformat}
  
 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:title=Warning}
 This implementation does not support
  - Paging functionality
  - Remappable results for parameter assigned resultMaps
 {warning}
 h3. Improvement 2
  
 ...
  
 h2. New Solutions
  
 *If you have a completely new solution that can only work by changing or extending the iBATIS framework, please put it here.*
  
 h3. New 1
  
 ...
  
 h3. New 2
  
 ...
  
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