title: Eclipselink/JPA: how to call a pl/sql function that returns an object type using '@NamedPLSQLStoredFunctionQuery'
body: <p>Is it possible to call a pl/sql function from eclipselink that returns an object type? </p>
  
  <p>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 <code>NoResultException</code>.</p>
  
  <p>In the eclipselink-log the statement looks ok, but the binding of the return parameter is empty. What are we missing?</p>
  
  <pre><code>[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 =&gt; []
  [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].
  </code></pre>
  
  <p>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 <code>databaseType</code> in the parameter definition of <code>@NamedPLSQLStoredFunctionQuery</code>?</p>
  
  <pre><code>@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();
  }
  </code></pre>
  
  <p>The object types, the backing table and the pl/sql-function are defined as</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Our Java entity and the embeddable classes are</p>
  
  <pre><code>@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;
  }
  </code></pre>
  
  <p>We are using eclipselink 2.6.4 and an oracle 11.2 database.</p>