1

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.
MangaPowerZero
  • 58
  • 5
  • One thing I notice is a leading space after the procedure name, you might want to remove that to begin with. Next the `execute` you are using will expect a scalar result and not the rowmapper. So you need to use the `executeObject` instead of the execute. Finally enable debug logging for `org.springframework.jdbc` as that will give you some information and what is being generated and executed. – M. Deinum Feb 16 '21 at 11:00
  • Thanks @M.Deinum for the tips! Actually part of the code is modified from the original and the leading space is not present in the actual code. Nonetheless you may be right about the `execute`, what type should I declare for `executeObject`? – MangaPowerZero Feb 16 '21 at 11:24

0 Answers0