1 Reply Latest reply: Jan 30, 2012 4:59 AM by gimbal2 RSS

    how to call oracle stored procedure?

    Joseph Hwang
      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
          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