Named query for stored procedure

[KV Zensan]

How do I write a named query for calling a stored procedure? The stored procedure has an OUT param which is of type VARCHAR2.

I did something like this..
<blockquote>code:

---

<pre name="code" class="core"><sql-query name="updateStatus" callable="true">
<return-scalar column="out_test" type="string"/>
{call TIUSER.TestProc(?) }
</sql-query></pre>

---

</blockquote>

to call the below given procedure

<blockquote>code:

---

<pre name="code" class="core">CREATE OR REPLACE PROCEDURE TIUSER.TestProc (out_test OUT VARCHAR2) as
BEGIN
SELECT name into out_test
FROM user_master where user_id='104';
END; </pre>

---

</blockquote>

Is there anything wrong with this code? When i execute im getting following error message
<blockquote>code:

---

<pre name="code" class="core"> Jul 15, 2008 1:33:14 PM org.hibernate.util.JDBCExceptionReporter
WARNING: SQL Error: 6550, SQLState: 65000
Jul 15, 2008 1:33:14 PM org.hibernate.util.JDBCExceptionReporter
SEVERE: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TESTPROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2216)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at com.test.UnitTest.main(UnitTest.java:37) </pre>

---

</blockquote>



Please help me with this.
Thanks in advance,

[Mark Spritzler]

"zensan"

Please click on the My Profile link above and change your display name to meet the JavaRanch naming policy of using your real first and real last names.

Thank you for your understanding.

Mark Sprizler

[Mark Spritzler]

In Hibernate there are some caveats to stored procedures.

1. Only one out parameter and it must be the first parameter
2. The out parameter must be a refcursor. No other types will work.

If you have to work with a stored procedure that returns a varchar, you can call session.getConnection() and work directly with the jdbc connection. Although in this case you can't use a named query.

Mark

[KV Zensan]

Thanks for the reply.

I dont want to use jdbc connection. Is there any other way to achieve this? Actually the value returned by the stored procedure is based on some calculation. If i put that in a ref_cursor like given below, will i be able to get the local variable 'ln_out_parameter'??
<blockquote>code:

---

<pre name="code" class="core">CREATE OR REPLACE PROCEDURE TIUSER.TestProc (out_refcursor OUT SYS_REFCURSOR) as
BEGIN
ln_out_parameter := ln_val1|| ln_val2;
OPEN out_refcursor FOR SELECT ln_out_parameter FROM dual;
END; </pre>

---

</blockquote>

[Mark Spritzler]

Originally posted by Zensan:
Thanks for the reply.

I dont want to use jdbc connection. Is there any other way to achieve this? Actually the value returned by the stored procedure is based on some calculation. If i put that in a ref_cursor like given below, will i be able to get the local variable 'ln_out_parameter'??
<blockquote><font size="1" face="Verdana, Arial">code:</font><hr><pre name="code" class="core"><font size="2">CREATE OR REPLACE PROCEDURE TIUSER.TestProc (out_refcursor OUT SYS_REFCURSOR) as
BEGIN
ln_out_parameter := ln_val1|| ln_val2;
OPEN out_refcursor FOR SELECT ln_out_parameter FROM dual;
END; </font></pre><hr></blockquote>

No, basically, you have to have a mapped object to a table, and that ref cursor match that table structure.

This is the only way Hibernate can call a stored procedure. It is because of a certain jdbc driver that didn't implement the JDBC standard completely, at the time, which most people are still using.

Mark

[KV Zensan]

Thanks again!

It is not necessary that ref cursor should match a table structure. I did the same way i mentioned earlier, fetching the local variable in the cursor. And believe me it worked!!

All that was required to retrieve the value in java is...

[B]<resultset name="spResult"/>[/B]
<sql-query name="spTestProc" callable="true" resultset-ref="spResult">
    {call TIUSER.TestProc(?) } 
</sql-query>  

[Mark Spritzler]

Originally posted by KV Zensan:
Thanks again!

It is not necessary that ref cursor should match a table structure. I did the same way i mentioned earlier, fetching the local variable in the cursor. And believe me it worked!!

All that was required to retrieve the value in java is...

[B]<resultset name="spResult"/>[/B]
<sql-query name="spTestProc" callable="true" resultset-ref="spResult">
    {call TIUSER.TestProc(?) } 
</sql-query>  

So, post your Java code too.

Mark