This discussion is archived
1 Reply Latest reply: Jan 30, 2012 2:59 AM by gimbal2 RSS

how to call oracle stored procedure?

JosephHwang Newbie
Currently Being Moderated
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 !
  • 1. Re: how to call oracle stored procedure?
    gimbal2 Guru
    Currently Being Moderated
    My IDE is eclipse Indigo.
    Can you explain to yourself why this would be relevant information? I'm not trying to be an ahole, you should realize that whatever IDE you uses has no impact at all.

    Is there a specific reason why you must do this through JPA? Even if I have an application that uses ORM, I still use a regular JDBC CallableStatement to invoke procedures. Far less code required than when using Hibernate/JPA.

    http://docs.oracle.com/javase/1.3/docs/guide/jdbc/getstart/callablestatement.html

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points