Home » Eclipse Projects » EclipseLink » PLSQLStoredProcedureCall Fail to convert to internal representation(Call a oracle procedure which has plsql table as input)
PLSQLStoredProcedureCall Fail to convert to internal representation [message #811817] |
Fri, 02 March 2012 21:34 |
Edwin Biemond Messages: 5 Registered: December 2010 |
Junior Member |
|
|
Hi,
I am using EclipseLink 2.3.2 and got this error
the PLSQL code looks Ok but Oracle JDBC can not convert my department object to the plsql type
hope someone got something like this working.
thanks
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Fail to convert to internal representation: nl.amis.jpa.entities.Departments@2b3d9460
Error Code: 17059
Call:
DECLARE
P_RECORDS_TARGET HR_DEPARTMENTS.DEPTTABTYPE;
P_RECORDS_COMPAT HR_DEPARTMENT_TYPE := :1;
FUNCTION EL_SQL2PL_1(aSqlItem HR_DEPARTMENT_ROW_TYPE)
RETURN HR_DEPARTMENTS.DEPARTMENTRECTYPE IS
aPlsqlItem HR_DEPARTMENTS.DEPARTMENTRECTYPE;
BEGIN
aPlsqlItem.DEPARTMENT_ID := aSqlItem.DEPARTMENT_ID;
aPlsqlItem.DEPARTMENT_NAME := aSqlItem.DEPARTMENT_NAME;
aPlsqlItem.LOCATION_ID := aSqlItem.LOCATION_ID;
aPlsqlItem.MANAGER_ID := aSqlItem.MANAGER_ID;
RETURN aPlsqlItem;
END EL_SQL2PL_1;
FUNCTION EL_SQL2PL_3(aSqlItem HR_DEPARTMENT_TYPE)
RETURN HR_DEPARTMENTS.DEPTTABTYPE IS
aPlsqlItem HR_DEPARTMENTS.DEPTTABTYPE;
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_RECORDS_TARGET := EL_SQL2PL_3(P_RECORDS_COMPAT);
HR_DEPARTMENTS.INSERTDEPARTMENTS(P_RECORDS=>P_RECORDS_TARGET);
END;
bind => [:1 => [nl.amis.jpa.entities.Departments@2b3d9460, nl.amis.jpa.entities.Departments@57d840cd]]
Query: DataReadQuery()
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717)
Caused by: java.sql.SQLException: Fail to convert to internal representation: nl.amis.jpa.entities.Departments@2b3d9460
at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTypeADT.java:532)
at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(OracleTypeADT.java:602)
at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(OracleTypeUPT.java:253)
at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:2438)
at oracle.sql.ARRAY.<init>(ARRAY.java:124)
at org.eclipse.persistence.platform.database.oracle.Oracle8Platform.createArray(Oracle8Platform.java:267)
I tried plsql table and varray but always the same error.
create or replace
type hr_department_row_type as object
( department_id number(4,0)
, department_name varchar2(30)
, manager_id number(6,0)
, location_id number(4,0)
);
create or replace
type hr_department_type as table of hr_department_row_type;
create or replace
package hr_departments
is
type DepartmentRecType is RECORD ( DEPARTMENT_ID NUMBER(4,0)
, DEPARTMENT_NAME VARCHAR2(30)
, MANAGER_ID NUMBER(6,0)
, LOCATION_ID NUMBER(4,0)
);
type DeptTabType is TABLE of DepartmentRecType INDEX BY BINARY_INTEGER;
procedure insertDepartments(p_records in DeptTabType);
procedure test;
end hr_departments;
create or replace
package body hr_departments
is
procedure insertDepartments(p_records in DeptTabType)
is
begin
forall i in p_records.first..p_records.last
insert into departments ( department_id
, department_name
, manager_id
, LOCATION_ID
)
values ( p_records(i).department_id
, p_records(i).department_name
, p_records(i).MANAGER_ID
, p_records(i).location_id
);
end;
procedure test
is
v_records DeptTabType;
v_record1 DepartmentRecType;
begin
v_record1.DEPARTMENT_ID := 501;
v_record1.DEPARTMENT_NAME := 'arnhem';
v_record1.LOCATION_ID := 1700;
v_record1.MANAGER_ID := 100;
v_records(1) := v_record1;
v_record1.DEPARTMENT_ID := 502;
v_record1.DEPARTMENT_NAME := 'amersfoort';
v_record1.LOCATION_ID := 1700;
v_record1.MANAGER_ID := 100;
v_records(2) := v_record1;
insertDepartments(p_records => v_records);
end;
end hr_departments;
the department entity
package nl.amis.jpa.entities;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Embeddable;
import org.eclipse.persistence.annotations.Struct;
@Embeddable
@Struct( name = "HR_DEPARTMENT_TYPE" ,
fields = {"DEPARTMENT_ID",
"DEPARTMENT_NAME",
"LOCATION_ID",
"MANAGER_ID" } )
public class Departments implements Serializable {
private static final long serialVersionUID = 1L;
@Column(name="DEPARTMENT_ID")
private Long departmentId;
@Column(name="DEPARTMENT_NAME")
private String departmentName;
@Column(name="LOCATION_ID")
private Long locationId;
@Column(name="MANAGER_ID")
private Long managerId;
public Departments() {
}
}
and my test code
package nl.amis.jpa;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import nl.amis.jpa.entities.Departments;
import org.eclipse.persistence.jpa.JpaHelper;
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLCollection;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;
import org.eclipse.persistence.queries.DataReadQuery;
public class TestMultiInsert {
public TestMultiInsert() {
}
public static void main(String[] a) throws Exception {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("HrMulitDeptService");
EntityManager em = emf.createEntityManager();
// em.getTransaction().begin();
DataReadQuery databaseQuery = new DataReadQuery();
databaseQuery.setResultType(DataReadQuery.VALUE);
PLSQLrecord record = new PLSQLrecord();
record.setTypeName("HR_DEPARTMENTS.DEPARTMENTRECTYPE");
record.setCompatibleType("HR_DEPARTMENT_ROW_TYPE");
record.setJavaType(Departments.class);
record.addField("DEPARTMENT_ID", JDBCTypes.NUMERIC_TYPE, 4,0);
record.addField("DEPARTMENT_NAME", JDBCTypes.VARCHAR_TYPE, 30);
record.addField("LOCATION_ID", JDBCTypes.NUMERIC_TYPE, 4, 0);
record.addField("MANAGER_ID", JDBCTypes.NUMERIC_TYPE, 6, 0);
PLSQLCollection collection = new PLSQLCollection();
collection.setTypeName("HR_DEPARTMENTS.DEPTTABTYPE");
collection.setCompatibleType("HR_DEPARTMENT_TYPE");
collection.setJavaType(List.class);
collection.setNestedType(record);
List dept = new ArrayList();
Departments dept1 = new Departments();
dept1.setDepartmentId(505L);
dept1.setDepartmentName("amersfoort");
dept1.setLocationId(1700L);
dept1.setManagerId(100L);
dept.add(dept1);
Departments dept2 = new Departments();
dept2.setDepartmentId(506L);
dept2.setDepartmentName("utrecht");
dept2.setLocationId(1700L);
dept2.setManagerId(100L);
dept.add(dept2);
PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
call.addNamedArgument("P_RECORDS", collection);
call.setProcedureName("HR_DEPARTMENTS.INSERTDEPARTMENTS");
databaseQuery.addArgument("P_RECORDS",PLSQLCollection.class);
databaseQuery.setCall(call);
databaseQuery.bindAllParameters();
Vector args=new Vector();
args.add(dept);
Object result = JpaHelper.getEntityManager(em).getActiveSession().executeQuery(databaseQuery,args);
// em.getTransaction().commit();
em.close();
emf.close();
}
}
|
|
| | | |
Re: PLSQLStoredProcedureCall Fail to convert to internal representation [message #828473 is a reply to message #825217] |
Sat, 24 March 2012 21:46 |
Edwin Biemond Messages: 5 Registered: December 2010 |
Junior Member |
|
|
Thanks a lot.
Everything is working perfectly know
Creating an ObjectRelationalDataTypeDescriptor is the answer
even in the example of the eclipselink wiki.
thanks
package nl.amis.jpa;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import nl.amis.jpa.entities.Departments;
import org.eclipse.persistence.jpa.JpaEntityManager;
import org.eclipse.persistence.jpa.JpaHelper;
import org.eclipse.persistence.mappings.structures.ObjectRelationalDataTypeDescriptor;
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLCollection;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;
import org.eclipse.persistence.queries.DataReadQuery;
import org.eclipse.persistence.sessions.server.ServerSession;
public class TestMultiInsert {
public TestMultiInsert() {
}
public static void main(String[] a) throws Exception {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("HrMulitDeptService");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
DataReadQuery databaseQuery = new DataReadQuery();
PLSQLrecord record = new PLSQLrecord();
record.setTypeName("HR_DEPARTMENTS.DEPARTMENTRECTYPE");
record.setCompatibleType("HR_DEPARTMENT_ROW_TYPE");
record.setJavaType(Departments.class);
record.addField("DEPARTMENT_ID", JDBCTypes.NUMERIC_TYPE, 4,0);
record.addField("DEPARTMENT_NAME", JDBCTypes.VARCHAR_TYPE, 30);
record.addField("LOCATION_ID", JDBCTypes.NUMERIC_TYPE, 4, 0);
record.addField("MANAGER_ID", JDBCTypes.NUMERIC_TYPE, 6, 0);
PLSQLCollection collection = new PLSQLCollection();
collection.setTypeName("HR_DEPARTMENTS.DEPTTABTYPE");
collection.setCompatibleType("HR_DEPARTMENT_TYPE");
collection.setJavaType(ArrayList.class);
collection.setNestedType(record);
List dept = new ArrayList();
Departments dept1 = new Departments();
dept1.setDepartmentId(505L);
dept1.setDepartmentName("amersfoort");
dept1.setLocationId(1700L);
dept1.setManagerId(100L);
dept.add(dept1);
Departments dept2 = new Departments();
dept2.setDepartmentId(506L);
dept2.setDepartmentName("utrecht");
dept2.setLocationId(1700L);
dept2.setManagerId(100L);
dept.add(dept2);
PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
call.addNamedArgument("P_RECORDS", collection,JDBCTypes.ARRAY_TYPE.getSqlCode());
call.setProcedureName("HR_DEPARTMENTS.INSERTDEPARTMENTS");
databaseQuery.addArgument("P_RECORDS",ArrayList.class);
databaseQuery.setCall(call);
Vector args=new Vector();
args.add(dept);
ServerSession session =
((JpaEntityManager)em.getDelegate()).getServerSession();
ObjectRelationalDataTypeDescriptor descriptor =
new ObjectRelationalDataTypeDescriptor();
descriptor.setJavaClass(Departments.class);
descriptor.setTableName("HR_DEPARTMENTS.DEPARTMENTRECTYPE");
descriptor.setStructureName("HR_DEPARTMENT_ROW_TYPE");
descriptor.addFieldOrdering("DEPARTMENT_ID");
descriptor.addFieldOrdering("DEPARTMENT_NAME");
descriptor.addFieldOrdering("MANAGER_ID");
descriptor.addFieldOrdering("LOCATION_ID");
descriptor.addDirectMapping("departmentId",
"getDepartmentId",
"setDepartmentId",
"DEPARTMENT_ID");
descriptor.addDirectMapping("departmentName",
"getDepartmentName",
"setDepartmentName",
"DEPARTMENT_NAME");
descriptor.addDirectMapping("managerId",
"getManagerId",
"setManagerId",
"MANAGER_ID");
descriptor.addDirectMapping("locationId",
"getLocationId",
"setLocationId",
"LOCATION_ID");
descriptor.addPrimaryKeyFieldName("DEPARTMENT_ID");
session.addDescriptor(descriptor);
// execute
JpaHelper.getEntityManager(em).getActiveSession().executeQuery(databaseQuery,args);
em.getTransaction().commit();
em.close();
emf.close();
}
}
|
|
|
Goto Forum:
Current Time: Fri Apr 19 23:54:19 GMT 2024
Powered by FUDForum. Page generated in 0.03408 seconds
|