Stored Procedure 

The following example shows getting character delimited list of distinct hobbies from a oracle stored procedure. As procedures doesn't return a value, we need to map an OUT or INOUT parameter to get result back from the stored procedure. We can specify the same using mode attribute of parameter tag. It takes IN, OUT, INOUT as values.

File Name  :   /IBATIS001/config/basic/StoredProcedure.xml

view sourceprint? 01 <?xml version="1.0" encoding="UTF-8"?> 02 <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" 03 "http://ibatis.apache.org/dtd/sql-map-2.dtd"> 04   05 <sqlMap namespace="Student"> 06   07     <parameterMap id="hobbyParams" class="map"> 08         <parameter property="sep" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" /> 09         <parameter property="hobbyStr" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" /> 10     </parameterMap> 11       12     <procedure id="hobbyList" parameterMap="hobbyParams"> 13      { call distinct_hobbies( ? , ? ) } 14     </procedure> 15       16 </sqlMap>

File Name  :   /IBATIS001/config/oracle_scripts/distinct_hobbies_proc.sql

view sourceprint? 01 -- Oracle procedure to return distinct hobbies through 'hobbyStr' OUT parameter 02 create or replace procedure distinct_hobbies (sep IN VARCHAR2, hobbyStr OUT VARCHAR2) is 03   04   CURSOR hobby_cur IS SELECT distinct(hobby) as hobby FROM STUDENT; 05   hobby_rec hobby_cur%rowtype; 06   07 begin 08   09   FOR hobby_rec in hobby_cur 10   LOOP 11     IF hobby_cur%ROWCOUNT = 1 THEN 12         hobbyStr := hobby_rec.hobby; 13     ELSE 14         hobbyStr := hobbyStr || sep || hobby_rec.hobby; 15     END IF; 16     17   END LOOP; 18   19 end;

File Name  :   com/bethecoder/tutorials/ibatis/tests/basic/StoredProcedure.java  Author  :   Sudhakar KV Email  :   kvenkatasudhakar@gmail.com

package com.bethecoder.tutorials.ibatis.tests.basic; import java.io.IOException; import java.io.Reader; import java.sql.SQLException; 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 StoredProcedure {   /**    * @param args    * @throws IOException     * @throws SQLException     */   public static void main(String[] args) throws IOException, SQLException {     Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");     SqlMapClient sqlMapClent = SqlMapClientBuilder.buildSqlMapClient(reader);     Map<String, String> dataMap = new HashMap<String, String>();     dataMap.put("sep", ",");          System.out.println("Before Proc Call : " + dataMap);     sqlMapClent.queryForObject("Student.hobbyList", dataMap);     System.out.println("After Proc Call : " + dataMap);               dataMap = new HashMap<String, String>();     dataMap.put("sep", "#");          System.out.println("Before Proc Call : " + dataMap);     sqlMapClent.queryForObject("Student.hobbyList", dataMap);     System.out.println("After Proc Call : " + dataMap);   } }

Before Proc Call : {sep=,}
After Proc Call : {hobbyStr=Reading books,Cricket,Painting,Football, sep=,}

Before Proc Call : {sep=#}
After Proc Call : {hobbyStr=Reading books#Cricket#Painting#Football, sep=#}