calling stored function in hibernate

Question

I am trying to call a stored function in hibernate but I am getting invalid sql exception.

Java code ---

public Integer callSqlBlock(){
        Integer outputValue =1;
        Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
        session.doWork(new Work() {
                                        @Override
                                        public void execute(Connection conn)
                                                throws SQLException {
                                              CallableStatement stmt = conn.prepareCall("? = call test(?)");
                                              stmt.registerOutParameter(1, Types.INTEGER);
                                              stmt.setString(2, "callIndex");
                                              stmt.execute();
                                              Integer output = stmt.getInt(1);
                                              test(output);

                                           }
                        });
        Connection oracleConnection = getJavaSqlConnectionFromHibernateSession(session);
       // CallableStatement statement = oracleConnection.prepareCall(sql)

        return outputValue;
}

Test stored function in oracle ---

create or replace 
function test( str in varchar2) return number
as 
begin
if str = 'test' then
 return 1;
end if;
return 0;
end;

Please let me know where I am going wrong

Bob Jarvis - Слава Україні · Accepted Answer · 2015-06-12 13:50:03Z

1

You can either change the prepareCallline to use the JDBC escape syntax by enclosing the call in braces, as in

CallableStatement stmt = conn.prepareCall("{? = call test(?)}");

or you can use a PL/SQL block in the call, such as

CallableStatement stmt = conn.prepareCall("begin ? := test(?); end;");

The JDBC syntax is more portable, the PL/SQL block is probably some immeasurably small amount faster.

Best of luck.

edited Jun 12, 2015 at 13:50

answered Jun 12, 2015 at 10:45

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

calling stored function in hibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related