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;