CODEWITHSUNDEEP
springoracle-databasehibernate
Abhi
Abhi

Reputation: 384

calling stored function in hibernate

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

Upvotes: 0

Views: 385

Answers (1)

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

Reputation: 50017

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.

Upvotes: 1

Related Questions