[jboss-user] [EJB3] - how to call oracle stored procedure?

[Joseph Hwang]

Joseph Hwang [https://community.jboss.org/people/aupres] created the discussion

"how to call oracle stored procedure?"

To view the discussion, visit: https://community.jboss.org/message/649396#649396

--------------------------------------------------------------
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 !
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/649396#649396]

Start a new discussion in EJB3 at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2029]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jboss-user/attachments/20120130/dfb1143d/attachment.html