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 !