Bug 353417

Description Mike Norman 2011-07-29 10:10:40 EDT

From the issue raised in the OTN TopLink forum:
   http://forums.oracle.com/forums/thread.jspa?threadID=2257216

A simple PLSQL collection type is used in a Stored Procedure call query:

CREATE OR REPLACE PACKAGE MY_PACKAGE AS 
    TYPE MY_ARRAY IS TABLE OF VARCHAR2(20); 
    PROCEDURE TEST(NAMES IN MY_ARRAY, CNT OUT NUMERIC);  
END MY_PACKAGE;

CREATE OR REPLACE PACKAGE BODY MY_PACKAGE AS
  PROCEDURE TEST(NAMES IN MY_ARRAY, CNT OUT NUMERIC) AS
  BEGIN
    CNT := NAMES.COUNT;
  END TEST;
END MY_PACKAGE;

        PLSQLCollection myArrayType = new PLSQLCollection();
        myArrayType.setTypeName("MY_PACKAGE.MY_ARRAY");
        myArrayType.setCompatibleType("MY_PACKAGE_MY_ARRAY");
        myArrayType.setJavaType(ArrayList.class);
        myArrayType.setNestedType(JDBCTypes.VARCHAR_TYPE);

        PLSQLStoredProcedureCall testCall = new PLSQLStoredProcedureCall();
        testCall.setProcedureName("MY_PACKAGE.TEST");
        testCall.addNamedArgument("NAMES", myArrayType);
        testCall.addNamedOutputArgument("CNT", JDBCTypes.INTEGER_TYPE);
        ValueReadQuery query = new ValueReadQuery();
        query.addArgument("NAMES", ArrayList.class);
        query.setCall(testCall);

        ArrayList<String> items = new ArrayList<String>();
        items.add("first string");
        items.add("second string");
        items.add("third string");
        items.add("fourth string");
        items.add("fifth string");
        items.add("sixth string");
        items.add("seventh string");
        items.add("eighth string");
        items.add("nineth string");
        items.add("tenth string");
        Object value = null;
        try {
            List<Object> args = new ArrayList<Object>();
            args.add(items);
            value = session.executeQuery(query, args);
        }
        catch (Exception e) {
            e.printStackTrace();
        }

The following anonymous PLSQL block is generated:
DECLARE
  NAMES_TARGET MY_PACKAGE.MY_ARRAY;
  NAMES_COMPAT MY_PACKAGE_MY_ARRAY := :1;
  CNT_TARGET INTEGER;
  FUNCTION EL_SQL2PL_1(aSqlItem MY_PACKAGE_MY_ARRAY) RETURN MY_PACKAGE.MY_ARRAY IS
      aPlsqlItem MY_PACKAGE.MY_ARRAY;
  BEGIN
      IF aSqlItem.COUNT > 0 THEN
          FOR I IN 1..aSqlItem.COUNT LOOP
              aPlsqlItem(I) := aSqlItem(I);
          END LOOP;
      END IF;
      RETURN aPlsqlItem;
  END EL_SQL2PL_1;
BEGIN
  NAMES_TARGET := EL_SQL2PL_1(NAMES_COMPAT);
  MY_PACKAGE.TEST(NAMES=>NAMES_TARGET, CNT=>CNT_TARGET);
  :2 := CNT_TARGET;
END;

An exception is thrown:
...
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 11
...