How do I return a nested table from an oracle function using Java?
- by Benny
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.
