File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes calling oracle stored procedure from hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "calling oracle stored procedure from hibernate" Watch "calling oracle stored procedure from hibernate" New topic

calling oracle stored procedure from hibernate

prash boy

Joined: Jan 01, 2007
Posts: 1
I am trying to call a stored procedure through hibernate, passing a in parameter and a out parameter.

stored procedure:

create or replace PROCEDURE USER_pref_NEW (User_cv OUT SYS_REFCURSOR,userid in varchar) AS
OPEN User_cv FOR
SELECT * FROM contact where contact.USERID = userid;

hbm file:

<sql-query name="prash_test" callable="true">
<return class="">
{ call USER_pref_NEW(?,:USERID) }


private String userid;
private String firstname;
private String lastname;
private String email;

public String getEmail() {
return email;
public void setEmail(String email) { = email;
public String getFirstname() {
return firstname;
public void setFirstname(String firstname) {
this.firstname = firstname;

public String getUserid() {
return userid;
public void setUserid(String userid) {
this.userid = userid;
public String getLastname() {
return lastname;
public void setLastname(String lastname) {
this.lastname = lastname;

java class calling the stored procedure:

List list =session.getNamedQuery("prash_test").setParameter("USERID","1").list();

Query: I am passing a parameter "userid" when I call the procedure. I use this input parameter to form the "where" clause in the stored procedure query.
I am passing userid as "1" from java, but the result I am getting is "select * from contact" => where clause is being ignored.

Any idea why this could be happening?

Thanks in advance!
Vilpesh Mistry
Ranch Hand

Joined: May 27, 2003
Posts: 62

can this link be useful to you


I agree. Here's the link:
subject: calling oracle stored procedure from hibernate
jQuery in Action, 3rd edition