-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: calling oracle procedure using hibernate
PostPosted: Tue Jan 02, 2007 12:08 pm 
Newbie

Joined: Fri Dec 29, 2006 3:14 am
Posts: 5
Hi,

I want to call a oracle procedure using hibernate. I just want my procedure to do some processing and has no out parameter. But as I have gathered from the docs - A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10

I have made my procedure with an out parameter satisfing the above requirement.

Procedure:

CREATE OR REPLACE
PROCEDURE SPC_USER_DETAILS (User_cv OUT SYS_REFCURSOR) AS
v_User_id Number(38);
v_FIRST_NAME VARCHAR2(255);
v_LAST_NAME VARCHAR2(255);
v_email VARCHAR2(255);

BEGIN
OPEN User_cv FOR
SELECT * FROM contact;
loop
fetch User_cv into v_User_id, v_FIRST_NAME,v_LAST_NAME,v_email;
exit when User_cv%notFound;

insert into dummy_contact values (v_User_id, v_FIRST_NAME,v_LAST_NAME,v_email);
end loop;
close User_cv;
End;

Java code calling the procedure.

tx = session.beginTransaction();
session.getNamedQuery("prash_test");

hibernate.cfg.xml
<mapping resource="hibernate/test.hbm.xml" />

test.hbm.xml
<hibernate-mapping>
<sql-query name="prash_test" callable="true">
<return class="com.ni.genreg.presentation.formbeans.TestForm">
</return>
{ call SPC_USER_DETAILS(?) }
</sql-query>
</hibernate-mapping>

On running my java class, that calls the procedure, I dont get any errors. But the changes are not getting reflected in the database. I have tested the oracle procedure, its in working fine.

Any idea what I could be doing wrong in this approach?

Thanks in advance.b]


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 02, 2007 5:28 pm 
Newbie

Joined: Wed Aug 03, 2005 2:13 am
Posts: 16
Hi there,

That Hibernate functionality really is intended for using Stored Procedures for querying, which is why it requires the OUT parameter. (And possibly why it's not doing a commit?)

If you just want to call a stored procedure to do some work, there's no real benefit in having a "hibernate" way to do it -- use a pure JDBC CallableStatement.

cheers,
D.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.