0 Replies Latest reply: Feb 8, 2012 7:43 AM by Joseph Hwang RSS

    how to call oracle stored procedure in jpa?

    Joseph Hwang Novice

      I made the oracle stored procedure,

       

      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;

       

      I try to call above oracle sp in JPA. These are my codes.

       

      @NamedNativeQueries({

      @NamedNativeQuery(name = "callSelectSP", query = "exec SP_SELECT_ORA(?, :id_output, :passwd_output, :name_output)", 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;
      ...........
      ...........
      }
      ==============

       

      query = em.createNamedQuery("callSelectSP");

      query.setParameter(1, ID);

       

      String id_output = null;
      String passwd_output = null;
      String name_output = null;

       

      query.setParameter("id_output", id_output);
      query.setParameter("passwd_output", passwd_output);
      query.setParameter("name_output",name_output);
       

      query.executeUpdate();

       

      On console error is thrown.

       

      19:59:25,160 INFO [stdout] (http--127.0.0.1-8080-1) Hibernate: exec SP_SELECT_ORA( ?, ?, ?, ?)

      19:59:25,192 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--127.0.0.1-8080-1) SQL Error: 900, SQLState: 42000

      19:59:25,192 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--127.0.0.1-8080-1) ORA-00900: Invalid SQL statement

       

      Pls, inform me your advice. Thanks in advance.

       

      Best regards