• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Junilu Lacar
  • Martin Vashko
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Scott Selikoff
  • salvin francis
  • Piet Souris

Named query for stored procedure

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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,
 
ranger
Posts: 17344
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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
ranger
Posts: 17344
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
ranger
Posts: 17344
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
Mark Spritzler
ranger
Posts: 17344
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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...



So, post your Java code too.

Mark
 
Don't count your weasels before they've popped. And now for a mulberry bush related tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!