SimpleJdbcCall and Oracle's PLS_INTEGER return type

[lako12]

Hello,

I am using spring boot 2.7.4. I'm trying to call this Oracle function:

FUNCTION GetKey(
  p_table VARCHAR2
) RETURN PLS_INTEGER;

To do this, I'm using SimpleJdbcCall:

        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
                .withCatalogName("MYPACKAGE")
                .withFunctionName("GetKey");

        MapSqlParameterSource inputParamValues = new MapSqlParameterSource();
        inputParamValues.addValue("p_table", "TEST");

        Map<String, Object> obj = simpleJdbcCall.execute(inputParamValues);

The problem is that I get this error:

SQL state [99999]; error code [17004]; [Invalid column type: 1111

However if I change the return type inside my function from PLS_INTEGER to NUMBER, it works fine.

Is it a bug? I don't know if I'm doing something wrong.

I know I can use parameter declaration, but I wanted to understand why it doesn't recognize PLS_INTEGER automatically like it does for other types.

Thanks

[sbrannen]

I wanted to understand why it doesn't recognize PLS_INTEGER automatically like it does for other types.

I believe it's because PLS_INTEGER is not a standard SQL type, but @jhoeller can likely provide additional information.

[lako12]

@sbrannen @jhoeller any updates? Thanks

[lako12]

Hi,

@sbrannen @jhoeller any updates?

Thanks

[snicoll]

Yes, it looks like that's the reason. This question might be of interest as well. There's no evidence I can see that something in SimpleJdbcCall should be fixed so I am going to close this.