I am using an oracle db 10g, hibernate 3.2.5

Here is my store proc:

CREATE OR REPLACE PROCEDURE
get_contact_by_workPhone_sp (
contact_ref_cur OUT sys_refcursor,
in_work_phone IN VARCHAR2
)
IS
BEGIN
OPEN contact_ref_cur
FOR
SELECT *
FROM contact contact
WHERE contact.phone_work like '%' || in_work_phone || '%';
END;

Here is what I have in my hibernate mapping file:

<sql-query name="getContactsByWorkPhone_sp">
<return alias="contact" class="vo.Contact"/>
{ ? = call get_contact_by_workPhone_sp(:workPhone) }
</sql-query>

I have also tried the following:

<sql-query name="getContactsByWorkPhone_sp">
<return alias="contact" class="vo.Contact"/>
{ call get_contact_by_workPhone_sp(?, :workPhone) }
</sql-query>

My java code:


Query q = getSession().getNamedQuery("getContactsByWorkPhone_sp");
List results = q.setParameter("workPhone", "5551234")
.list();


Here is the exception I get:
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
.......

org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)


Can someone please give me a clear example on how I can call this stored proc?
The hibernate documentation is not very helpful, they do not give a clear example on how to call a stored procedure in java.
Do I have to bind the out parameter? I thought Hibernate would do that for me, since list() returns a list of "contact" objects for me.

Thanks
Shirley

yes, put something like {?=your_sp(?)}
Also, don't set parameters by name, just by position.

Ok So I changed my procedure call to:

<sql-query name="getContactsByWorkPhone_sp">
<return alias="contact" class="ca.otn.rossco.vo.Contact"/>
{ ? = call get_contact_by_workPhone_sp(?) }
</sql-query>

as suggested in my mapping file.

I did the following java call:


Query q = getSession().getNamedQuery("getContactsByWorkPhone_sp");
List results = q.setParameter(0,"5551234").list();

and I got the following error:

16:39:21,800 DEBUG (http-8443-1) [JDBCExceptionReporter] could not execute query [{ ? = call get_contact_by_workPhone_sp(?) }]
java.sql.SQLException: ORA-01008: not all variables bound

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)


Do I need to bind the resultset? I'm returning an oracle ref cursor?

try with q.setParameter(1,"5551234")

[quote="gonzao_diaz"]try with q.setParameter(1,"5551234")[/quote]

I just tried that and received the following error:

13:44:11,834 ERROR (http-8443-1) [PreAction] Unexpected application error - Remember that ordinal parameters are 1-based!
java.lang.IndexOutOfBoundsException: Remember that ordinal parameters are 1-based!
at org.hibernate.engine.query.ParameterMetadata.getOrdinalParameterDescriptor(ParameterMetadata.java:55)
at org.hibernate.engine.query.ParameterMetadata.getOrdinalParameterExpectedType(ParameterMetadata.java:61)
at org.hibernate.impl.AbstractQueryImpl.determineType(AbstractQueryImpl.java:397)
at org.hibernate.impl.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:369)
at
........

Am I suppose to set the out parameter? If so, How do I go about doing that?

I don't want to use the native JDBC call..then I will have to massage the resultset myself to return a list of my "contact" VO object.
I"m beginning to think Hibernate cannot do what is says it can do with stored procedures.

I believe Hibernate is getting confused by the fact that there's 2 ?'s

try just:


and then

q.setParameter(1,"5551234")

I have tried that too .
Using q.setParameter(1,"5551234") gives me that ordinal parameter error mentioned above and using
q.setParameter(0,"5551234") gives me this error:

14:42:24,014 DEBUG (http-8443-1) [JDBCExceptionReporter] could not execute query [{ call get_contact_by_workPhone_sp(?) }]
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_CONTACT_BY_WORKPHONE_SP'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
......

has anybody been successful executing a stored procedure in Hibernate without having to use the JDBC Connection method?

I did it, but it was like 3 years ago and I've forgotten how I did it. I do recall that it was painful.

From the docs:



Perhaps you need to do something like this:

then:

q.setParameter("pnum","5551234")

I have done that as well..if you look at my first post and all my other subsequent posts, I have tried various ways to define the store procedure call in the mapping file.

What the hibernate doc does not show you is how to call the stored procedure in java. How do we set the out parameter? Because that seems to be my biggest problem. Oracle is expecting all parameters to be bound. One would assume that hibernate's internal code did that for you..but apparently not. How does one bind the out parameter in java?

I have even tried to rewrite the stored procedure as a function..but still getting same error messages about the out parameter not being bound.