Get a return value from an Oracle Stored Procedure

gotosleep · **Joined:** Mon Mar 13, 2006 9:55 pm **Posts:** 8

Hibernate 3

Oracle 10g

I am trying to get a value from a column in a oracle stored proceudre. this column is an oracle integer. It is not mapped to any class. The stored proc returns the Oracle SID from the V$session.

How do I do this? I used a name query it does not work....

Is it possible to do this? I want to set this value as the primary key of another class I am going to do an insert on..

Thanks

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Have a look in section 16.3. "Named SQL queries", of the 3.1 ref docs. The sample code you want is prefixed by the sentence "A named SQL query may return a scalar value." The stored procedure may return anything (the rowcount is suggested), but it should issue only one top-level select.

gotosleep · **Joined:** Mon Mar 13, 2006 9:55 pm **Posts:** 8

<sql-query name="mySqlQuery">
<return-scalar column="name" type="string"/>
<return-scalar column="age" type="long"/>
SELECT p.NAME AS name,
p.AGE AS age,
FROM PERSON p WHERE p.NAME LIKE 'Hiber%'
</sql-query>

"SELECT p.NAME AS name,
p.AGE AS age,
FROM PERSON p WHERE p.NAME LIKE 'Hiber%'"

What do I replace this with? call procedurename()..?

Or the whole stored procedure code from the database?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Code:

<sql-query name="mySqlQuery" callable="true">
  <return-scalar column="NameOfColumnReturned" type="integer"/>
    { ? = call stpStoredProc() }
</sql-query>

You can pass in parameters in the normal way. Note that (afaik) you must return something, by convention the number of rows in the result set, but you can just return a constant 1 if you like. So your stored proc could look like this (SQLServer dialect, modify as appropriate for your DB):

Code:

create proc stStoredProc as
begin
  select value as NameOfColumnReturned
  from table

  return 1
end

gotosleep · **Joined:** Mon Mar 13, 2006 9:55 pm **Posts:** 8

Thanks... I seem to be more than half way there now I get this exception..

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1674)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:147)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at com.monsanto.hibernatedataservices.HibernateQuery.list(HibernateQuery.java:52)
... 24 more
Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

here is my calling code..

ObjectPersistentStoreQuery query = conn.createNamedQuery("nativeGetOracleSID");
List results = query.list();

jt_1000 · **Posted:** Tue Mar 14, 2006 11:45 am

Here is a complete Oracle example:
Java Code:

Code:

       Query q=sess.getNamedQuery("getCount");
       q.setDouble(0,2);
        boolean doUnique=false;
        if(doUnique) //using the "unique" feature
        {
            Object obj=q.uniqueResult();
            if(obj instanceof Long)
            {
                System.out.println("answer:" + ((Long)obj).doubleValue());
            }
        }else
        { //loop through results set.
            Long dbl=null;
            Iterator itr=q.list().iterator();
            while(itr!=null&&itr.hasNext())
            {
                 dbl=(Long) itr.next();
                System.out.println("dbl=" + dbl);
            }
        }

Mapping declaration:

Code:

<hibernate-mapping>
    <class> 
...
    </class>

    <sql-query name="getCount" callable= "true">
        <return-scalar column="cnt" type="long"/>
        { ? = call getCount(?) }
    </sql-query>
</hibernate-mapping>

Oracle Function:

Code:

create or replace function getCount(arg number)
    RETURN SYS_REFCURSOR AS st_cursor SYS_REFCURSOR;
    mycnt number:=0;
    BEGIN

    OPEN st_cursor FOR

      select (count(*)* arg) as cnt from user_tables;

    RETURN  st_cursor;

end;
/

gotosleep · **Joined:** Mon Mar 13, 2006 9:55 pm **Posts:** 8

I do not see why you are doing this...

q.setDouble(0,2);

jt_1000 · **Posted:** Tue Mar 14, 2006 11:55 am

gotosleep wrote:

I do not see why you are doing this...

q.setDouble(0,2);

it is just an example whereby I am passing an ARG (which is 2) to the function - the arg is then multiplied by the count...

gotosleep · **Joined:** Mon Mar 13, 2006 9:55 pm **Posts:** 8

When does Oracle close the cursor???

jt_1000 · **Posted:** Tue Mar 14, 2006 12:43 pm

Actually not sure at this point.. I just noticed that querying v$open_cursor the cursor is open and stays open until my program exists - it doesn't even close on session.close().

jt_1000 · **Posted:** Tue Mar 14, 2006 2:07 pm

After looking over some other code, I found out what I needed to do to get the sample to close the cursor:

Apparently it needs surrounding query with transaction and cursor then closed upon commiting...

Code:

Transaction trx=sess.beginTransaction();
Query q=sess.getNamedQuery("getPeople");
q.setString (0,"John");
   Person p=null;
ScrollableResults sr = q.scroll();

   for(int jj=0;(sr.next()); jj++)
   {     
      p=(Person) sr.get(0);
      System.out.println("p.PERSON_ID=" + p.getPERSON_ID());
      System.out.println("p.FIRST_NAME=" + p.getFIRST_NAME());
      System.out.println("p.LAST_NAME=" + p.getLAST_NAME());
   }
sr.close();
trx.commit();

Even my original sample will close cursors, when you surround the Query in a transaction.

I think this is related to David's comments:
http://forum.hibernate.org/viewtopic.php?t=956660&highlight=

but I am not sure. Now I am really curious as to why a transaction w/ commit is needed for a Query, aside from the obvious mechanical reason shown above.

Also - for fyi :

I tried closing the ScrollableResults directly and that was not sufficient to close the cursor.

I also tried Hibernate.close(itr);
got an exception (IllegalArgumentException: not a Hibernate iterator)... but I couldn't create a hibernate iterator, since that is created from (Query q) q.iterate(); and that throws an UnsupportedOperationException "SQL queries do not currently support iteration". So apparently that "q.iterator()" only works with HQL queries; and not named Queries ...

max · **Posted:** Tue Mar 14, 2006 4:31 pm

this is apparently how oracle works - you would need to do the same when using jdbc.

e.g. closing/committing your connection/tx.

jt_1000 · **Posted:** Tue Mar 14, 2006 5:18 pm

Thanks Max - you are right!

jt_1000 · **Posted:** Tue Mar 14, 2006 6:23 pm

One other thing: I would have expected the session.close() and the session.disconnect() to have released the resources (as stated in the API docs). However, they didn't. And when closing an Oracle jdbc-connection it did release the resources.

max · **Posted:** Wed Mar 15, 2006 2:01 am

are you using a connection pool ? if yes, then the connection is not actually closed..this is another very good reason for always using tx's