How to use the return parameter from Hibernate to the Oracle function?

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();
}

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
 }
});

My question is very similar to Getting the return value of a PL/SQL function via Hibernate

I have a function with some modifications internally, it Return 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 does not apply to Oracle. The recommended way to do such a thing What is it?

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

Hibernate Session provides a doWork() method, which can directly access java.sql.Connection . Then, you can create and use java.sql.CallableStatement to perform 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
 }
});