Eclipselink/JPA: how to call a pl/sql function that returns an object type using '@NamedPLSQLStoredFunctionQuery'

Question

Is it possible to call a pl/sql function from eclipselink that returns an object type?

We built a testcase that correctly selects an object type from the backing table. So our entity seems to be properly defined. But when calling the pl/sql-function the testcase failes with a NoResultException.

In the eclipselink-log the statement looks ok, but the binding of the return parameter is empty. What are we missing?

[EL Finest]: connection: 2017-03-30 13:51:32.631--ServerSession(1014328909)--Connection(1807648168)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].
[EL Fine]: sql: 2017-03-30 13:51:32.632--ServerSession(1014328909)--Connection(1807648168)--Thread(Thread[main,5,main])--
DECLARE
  RESULTTARGET t_emp;
BEGIN
  RESULTTARGET := GET_EMP();
END;
  bind => []
[EL Finest]: connection: 2017-03-30 13:51:32.688--ServerSession(1014328909)--Connection(1807648168)--Thread(Thread[main,5,main])--Connection released to connection pool [default].

When we call a pl/sql function returning a varchar2 it runs fine, the binding is ok as well. Maybe we just did not find the correct databaseType in the parameter definition of @NamedPLSQLStoredFunctionQuery?

@Before
public void initEM() {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory( "test" );
    em = emf.createEntityManager();
}

@Test // SUCCEEDS
public void testFind() {
    EmpEntity e = em.find( EmpEntity.class, 2 );
    assertNotNull( e );
}

@Test // FAILES: javax.persistence.NoResultException: getSingleResult() did not retrieve any entities
public void testNamedFunctionCall() {
    EmpEntity e = em.createNamedQuery( "callEmpFunction", EmpEntity.class ).getSingleResult();
    assertNotNull( e );
}

@After
public void closeEM() {
    em.close();
}

The object types, the backing table and the pl/sql-function are defined as

create or replace type T_DEPT as object
( deptno   number
, deptname varchar2(40)
, loc      varchar2(40)
);

create or replace type T_ADDRESS as object
( street varchar2(40)
, city   varchar2(40)
);

create or replace type T_EMP as object
( address t_address
, empno   number
, ename   varchar2(40)
, dept    t_dept
);

create table EMP_OBJECTS
( obj_id  number not null primary key
, emp     t_emp  not null
);

create or replace function GET_EMP
return t_emp
is
    v_emp    t_emp;
begin
    select emp
    into   v_emp
    from emp_objects
    where obj_id = 2; 

    return v_emp;
end;
/

declare
    v_dept t_dept    := t_dept( 10, 'sales', 'beach' );
    v_addr t_address := t_address( 'here', 'there' );
    v_emp  t_emp     := t_emp( v_addr, 1, 'scott', v_dept );
begin
    insert into emp_objects
    ( obj_id, emp )
    values
    ( 2, v_emp );
    commit;
end;

Our Java entity and the embeddable classes are

@Entity
@Table(name="EMP_OBJECTS")
@NamedPLSQLStoredFunctionQuery( name = "callEmpFunction"
   , functionName = "GET_EMP"
   , returnParameter = @PLSQLParameter(name = "result", databaseType="t_emp" )
   )
public class EmpEntity {    
    @Id
    private int obj_id;   
    @Structure
    private Emp emp;

    ... getter and setter ...
}

@Embeddable
@Struct(name = "T_EMP", fields = { "addr", "empno", "name", "dept" } )
public class Emp {
    @Structure
    private Address addr;
    private int     empno;
    private String  name;
    @Structure
    private Dept    dept;
}

@Embeddable
@Struct(name="T_ADDRESS", fields={"street","city"})
public class Address {   
    private String street;    
    private String city;
}

@Embeddable
@Struct(name="T_DEPT", fields={"deptno","deptname","loc"})
public class Dept {    
    private int deptno;    
    private String deptname;    
    private String loc;
}

We are using eclipselink 2.6.4 and an oracle 11.2 database.

Answer 1

Sorry for answering my own question. After quite some trial and error we found two solutions we would like to share.

First: Create the function call programmatically and set the result type to OracleObjectType. Mind the uppercase names of the type and procedurename. This seem to be important.

@Test
public void testFunctionCall() {

    OracleObjectType result = new OracleObjectType();
    result.setJavaType( Emp.class );
    result.setTypeName( "T_EMP" );

    DataReadQuery databaseQuery = new DataReadQuery();
    databaseQuery.setResultType(DataReadQuery.VALUE);

    PLSQLStoredFunctionCall call = new PLSQLStoredFunctionCall(result);
    call.setProcedureName("GET_EMP");
    databaseQuery.setCall(call);

    Query query = ((JpaEntityManager)em.getDelegate()).createQuery(databaseQuery);
    Emp e = (Emp)query.getSingleResult();

    assertNotNull( e );        
}

Second: It seems to be necessary to annotate the embeddable classes with both @Struct and @OracleObject whenever you want to use it in procedure or function calls. With the following change our formerly failing testcase succeeds.

@Embeddable
@Struct( name = "T_EMP", fields = { "addr", "empno", "name", "dept" } )
@OracleObject( name = "T_EMP", javaType = Emp.class, fields = {} )
public class Emp {
    @Structure
    private Address addr;
    private int     empno;
    private String  name;
    @Structure
    private Dept    dept;
}

@Embeddable
@Struct( name="T_ADDRESS", fields={"street","city"} )
@OracleObject( name = "T_ADDRESS", javaType = Address.class, fields = {} )
public class Address {    
    private String street;    
    private String city;
}

@Embeddable
@Struct( name = "T_DEPT", fields = { "deptno", "deptname", "loc" } )
@OracleObject( name = "T_DEPT", javaType = Dept.class, fields = {} )
public class Dept {
    private int    deptno;
    private String deptname;
    private String loc;
}

Maybe someone with deeper knowledge on eclipselink can comment on the redundancy of using both @Struct and @OracleObject?