Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    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 21:34 Go to next message
Edwin Biemond is currently offline Edwin BiemondFriend
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();
}

}

Report message to a moderator

Re: PLSQLStoredProcedureCall Fail to convert to internal representation [message #813735 is a reply to message #811817] Mon, 05 March 2012 16:56 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member
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.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: PLSQLStoredProcedureCall Fail to convert to internal representation [message #823641 is a reply to message #813735] Sun, 18 March 2012 17:00 Go to previous messageGo to next message
Edwin Biemond is currently offline Edwin BiemondFriend
Messages: 5
Registered: December 2010
Junior Member
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)

Report message to a moderator

Re: PLSQLStoredProcedureCall Fail to convert to internal representation [message #825217 is a reply to message #823641] Tue, 20 March 2012 16:04 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member
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.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: PLSQLStoredProcedureCall Fail to convert to internal representation [message #828473 is a reply to message #825217] Sat, 24 March 2012 21:46 Go to previous message
Edwin Biemond is currently offline Edwin BiemondFriend
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();
}

}

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Sep 15 05:58:24 GMT 2019

Powered by FUDForum. Page generated in 0.01869 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