I have a function with some modifications inside, 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();
}
Unfortunate The thing is, this does not apply to Oracle. What is the recommended way to do such a thing?
Is there a way to extract declared variables from my statement?
doWork() method, which can be accessed directly 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
}
}); 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
}
});