Bayes Blog

I came accross the problem where hibernate is restricted in the way it can access an oracles stored procedure that returns a reference cursor. In hibernate the reference cursor should be the first parameter in the stored procedure. Now working with stored procedures writted differently you could always just extend the “org.springframework.jdbc.object.StoredProcedure” abstract class, if you are using spring that is. 

Since my blog is all about the samples here it is:

package com.bayestech.sampleprocedure; 

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types; 

import javax.sql.DataSource; 

import oracle.jdbc.driver.OracleTypes; 

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure; 

import uk.co.pruhealth.admin.communication.notificationengine.PolicyLinks; 

/**
 * This class reads a stored procedure named 'sample_procedure' which has the following usag:
 * call sample_procedure (? <= p_Number_Input, ? <= p_String_Input, ? <= p_Date, ? => Pr_Returned_Cursor, ? => Pr_Error_Code, ? => Pr_Error_Message)
 *
 *
 * @author Kevin Bayes
 *
 */
public class SampleStoredProcedure extends StoredProcedure {

 /** Name of procedure in database. */
 public static final String PROC_NAME = "sample_procedure";�
 public GetPolicyLinksStoredProcedure(DataSource ds) {
  super(ds, PROC_NAME);

  declareParameter(new SqlParameter("p_Number_Input", Types.NUMERIC));
  declareParameter(new SqlParameter("p_String_Input", Types.VARCHAR));
  declareParameter(new SqlParameter("p_Date", Types.DATE));
  declareParameter(new SqlOutParameter("Pr_Returned_Cursor", OracleTypes.CURSOR, new RowMapper() {
   public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    SampleReturnClass sampleClass = new SampleReturnClass();
    policyLink.setColumn1Name(rs.getString("COLUMN_1_NAME"));
    policyLink.setColumn2Name(rs.getString("COLUMN_2_NAME"));
    policyLink.setColumn3Name(rs.getString("COLUMN_3_NAME"));
    return sampleClass;
   }
  }));
  declareParameter(new SqlOutParameter("Pr_Error_Code", Types.NUMERIC));
  declareParameter(new SqlOutParameter("Pr_Error_Message", Types.VARCHAR));

     compile();
 }   

}

Once you have created the above class you need to execute it by setting the datasource and passing in the desired parameters like so:

public Collection getList(DataSource dataSource, Long inputNumber, String inputString, Date effectiveDate) {

	SampleStoredProcedure sampleStoredProcedure = new SampleStoredProcedure(dataSource);
	Map parameters = new HashMap();
	parameters.put("p_Number_Input", targetEntityNumber);
	parameters.put("p_String_Input", null);
	parameters.put("p_Date", effectiveDate);

       	Map m = SampleStoredProcedure.execute(parameters);
       	return (Collection) m.get("Pr_Returned_Cursor");
}

And that is all there is to it!