Philippe Marschall opened SPR-12620 and commented

When the database user is not the schema owner the parameters have to be declared.

The following works when the database user is the schema owner.

    Map<String, ?> params = Collections.singletonMap("pi_param_name", param);

    Map<String, Object> result = new SimpleJdbcCall(jdbcTemplate)
      .withCatalogName("the_package")
      .withProcedureName("the_procedure")
      .execute(params);

However it fails when when he's not the schema owner and the_package.the_procedure is accessed through a public synonym. If that is the case you need to additionally call #declareParameters and disable metadata access

    Map<String, ?> params = Collections.singletonMap("pi_param_name", param);

    Map<String, Object> result = new SimpleJdbcCall(jdbcTemplate)
      .withCatalogName("the_package")
      .withProcedureName("the_procedure")
      .withoutProcedureColumnMetaDataAccess()
      .declareParameters(new SqlParamter("pi_param_name", Types.VARCHAR))
      .execute(params);

Which forces you to repeat yourself. The issue boils down to: * when the SimpleJdbcCall is compiled it does not have access to the parameters and relies solely on the database metadata to compose the query string * OracleCallMetaDataProvider#metaDataCatalogNameToUse defaults to the user name * GenericCallMetaDataProvider#processProcedureColumns passes the above result to DatabaseMetaData#getProcedures

Solving this is a bit tricky. A possible way we see is to change GenericCallMetaDataProvider#processProcedureColumns to: * first call DatabaseMetaData#getProcedures with either "" or the result of #metaDataSchemaNameToUse (at this point we don't see which one is preferable). * If nothing was found and and the schemaPattern passed to DatabaseMetaData#getProcedures was not null call it again with null

We are using Oracle.

Affects: 4.1.1

2 votes, 5 watchers

Comment From: spring-projects-issues

Haim Zamir commented

Is this really waiting for Triage for two years?

Comment From: spring-projects-issues

Juergen Hoeller commented

I'm happy to consider this but it's just not at the top of the list and only has two votes...

If you'd like to speed it up, a pull request would be very appreciated. Doesn't have to be perfect, even a concrete suggestion helps.

Comment From: jhoeller

Closing this issue due to the lack of feedback for us to act on. If there is a concrete enhancement that we can apply for modern-day Oracle, a PR would be welcome.