Return value of a oracle stored function

bhupeshreddy · **Joined:** Mon Jan 03, 2005 3:18 am **Posts:** 8

I have a oracle stored function which returns an integer. Something similar to

Code:

CREATE OR REPLACE FUNCTION SF_SAMPLE
  (days IN INTEGER)
  RETURN INTEGER
AS
BEGIN
RETURN days;

END SF_SAMPLE;
/

If I get the connection from the session and execute it the jdbc style, i get the return value. but the session.connection() is deprecated and i don't want to use it.

I am trying to execute the following way from my hibernate dao.

Code:

        SQLQuery query = super.getSession().createSQLQuery("{ ? = call SF_SAMPLE(:days) }");
        query.setParameter("days", _days );
        int i = query.executeUpdate();

First it would not execute unless I set the parameter for the return value too.
Second, I don't know how to get the return value back. I have looked up the documentation but couldn't find much.
I know that its something I must be doing wrong, but don't know what it is. Please help.

Thanks in Advance.

malm66 · **Joined:** Sat Nov 25, 2006 11:37 am **Posts:** 72

I am not an expert in Oracle but can't you reformulate this as a select query like SELECT SF_SAMPLE(days) AS days FROM dual and then you deal with a normal select statement which Hibernate should be just fine with?

mario.casola · **Joined:** Mon Nov 26, 2007 11:41 am **Posts:** 6

Hi,

I have the same problem.
I call a oracle stored function with <sql-delete> tag

<sql-delete callable="true">{? = call deletenode (?)}</sql-delete>

The documentation says...Hibernate always registers the first statement parameter as a numeric output parameter for the CUD operations...

but when i call the delete method....

TestHibernateUtil.getSessionFactory().getCurrentSession().beginTransaction();
......
TestHibernateUtil.getSessionFactory().getCurrentSession().delete(object);
TestHibernateUtil.getSessionFactory().getCurrentSession().getTransaction().commit();

.... I receive the following error:

Hibernate:
{? = call fdeletenode (?)}
18 dic 2007 16:22:45,613 WARN [RUBRICA] JDBCExceptionReporter - SQL Error: 17041, SQLState: null
18 dic 2007 16:22:45,613 ERROR [RUBRICA] JDBCExceptionReporter - Missing IN or OUT parameter at index:: 2.

What's wrong?

thanks in advance

plasmafire · **Posted:** Tue Dec 18, 2007 11:49 am

Code:

CallableStatement st = hibernateSession
                    .connection()
                    .prepareCall(
                            "{? = call abc(?)}");

            st.registerOutParameter(0, java.sql.Types.INTEGER);
            st.set(1, 123));
st.execute();
st.getInt(1);

I believe that is how I got the result for a record count query. If the oracle function is returning a resultset. Use code as below.

Code:

st.registerOutParameter(i++, OracleTypes.CURSOR);
st.execute();
ResultSet rs = (ResultSet) st.getObject(1);

Cheers!

mario.casola · **Joined:** Mon Nov 26, 2007 11:41 am **Posts:** 6

Hello Cheers, thanks for your response.

I know this mode, I hoped that there was another solution because the connection method is deprecated....the javadoc says:

Code:

Deprecated. To be replaced with a SPI for performing work against the connection; scheduled for removal in 4.x.

Thanks again

Mario

plasmafire · **Posted:** Tue Dec 18, 2007 1:36 pm

Well, that is the hack that I have used, maybe that'll solve the OP's problem.
I understand that it is a hack. But I am unaware of any other approach, except the storedprocedure class in Spring

jim_b_o · **Joined:** Tue Mar 21, 2006 9:25 am **Posts:** 8

The docs forget to mention the new 'check' setting that was added to make things more flexible. In this example you need to use 'check="param"'. See the JavaDoc for the ResultCheckStyle annotation equivalent for more about the alternatives now available.

Code:

<sql-delete callable="true" check="param">{? = call deletenode (?)}</sql-delete>