0 Replies Latest reply: Jan 30, 2012 5:05 AM by Joseph Hwang RSS

how to call oracle stored procedure?

Joseph Hwang Novice

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 !