[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] How to call some procedures which has an associative array as an argument.
|
I think your issue is your wrapper type must be a VARRAY not a TABLE type.
>> CREATE OR REPLACE TYPE xxz_test001_rec_tbl_type AS TABLE OF
>> xxz_test001_rec;
CREATE OR REPLACE TYPE xxz_test001_rec_tbl_type AS VARRAY(255) OF
xxz_test001_rec;
Otherwise you may want to try to simplify the query/call to narrow down the
issue.
There are some examples in our tests in SVN
<root>\foundation\eclipselink.core.test\src\org\eclipse\persistence\testing\models\plsql
oratta wrote:
>
> Hi,
>
> We have a problem to call some procedures which has an associative
> array as an argument.
> In our project, Eclipselink 1.2.0 (included in TopLink 11.1.1.2.0) is
> used on OC4J 10.1.3.5.
> Could someone help us?
>
> The procedure is as follow.
> [[PL/SQL Package]]
> CREATE OR REPLACE PACKAGE XXZ_TEST_PKG AS
> TYPE test001_rec IS RECORD (
> column01 VARCHAR2(10),
> column02 NUMBER);
> TYPE test001_rec_tbl_type IS TABLE OF test001_rec INDEX BY
> PLS_INTEGER;
> PROCEDURE procedure_tab_index(
> input IN NUMBER,
> output OUT VARCHAR2,
> inputoutput IN OUT test001_rec_tbl_type);
> END XXZ_TEST_PKG;
>
> Firstly we defined shadow types in the 'global' JDBC namespace outside
> of the PL/SQL package.
> [[Shadow Type]]
> CREATE OR REPLACE TYPE xxz_test001_rec AS OBJECT (
> column01 VARCHAR2(10),
> column02 NUMBER);
> CREATE OR REPLACE TYPE xxz_test001_rec_tbl_type AS TABLE OF
> xxz_test001_rec;
>
> Secondly we make some Java DTO class for mapping the PL/SQL record
> type and PL/SQL associative array.
> [[Java DTO ]]
> public class PLSQLHelperTestRecDTO1 implements Serializable{
> private String column01;
> private Long column02;
> }
> public class PLSQLHelperTestTableIndexDTO implements Serializable{
> private PLSQLHelperTestRecDTO1[] nest;
> }
>
> Finally we implements Java method to call the procedure.
> [[Java method ]]
> /*Descriptor for mapping nested Record*/
> ObjectRelationalDataTypeDescriptor nestedRecordDesc =
> new ObjectRelationalDataTypeDescriptor();
> nestedRecordDesc.descriptorIsAggregate();
>
> nestedRecordDesc.setJavaClass(PLSQLHelperTestRecDTO1.class);
> nestedRecordDesc.setAlias("NestedRecord");
> nestedRecordDesc.setStructureName("XXZ_TEST001_REC");
> DirectToFieldMapping value01Mapping = new
> DirectToFieldMapping();
> value01Mapping.setAttributeName("column01");
> value01Mapping.setFieldName("COLUMN01");
> nestedRecordDesc.addMapping(value01Mapping);
> DirectToFieldMapping value02Mapping = new
> DirectToFieldMapping();
> value02Mapping.setAttributeName("column01");
> value02Mapping.setFieldName("COLUMN02");
> nestedRecordDesc.addMapping(value02Mapping);
>
> JpaHelper.getEntityManager(em).getServerSession().addDescriptor(nestedRecordDesc);
>
> /*Descriptor for mapping Associative Array*/
> ObjectRelationalDataTypeDescriptor recordDescriptor =
> new ObjectRelationalDataTypeDescriptor();
> recordDescriptor.descriptorIsAggregate();
>
> recordDescriptor.setJavaClass(PLSQLHelperTestTableIndexDTO.class);
>
> nestedRecordDesc.setStructureName("XXZ_TEST001_REC_TBL_TYPE");
> recordDescriptor.setAlias("TableIndex");
>
> JpaHelper.getEntityManager(em).getServerSession().addDescriptor(recordDescriptor);
>
> PLSQLrecord inRecordNest = new PLSQLrecord();
> inRecordNest.setTypeName("XXZ_TEST_PKG.TEST001_REC");
> inRecordNest.setCompatibleType("XXZ_TEST001_REC");
> inRecordNest.setJavaType(PLSQLHelperTestRecDTO1.class);
> inRecordNest.addField("COLUMN01", JDBCTypes.VARCHAR_TYPE);
> inRecordNest.addField("COLUMN02", JDBCTypes.NUMERIC_TYPE);
>
> PLSQLCollection inRecord = new PLSQLCollection();
> inRecord.setTypeName("XXZ_TEST_PKG.TEST001_REC_TBL_TYPE");
> inRecord.setCompatibleType("XXZ_TEST001_REC_TBL_TYPE");
> inRecord.setJavaType(PLSQLHelperTestTableIndexDTO.class);
> inRecord.setNestedType(inRecordNest);
>
> PLSQLStoredProcedureCall call = new
> PLSQLStoredProcedureCall();
> call.setProcedureName("XXZ_TEST_PKG.procedure_tab_index");
> call.addNamedArgument("INPUT",JDBCTypes.NUMERIC_TYPE);
> call.addNamedOutputArgument("OUTPUT",
> JDBCTypes.VARCHAR_TYPE);
> call.addNamedInOutputArgument("INPUTOUTPUT", inRecord);
> ValueReadQuery query = new ValueReadQuery();
> query.addArgument("INPUT");
> query.addArgument("INPUTOUTPUT",
> PLSQLHelperTestTableIndexDTO.class);
>
> query.setCall(call);
> query.setResultType(DataReadQuery.ARRAY);
> query.bindAllParameters();
>
> PLSQLHelperTestRecDTO1 nestedRec = new
> PLSQLHelperTestRecDTO1();
> nestedRec.setColumn01("Test");
> nestedRec.setColumn02(1L);
>
> PLSQLHelperTestTableIndexDTO rec = new
> PLSQLHelperTestTableIndexDTO();
> rec.setNest(new PLSQLHelperTestRecDTO1[] {nRec});
>
> List attributes = new ArrayList();
> attributes.add(in);
> attributes.add(rec);
>
> List attributes = new ArrayList();
> attributes.add(in);
> attributes.add(rec);
> Object returnObject =
> JpaHelper.getEntityManager(em).getServerSession().executeQuery(query,
> attributes);
>
>
> But, we cannot call ther procedure because of this error, as follow.
> How can I do this?
> Sorry for my poor English.
>
> best regards.
>
>
> [[Error message]]
> Exception [EclipseLink-4002] (Eclipse Persistence Services -
> 1.2.0.v20091016-r5565):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: java.sql.SQLException: 列の型が無効です。
> Error Code: 17004
> Call:
> DECLARE
> input_TARGET NUMERIC := :1;
> inputoutput_TARGET XXZ_TEST_PKG.TEST001_REC_TBL_TYPE;
> inputoutput_COMPAT XXZ_TEST001_REC_TBL_TYPE := :2;
> output_TARGET VARCHAR(255);
> FUNCTION EL_SQL2PL_1(aSqlItem XXZ_TEST001_REC)
> RETURN XXZ_TEST_PKG.TEST001_REC IS
> aPlsqlItem XXZ_TEST_PKG.TEST001_REC;
> BEGIN
> aPlsqlItem.column01 := aSqlItem.column01;
> aPlsqlItem.column02 := aSqlItem.column02;
> RETURN aPlsqlItem;
> END EL_SQL2PL_1;
> FUNCTION EL_PL2SQL_0(aPlsqlItem XXZ_TEST_PKG.TEST001_REC)
> RETURN XXZ_TEST001_REC IS
> aSqlItem XXZ_TEST001_REC;
> BEGIN
> aSqlItem := XXZ_TEST001_REC(NULL, NULL);
> aSqlItem.column01 := aPlsqlItem.column01;
> aSqlItem.column02 := aPlsqlItem.column02;
> RETURN aSqlItem;
> END EL_PL2SQL_0;
> FUNCTION EL_SQL2PL_3(aSqlItem XXZ_TEST001_REC_TBL_TYPE)
> RETURN XXZ_TEST_PKG.TEST001_REC_TBL_TYPE IS
> aPlsqlItem XXZ_TEST_PKG.TEST001_REC_TBL_TYPE;
> 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;
> FUNCTION EL_PL2SQL_2(aPlsqlItem XXZ_TEST_PKG.TEST001_REC_TBL_TYPE)
> RETURN XXZ_TEST001_REC_TBL_TYPE IS
> aSqlItem XXZ_TEST001_REC_TBL_TYPE;
> BEGIN
> aSqlItem := XXZ_TEST001_REC_TBL_TYPE();
> aSqlItem.EXTEND(aPlsqlItem.COUNT);
> IF aPlsqlItem.COUNT > 0 THEN
> FOR I IN aPlsqlItem.FIRST..aPlsqlItem.LAST LOOP
> aSqlItem(I + 1 - aPlsqlItem.FIRST) := EL_PL2SQL_0(aPlsqlItem(I));
> END LOOP;
> END IF;
> RETURN aSqlItem;
> END EL_PL2SQL_2;
> BEGIN
> inputoutput_TARGET := EL_SQL2PL_3(inputoutput_COMPAT);
> XXZ_TEST_PKG.procedure_tab_index(input=>input_TARGET,
> output=>output_TARGET, inputoutput=>inputoutput_TARGET);
> :3 := output_TARGET;
> :4 := EL_PL2SQL_2(inputoutput_TARGET);
> END;
> bind => [:1 => 10, :2 =>
> test.jp.co.benesse.y01.z0.dao.util.test.PLSQLHelperTestTableIndexDTO@18dd1d8,
> output => :3, inputoutput => :4]
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>
-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink , http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
--
View this message in context: http://old.nabble.com/How-to-call-some-procedures-which-has-an-associative-array-as-an-argument.-tp28147144p28166238.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
