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