I try to call oracle stored procedure from hibernate in JBoss as 7. My IDE is eclipse Indigo.
This is oracle stored procedure.
CREATE PROCEDURE SP_SELECT_ORA (
ID_INPUT IN VARCHAR2,
ID_OUTPUT OUT VARCHAR2,
PASSWD_OUTPUT OUT VARCHAR2,
NAME_OUTPUT OUT VARCHAR2) IS
BEGIN
SELECT EMP_ID, EMP_Passwd, EMP_Name
INTO ID_OUTPUT, PASSWD_OUTPUT, NAME_OUTPUT
FROM family
WHERE EMP_ID = ID_INPUT;
END;
And I call the SP in NamedNativeQuery like below
Members.java
@NamedNativeQueries({
@NamedNativeQuery(name = "callSelectSP", query = "CALL SP_SELECT_ORA(?,?,?,?)", resultClass = Members.class)
})
@Entity
@Table(name="family")
public class Members implements Serializable {
@Id
@Column(name = "EMP_ID")
private String ID;
@Column(name = "EMP_Passwd")
private String Passwd;
@Column(name = "EMP_Name")
private String Name;
CallStoredProcedureBean.java
@Stateless
public class CallStoredProcedureBean implements ICallStoredProcedurePort {
@PersistenceContext(unitName="MyFamily")
EntityManager em;
@Override
public Object callSP(String type, String ID) {
// TODO Auto-generated method stub
Query query = null;
Members member = null;
query = em.createNamedQuery("callSelectSP");
query.setParameter(1, ID);
////////////////////
// I don't know how to call stored procedure....
///////////////////
query.executeUpdate();
member = (Members)query.getSingleResult();
System.out.println("Flash !!");
return member;
}
I don't know how to handle out parameters in oracle SP.
Pls, advise me. Thanks in advanced !