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

Question

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_results;
    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.

Answer 1

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.

Answer 2

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.