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

Posted by Benny on Stack Overflow See other posts from Stack Overflow or by Benny
Published on 2010-05-24T16:16:30Z Indexed on 2010/05/24 16:21 UTC
Read the original article Hit count: 295

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about java

Related posts about Oracle