How do I return a nested table from an oracle function using Java?

I have the following type declaration and Oracle function:

CREATE OR REPLACE TYPE var_outcomes_results IS TABLE OF VARCHAR2(80);

CREATE OR REPLACE FUNCTION getValuesAbove(in_nodeID IN table1.KEY_SL%TYPE,
                                          in_variable IN VARCHAR2)
    RETURN var_outcomes_results
IS
    currentID table1.KEY_SL%TYPE;
    results var_outcomes_re开发者_如何学JAVAsults;
    currentIndex integer := 0;
BEGIN
    currentID := in_nodeID;

    WHILE currentID != null
    LOOP
        FOR outcomeRecord IN
            (select distinct a.PARENT, b.NAME, c.OUTCOME
             from table1 a
             left outer join table2 b on a.KEY_SL = b.KEY_SL
             left outer join table3 c on b.VAR_ID = c.VAR_ID
             where a.KEY_SL = currentID)
        LOOP
            currentID := outcomeRecord.PARENT;

            IF lower(outcomeRecord.NAME) = lower(in_variable) AND
               outcomeRecord.OUTCOME != null THEN
                currentIndex := currentIndex + 1; 
                results(currentIndex) := outcomeRecord.OUTCOME;
            END IF;
        END LOOP;
    END LOOP;

    RETURN results;
END;

I have the following Java function:

public List<Object> getAboveValues(String variable, Integer nodeID)
{
    Connection connection = null;
    CallableStatement callableStatement = null;

    try
    {
        connection = dataSource.getConnection();
        callableStatement = connection.prepareCall("begin ? := getValuesAbove(?,?); end;");

        callableStatement.registerOutParameter(1, OracleTypes.ARRAY);
        callableStatement.setInt(2, nodeID);
        callableStatement.setString(3, variable);
        callableStatement.execute();

        System.out.println(callableStatement.getObject(1));
    }
    catch( SQLException e )
    {
        logger.error("An Exception was thrown in getAboveValues: " + e);
    }
    finally
    {
        closeDataResources(callableStatement, connection);
    }
}

However, when I execute the function, I get the following error message: "ORA-03115: unsupported network datatype or representation"

What am I doing wrong?

Any ideas/suggestions would be appreciated.

Thanks, B.J.

I can't check this right now, but I think you could do this with a preparedStatement and resultSet using

... = connection.prepareStatement("select * from table(getValuesAbove(?,?))");

This should work with the thin driver, as far as I can remember - all the hard work is being done on the database so it looks like any other select from JDBC.

If you want to return TABLE OF VARCHAR2 you should use Oracle-specific code: OracleCallableStatement.registerIndexTableOutParameter instead of CallableStatement.registerOutParameter.

Since this requires OCI driver instead of the Thin driver, I cannot test this code.

Try this.
There are 3 collection types in PL/SQL: associative array, nested table, and varray.
You can transfer the data in the nested table into varray data type.
Then, you can follow the procedures in this link:
Fetch pl/sql array return values in java

Comment me if you found a direct solution so I can also use it. :)