EclipseLink/Examples/JPA/PLSQLStoredFunction

EclipseLink has support for calling Oracle PLSQL stored functions that return complex PLSQL data-types such as RECORD types and TABLE types. PLSQL types are not supported by Oracle JDBC, so these types must be translated to Oracle OBJECT types and VARRAY types. OBJECT types are returned as java.sql.Struct and VARRAY as java.sql.Array types in JDBC.

To call a stored function using PLSQL types the PLSQLStoredFunctionCall or @NamedPLSQLStoredFunctionQuery must be used. PLSQLStoredProcedureCall and @NamedPLSQLStoredProcedureQuery also exist for stored procedures. For regular stored functions and procedure that do not return complex PLSQL types the regular StoredFunctionCall, StoredProcedureCall and @NamedStoredFunctionQuery, @NamedStoredProcedureQuery can be used.

To call PLSQL stored functions or procedures mirror OBJECT/VARRAY types must be defined for the RECORD/TABLE types.

Oracle stored function returning PLSQL record type

CREATE PROCEDURE EMP_READ_ALL (
	RESULT_CURSOR OUT CURSOR_TYPE.ANY_CURSOR) AS
BEGIN 
OPEN RESULT_CURSOR FOR SELECT e.*, s.* FROM EMPLOYEE e, SALARY s WHERE e.EMP_ID = s.EMP_ID; 
END;

Using JpaEntityManager createQuery() API to execute a PLSQL stored function

import javax.persistence.Query;
import org.eclipse.persistence.platform.database.orcle.plsql.PLSQLStoredFunctionCall;
import org.eclipse.persistence.queries.ReadAllQuery;
 
DataReadQuery databaseQuery = new DataReadQuery();
PLSQLStoredFunctionCall call = new PLSQLStoredFunctionCall ();
call.setProcedureName("GET_EMP");
call.
databaseQuery.setCall(call);
 
Query query = ((JpaEntityManager)entityManager.getDelegate()).createQuery(databaseQuery);
Employee result = (Employee)query.getSingleResult();

Using @NamedPLSQLStoredFunctionQuery to define a stored function

@NamedPLSQLStoredFunctionQuery(name="getEmployee", procedureName="GET_EMP",
    returnParameter=@PLSQLParameter(name="RESULT", databaseType="EMP_REC", javaType=Employee.class))
@Embeddable
@Struct(fields={"F_NAME", "L_NAME", "SALARY"})
@PLSQLRecord(name="EMP_REC")
public class Employee {
 @Column(name="F_NAME")
 private String firstName;
 @Column(name="L_NAME")
 private String lastName;
 @Column(name="SALARY")
 private BigDecimal salary;
 ...
}

Using named query

Query query = entityManager.createNamedQuery("getEmployee");
Employee result = (Employee)query.getSingleResult();