RE: Looking for sample code for processing result set returned as Ref Cursor (Oracle)

[Rao, Satish]

Title: Message

Hi Anna,

 

You need to use a TypeHandlerCallback to map RefCursor and then iterate thru the ResultSet.

 

Here's a sample code.

 

InOutParams.java

public class InOutParams implements Serializable {

 

 //IN parameters
 private String state;
 private ResultSet outRS;

 

......

 

 public void setOutRS(ResultSet set) {
  outRS = set;
  setOutList();
 }

 

 public void setOutList() {

 

  outList = getList(this.getOutRS);
  
 }

 

 private List getList(ResultSet rs)
 {
  List list = new ArrayList();

 

  try {
    while (rs.next()) {
     //iterate thru rs and map it to your POJO

 

     list.add(POJO);
    }

 

    //closing resultset
    release();

 

  } catch (SQLException ex) {
   ex.printStackTrace();
   release();
  } finally {
   release();
  }
 
  return list;
 }

 

sqlMap.xml

 <parameterMap id="inout-param" class="InOutParams" >
      <parameter property="state" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
      <parameter property="outRS" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT"/>
 </parameterMap>

 

 <procedure id="storedprocedure" parameterMap="inout-param">
  {call PROCEDURE(?,?)}
 </procedure>

 

SqlMapConfig.xml

<sqlMapConfig>
 <settings
  cacheModelsEnabled="true"
  enhancementEnabled="false"
  lazyLoadingEnabled="false"
  maxRequests="32"
  maxSessions="10"
  maxTransactions="5"
  useStatementNamespaces="false"
  errorTracingEnabled="true"/>
 

<typeHandler javaType="java.sql.ResultSet" callback="RefCursorHandler"/>

 <sqlMap resource="sqlMap.xml"/>
</sqlMapConfig>

RefCursorHandler.java

public class RefCursorHandler implements TypeHandlerCallback {
      private ResultSet result;

     public void setParameter(ParameterSetter arg0, Object arg1) throws SQLException {
          if (arg1 != null) {
               arg0.setObject(arg1);
          }

 

     }

     public Object getResult(ResultGetter arg0) throws SQLException {
          this.release();
          this.result = (ResultSet)arg0.getObject();
          return this.result;

     }

     public Object valueOf(String arg0) {
          return null;
     }

     protected void finalize() throws Throwable{
          this.release();
          super.finalize();

     }

     private void release(){
        if(this.result != null) {
            try{
                this.result.close();
           } catch(SQLException ex){}

           this.result = null;
      }
     }

}

 

Hope this helps.

 

- Satish

 

 

-----Original Message-----
From: Balayn, Anna [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 02, 2005 10:17 AM
To: 'user-java@ibatis.apache.org'
Subject: Looking for sample code for processing result set returned as Ref Cursor (Oracle)

I am trying to use IBATIS with Oracle stored procedures.  We have many procedures that return Ref Cursor as an OUT parameter.  It seems that there is a way to process a Ref Cursor but I can't find any good examples or documentation on how this is done.  Can anyone help?

Thanks
Anna B

---

This e-mail message and any attachments contain confidential
information from Medco. If you are not the intended recipient, you are
hereby notified that disclosure, printing, copying, distribution, or
the taking of any action in reliance on the contents of this electronic
information is strictly prohibited. If you have received this e-mail
message in error, please immediately notify the sender by reply message
and then delete the electronic message and any attachments.