Skip to main content


Eclipse Community Forums

      Home
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 16:34 Go to next message
Eclipse UserFriend
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 #813735 is a reply to message #811817] Mon, 05 March 2012 11:56 Go to previous messageGo to next message
Eclipse UserFriend
One thing wrong I noticed was the name of the Struct in Department should be HR_DEPARTMENT_ROW_TYPE, not HR_DEPARTMENT_TYPE.
Does that fix it?

Maybe start out trying a procedure that just takes the RECORD type, and when you get that working, try the TABLE type.
Re: PLSQLStoredProcedureCall Fail to convert to internal representation [message #823641 is a reply to message #813735] Sun, 18 March 2012 13:00 Go to previous messageGo to next message
Eclipse UserFriend
Hi,

I did some more test but somehow everything is failing, even the example of the wiki
wiki.eclipse.org/EclipseLink/UserGuide/JPA/Advanced_JPA_Development/Stored_Procedure_and_PL/SQL_Support

what is your environment and does it works for you.

I get this as result instead of the entity
DatabaseRecord( RESULT => oracle.sql.STRUCT@21cfaa8)

thanks


[EL Info]: 2012-03-18 17:44:29.942--EclipseLink, version: Eclipse Persistence Services - 2.3.2.v20111125-r10461
[EL Config]: 2012-03-18 17:44:30.072--Connection(31678781)--connecting(DatabaseLogin(
platform=>Oracle11Platform
user name=> "hr"
datasource URL=> "jdbc:oracle:thin:@localhost:1521:XE"
))
[EL Config]: 2012-03-18 17:44:30.964--Connection(32442774)--Connected: jdbc:oracle:thin:@localhost:1521:XE
User: HR
Database: Oracle Version: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
Driver: Oracle JDBC driver Version: 11.2.0.2.0
[EL Info]: 2012-03-18 17:44:30.984--file:/C:/projecten/workspace/11gR1/PLSQLWrapper/HrMulitDeptService/classes/_HrMulitDeptService login successful
[EL Fine]: 2012-03-18 17:44:31.174--Connection(32442774)--
DECLARE
RESULT_TARGET EMP_PKG.EMP_REC;
FUNCTION EL_PL2SQL_0(aPlsqlItem EMP_PKG.EMP_REC)
RETURN EMP_TYPE IS
aSqlItem EMP_TYPE;
BEGIN
aSqlItem := EMP_TYPE(NULL, NULL, NULL);
aSqlItem.F_NAME := aPlsqlItem.F_NAME;
aSqlItem.L_NAME := aPlsqlItem.L_NAME;
aSqlItem.SALARY := aPlsqlItem.SALARY;
RETURN aSqlItem;
END EL_PL2SQL_0;
BEGIN
RESULT_TARGET := EMP_PKG.GET_EMP();
:1 := EL_PL2SQL_0(RESULT_TARGET);
END;
bind => [RESULT => :1]
Exception in thread "Main Thread" java.lang.ClassCastException: org.eclipse.persistence.sessions.DatabaseRecord
at nl.amis.jpa.TestSelect.main(TestSelect.java:47)


with this, I got a Oracle STRUCT as result.

Query query = ((JpaEntityManager)em.getDelegate()).createQuery(databaseQuery);
//Employee result = (Employee)query.getSingleResult();
DatabaseRecord result = (DatabaseRecord)query.getSingleResult();
Object record1 = result.get("RESULT");

DatabaseRecord(
RESULT => oracle.sql.STRUCT@21cfaa8)
Re: PLSQLStoredProcedureCall Fail to convert to internal representation [message #825217 is a reply to message #823641] Tue, 20 March 2012 12:04 Go to previous messageGo to next message
Eclipse UserFriend
Ensure you define the javaType on the record, and defined a descriptor for that type.
(em.unwrap(Session.class).getDescriptor(Employee.class)) should return a valid descriptor.
Re: PLSQLStoredProcedureCall Fail to convert to internal representation [message #828473 is a reply to message #825217] Sat, 24 March 2012 17:46 Go to previous message
Eclipse UserFriend
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();
}

}
Previous Topic:IntegrityChecker with PostgreSQL
Next Topic:BundleException in org.eclipse.persistence.jpa.osgi.Activator.start()
Goto Forum:
  


Current Time: Fri Jul 04 19:39:30 EDT 2025

Powered by FUDForum. Page generated in 0.05986 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly