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: 364
        
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