Hi there....

I am trying to do the table function call using SimpleJdbcCall object in Spring way, and I am stuck.
////////////////////////////////////////////////////////////////
// function definition
FUNCTION RATE_QUOTE (
p_employee_table IN RATE_EMPLOYEE_TABLE
)
RETURN RATE_DATA_TABLE pipelined;

// return table
create or replace type RATE_DATA_RECORD as
object(
EMPLOYEE_NUMBER NUMBER,
RATE NUMBER
);

create or replace type RATE_DATA_TABLE as table of RATE_DATA_RECORD;

// input table
create or replace type RATE_EMPLOYEE_RECORD
as object(
EMPLOYEE_NUMBER NUMBER,
AGE NUMBER,
);

create or replace type RATE_EMPLOYEE_TABLE as table of RATE_EMPLOYEE_RECORD;
////////////////////////////////////////////////////////////////


I had no problem using this function when I used oracle driver directly. You can use below query and set Oracle Array type to the statement.

"select * from table(RATE_QUOTE(?))"

Now, I am trying to use this in spring way with SimpleJdbcCall object and here is what I have done so far. Then, I don't know what to do to call this function and get the result.

Someone, please help......

.......
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withFunctionName("RATE_ QUOTE");
jdbcCall.getJdbcTemplate().setNativeJdbcExtractor( new OracleJdbc4NativeJdbcExtractor());
jdbcCall.getJdbcTemplate().setResultsMapCaseInsens itive(true);

MapSqlParameterSource mapIn = new MapSqlParameterSource();
this.jdbcCall.declareParameters(new SqlParameter("RATE_EMPLOYEE_RECORD", OracleTypes.STRUCT, "RATE_EMPLOYEE_TABLE"));
mapIn.addValue("P_EMPLOYEE_TABLE", new AbstractSqlTypeValue(){

@Override
protected Object createTypeValue(Connection connection, int sqlType, String typeName) throws SQLException {
StructDescriptor structDesc = StructDescriptor.createDescriptor(
"RATE_EMPLOYEE_RECORD", connection);
ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(
"RATE_EMPLOYEE_TABLE", connection);

List<STRUCT> structs = new ArrayList<STRUCT>();
structs.add(new STRUCT(structDesc, connection, new String[]{"1", "10"}));
structs.add(new STRUCT(structDesc, connection, new String[]{"2", "10"}));

ARRAY array_to_pass = new ARRAY(arrayDesc, connection, structs.toArray());
return array_to_pass;
}

}; ) ;
....................