Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[eclipselink-users] How to call some procedures which has an associative array as an argument. 
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<Object>();
                attributes.add(in);
                attributes.add(rec);

                List attributes = new ArrayList<Object>();
                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]

Back to the top