[
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.
|
Thanks for your advice. it helped us.
We change the wrapper type from TABLE to VARRAY.
it solved our problem.
thank you.
2010/4/7 James Sutherland <jamesssss@xxxxxxxxx>:
>
> 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.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
