"java.sql.SQLException: Missing IN or OUT parameter at index:: 1" when calling function

Question

While calling SimpleJdbcCall.withFunctionName I get java.sql.SQLException: Missing IN or OUT parameter at index:: 1

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(myDataSource)
.withCatalogName("package_name")
.withFunctionName("x");
String res = simpleJdbcCall.executeFunction(String.class, in);

SimpleJdbcCall parameters are:

MapSqlParameterSource in = new MapSqlParameterSource();

in.addValue("1", "a", Types.VARCHAR);
in.addValue("2", b, Types.NUMERIC);
in.addValue("3", c, Types.VARCHAR);
in.addValue("4", "d", Types.VARCHAR);
in.addValue("5", e, Types.NUMERIC);
in.addValue("6", "f", Types.VARCHAR);
in.addValue("7", g, Types.NUMERIC);
in.addValue("8", h, Types.NUMERIC);
in.addValue("9", null, Types.NUMERIC);

Oracle function declaration:

function x(1 in varchar2,
2 in number,
3 in varchar2,
4 in varchar2,
5 in number,
6 in varchar2,
7 in number default null,
8 in number default null,
9 in number default null) return varchar2 is

As far as I can see I am doing everything as per documentation. Only parameters in my Oracle function are not declared as in:

Alison Graham · Accepted Answer · 2023-07-14 18:27:12Z

0

I had this issue. I had to turn off MetaData access by adding .withoutProcedureColumnMetaDataAccess() like this:

        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withSchemaName("ar_pcmh.pcmh_prvdr_rqst_pkg")
            .withFunctionName("test_fnctn")
            .withoutProcedureColumnMetaDataAccess()
            .useInParameterNames("p_integer")
            .declareParameters(
                    new SqlParameter("p_integer", Types.INTEGER),
                    new SqlOutParameter("result", Types.INTEGER)        
            );

answered Jul 14, 2023 at 18:27

Alison Graham

418 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

"java.sql.SQLException: Missing IN or OUT parameter at index:: 1" when calling function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related