SimpleJdbcCall - How to extract table parameter from stored procedure in package

812 Views Asked by At

This is my situation: I am trying to call a procedure (which has been declared within a PL/SQL package) with two parameters:

PROCEDURE p_process_docs (
  p_cod IN NUMBER,
  p_doc_t OUT O_DOC_T
)

O_DOC_T is defined as following:

create or replace TYPE O_DOC_T FORCE AS TABLE OF O_DOC_S;

And O_DOC_S definition is like this:

create or replace TYPE O_DOC_S FORCE AS OBJECT
(
    COD_DOC_TYPE                   VARCHAR2(3),
    COD_DOCUMENT                   VARCHAR2(20),
    ...
,CONSTRUCTOR FUNCTION O_DOC_S(
     P_DOC_TYPE                    VARCHAR2,
     P_DOCUMENT                    VARCHAR2,
     ...
);

With SimpleJdbcCall I'm trying to read that second parameter, so I prepared this:

private List<O_Doc_S> processDocs(final String cod) {
        List<O_Doc_S> result = null;
        RowMapper<O_Doc_S> rm = new ParameterizedRowMapper<O_Doc_S>() {
            @Override
            public O_Doc_SmapRow(ResultSet rs, int rowNum) throws SQLException {
                O_Doc_Sresult = new O_Doc_S();
                result.setCod_doc_type(rs.getInt("cod_doc_type"));
                result.setCod_document(rs.getString("cod_tipo_docum"));
                // Rest of mappings
                return result;
            }
        };
        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource)
                .withCatalogName(CATALOG_NAME)
                .withProcedureName("p_process_docs ")
                .useInParameterNames("p_cod")
                .returningResultSet("p_doc_t", rm);;
        Map<String, Object> inParamMap = new HashMap<String, Object>();
        inParamMap.put("p_cod", Integer.valueOf(cod));
        SqlParameterSource in = new MapSqlParameterSource(inParamMap);

        try {
            Map<String, Object> out = simpleJdbcCall.execute(in);
            // Iterate and store in result
            logger.info("Number of values received: ");
        } catch (Exception e) {
            logger.error("Error");
            result = new ArrayList<O_Doc_S>();
        }
        return result;
    }

But I got this error on the execute line:

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'p_process_docs '
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I have tried many changes (declare the in/out params, switch to a function, remove/add params) and everything ended up in error (being "wrong number or type of arguments" the most commmon). I'm quite sure it is possible to get a list of records from a PL/SQL procedure/function but it escapes me how to do it properly. All I have found were explanations on how to read a single basic value at best. What I require is much more complex.

Any suggestions? Also, the solution must fulfill these two conditions:

  • It is mandatory to return a list/table of values.
  • It must work with JDK 1.6.
  • The procedure can be modified (even switch it to a function) but the same doesn't apply to the types.
0

There are 0 best solutions below