Announcement Announcement Module
Collapse
No announcement yet.
Having an issue with selecting from table function... Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Having an issue with selecting from table function...

    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;
    }

    }; ) ;
    ....................
Working...
X