1 Reply Latest reply on Jun 1, 2017 3:36 PM by Cdelahun-Oracle

    Table object types in Eclipselink

    1029762

      Can someone help me figure out on how to pass table object type as IN parameter to a stored procedure using EclipseLink JPA?

       

      Stored procedure:

      TYPE EMP_REC IS RECORD (EMP_ID NUMBER(10), NAME VARCHAR2(30), ADDRESS VARCHAR2(50));

      TYPE EMP_TABLE IS TABLE of EMP_REC;

      PROCEDURE PLSQL_COLL_EMP_PROC(P_EMP IN EMP_TABLE, P_STATUS OUT VARCHAR2);

       

      Also I have defined a VARRAY and Object Type:

      create or replace TYPE EMP_TYPE FORCE as OBJECT(EMP_ID   NUMBER,NAME VARCHAR2(20),ADDRESS   VARCHAR2(20),CONSTRUCTOR FUNCTION EMP_TYPERETURN SELF AS RESULT);

      create or replace TYPE emp_varray IS VARRAY(50) of EMP_TYPE;

       

       

      This is how I have defined the Stored Procedure using @NamedPLSQLStoredProcedureQuery:

       

      @Entity
      @NamedPLSQLStoredProcedureQueries( {  
      @NamedPLSQLStoredProcedureQuery(name = "Employee.PLSQL_COLL_EMP_PROC", procedureName = "PKG.PLSQL_COLL_EMP_PROC",
        parameters
      = {
         @PLSQLParameter(name = "P_EMP", direction = Direction.IN, databaseType = "PKG.EMP_TABLE"),
        
      @PLSQLParameter(name = "P_STATUS", direction = Direction.OUT, databaseType = "VARCHAR_TYPE")
        
      }
        
      )
      })

      @Struct(name="EMP_TYPE", fields={"EMP_ID", "NAME", "ADDRESS"})
      @PLSQLTable(
        name
      ="PKG.FORMS_DTLS_COLL",
        compatibleType
      ="EMP_VARRAY",

        nestedType="PKG.EMP_REC"
      )
      public class Employee implements Serializable {
      ...
      }

       

       

      Invoking the stored procedure as follows:

      public Object[] createEmployeeTable() throws ServiceException{

              Query query = em.createNamedQuery("Employee.PLSQL_COLL_EMP_PROC");

              Employee employee = new Employee();

              Employee[] empArr = new Employee[50];

              employee.setId(BigDecimal.valueOf(10));

              employee.setName("Tom");

              employee.setAddress("Burlington"); 

              empArr[1] = employee;

              empList.add(employee);

              query.setParameter("P_EMP", empArr);

              Object[] result = (Object[])query.getSingleResult();

              return result;

          }

       

       

      I get the below error on execution:

       

      [EL Warning]: 2017-05-19 19:41:47.985--UnitOfWork(1959258417)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.DatabaseException

      Internal Exception: java.sql.SQLException: ORA-06531: Reference to uninitialized collection

      ORA-06512: at line 21

      ORA-06512: at line 27

      Error Code: 6531

       

      Call:

      DECLARE

        P_EMPTARGET PKG.EMP_TABLE;

        P_EMPCOMPAT EMP_VARRAY := :1;

        P_STATUSTARGET VARCHAR(255);

        FUNCTION EL_SQL2PL_1(aSqlItem EMP_TYPE)

        RETURN PKG.EMP_REC IS

          aPlsqlItem PKG.EMP_REC;

        BEGIN

          aPlsqlItem.EMP_ID := aSqlItem.EMP_ID;

          aPlsqlItem.NAME := aSqlItem.NAME

          aPlsqlItem.ADDRESS := aSqlItem.ADDRESS;

          RETURN aPlsqlItem;

        END EL_SQL2PL_1;

        FUNCTION EL_SQL2PL_3(aSqlItem EMP_VARRAY)

        RETURN PKG.EMP_TABLE IS

          aPlsqlItem PKG.EMP_TABLE;

        BEGIN

          IF aSqlItem.COUNT > 0 THEN

            FOR I IN 1..aSqlItem.COUNT LOOP

              aPlsqlItem(I) := EL_SQL2PL_1(aSqlItem(I));

            END LOOP;

          END IF;

          RETURN aPlsqlItem;

        END EL_SQL2PL_3;

      BEGIN

        P_EMPTARGET := EL_SQL2PL_3(P_EMPCOMPAT);

        PKG.PLSQL_COLL_EMP_PROC(P_EMP=>P_EMPTARGET, P_STATUS=>P_STATUSTARGET);

        :2 := P_STATUSTARGET;

      END;