How to call a Oracle function from hibernate with return parameter?

Question

My question is very much like http://stackoverflow.com/questions/1068974/getting-the-return-value-of-a-pl-sql-function-via-hibernate

I have a function which does some modifications internally and it returns a value.

The original idea was to do something like this:

protected Integer checkXXX(Long id, Long transId)
  throws Exception {
 final String sql = "SELECT MYSCHEMA.MYFUNC(" + id + ", "
   + transId + ") FROM DUAL";
 final BigDecimal nr = (BigDecimal) this.getHibernateTemplate()
   .getSessionFactory().getCurrentSession().createSQLQuery(sql)
   .uniqueResult();
 return nr.intValue();
}

Unfortunately this doesn't work with Oracle. What is the recommended way to do something like this?

Is there a way to extract declared variables from within my statement?

Answer 1

Hibernate Session provides a doWork() method that gives you direct access to java.sql.Connection. You can then create and use java.sql.CallableStatement to execute your function:

session.doWork(new Work() {
  public void execute(Connection connection) throws SQLException {
    CallableStatement call = connection.prepareCall("{ ? = call MYSCHEMA.MYFUNC(?,?) }");
    call.registerOutParameter( 1, Types.INTEGER ); // or whatever it is
    call.setLong(2, id);
    call.setLong(3, transId);
    call.execute();
    int result = call.getInt(1); // propagate this back to enclosing class
  }
});

Answer 2

Yes, you do need to use an out parameter. If you use the doWork() method, you'd do something like this:

session.doWork(new Work() {
   public void execute(Connection conn) {
      CallableStatement stmt = conn.prepareCall("? = call <some function name>(?)");
      stmt.registerOutParameter(1, OracleTypes.INTEGER);
      stmt.setInt(2, <some value>);
      stmt.execute();
      Integer outputValue = stmt.getInt(1);
      // And then you'd do something with this outputValue
   }
});

Answer 3

public static void getThroHibConnTest() throws Exception {
    logger.debug("UsersActiion.getThroHibConnTest() | BEG ");
    Transaction tx = null;
    Connection conn = null;
    CallableStatement cs = null;
    Session session = HibernateUtil.getInstance().getCurrentSession();
    try {
        tx = session.beginTransaction();
        conn = session.connection();

        System.out.println("Connection = "+conn);
        if (cs == null)
        {
            cs = 
                conn.prepareCall("{ ?=call P_TEST.FN_GETSUM(?,?) }");
        }
        cs.clearParameters();
        cs.registerOutParameter(1,OracleTypes.INTEGER);
        cs.setInt(2,1);
        cs.setInt(3,2);
        cs.execute();
        int retInt=cs.getInt(1);
        tx.commit();            
    }catch (Exception ex) {  
        logger.error("UsersActiion.getThroHibConnTest() | ERROR | " , ex);  
        if (tx != null && tx.isActive()) {
            try {
                // Second try catch as the rollback could fail as well
                tx.rollback();
            } catch (HibernateException e1) {
                logger.debug("Error rolling back transaction");
            }
            // throw again the first exception
            throw ex;
        }
    }finally{
        try {
            if (cs != null) {
                cs.close();
                cs = null;
            }
            if(conn!=null)conn.close();

        } catch (Exception ex){;}
    }
    logger.debug("UsersActiion.getThroHibConnTest() | END ");
}