wood burning stoves 2.0*
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
Author

calling oracle stored procedure from hibernate

prash boy
Greenhorn

Joined: Jan 01, 2007
Posts: 1
Hi,
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
BEGIN
OPEN User_cv FOR
SELECT * FROM contact where contact.USERID = userid;
End;

hbm file:

<hibernate-mapping>
<sql-query name="prash_test" callable="true">
<return class="com.ni.genreg.presentation.formbeans.TestForm">
</return>
{ call USER_pref_NEW(?,:USERID) }
</sql-query>
</hibernate-mapping>

POJO:

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

public String getEmail() {
return email;
}
public void setEmail(String email) {
this.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
Hi
can this link be useful to you
http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html#querysql-limits-storedprocedures

Thanks
Vilpesh
http://www.desicrunch.com
http://www.desicrunch.co.uk


Thanks.
 
Consider Paul's rocket mass heater.
 
subject: calling oracle stored procedure from hibernate