SimpleJdbcCall can't access synonyms in Oracle database [SPR-16478]

[spring-projects-issues]

Ewgenij Sokolovski opened SPR-16478 and commented

After upgrading the Spring framework from <4.2.2.RELEASE> to <5.0.2.RELEASE> it is not possible to call a stored procedure with SimpleJdbcCall if it is behind a synonym in an Oracle database. I did not find any possibility to switch handling of synonyms on, so the only workaround was to use JdbcTemplate where everything worked.

---

Affects: 4.3.14, 5.0.2

Reference URL: https://stackoverflow.com/questions/48619799/spring-simplejdbccall-and-oracle-synonyms

Issue Links:

• SimpleJdbcCall does not throw an Exception for a missing stored procedure if the name does not contain "." [SPR-13669] #18244 SimpleJdbcCall does not throw an Exception for a missing stored procedure if the name does not contain "."
• NPE in Spring-JDBC with Oracle and SimpleJdbcInsert [SPR-16495] #21038 NPE in Spring-JDBC with Oracle and SimpleJdbcInsert

Referenced from: commits fc93f99, 2654dbf

Backported to: 4.3.15

[spring-projects-issues]

Juergen Hoeller commented

Any chance you could test this against 4.3.14 as well? It'd be great to narrow the source of the regression a bit. I suspect it's between 4.3 and 5.0 since there were quite a few JDBC-related refactorings there, but it could also be a side effect of a change between 4.2.x and 4.3.x...

[spring-projects-issues]

Juergen Hoeller commented

In general, any hint to what might go wrong here would be much appreciated. Specifically, what kind of syntax are you using on SimpleJdbcCall versus your direct JdbcTemplate usage?

[spring-projects-issues]

Ewgenij Sokolovski commented

Hello. I tested with <4.3.14.RELEASE> just now. It does not work. I get the

Unable to determine the correct call signature - no procedure/function/signature for 'STORED_PROCEDURE_NAME'

exception. The syntax of my call is quite simple :-)

1. For SimpleJdbcCall it is

private SimpleJdbcCall jdbcCall = new SimpleJdbcCall(myDataSource).withCatalogName("PACKAGE_NAME").withProcedureName("STORED_PROCEDURE_NAME");
jdbcCall.execute();

2. For JdbcTemplate it is as described on Stackoverflow

private jdbcTemplate = new JdbcTemplate(myDataSource);
try{
  CallableStatement callableStatement = jdbcTemplate.getDataSource().getConnection().prepareCall("{call PACKAGE_NAME.STORED_PROCEDURE_NAME}");
  callableStatement.executeUpdate();
} catch(SQLException e)
{
...
}

With <4.2.2.RELEASE> everything is fine, I retested that also.

[spring-projects-issues]

Juergen Hoeller commented

This seems to be caused by #18244. I suppose we'll have to relax that check again.

[spring-projects-issues]

Juergen Hoeller commented

I've relaxed that check to just log a debug message on Oracle, assuming a non-exposed synonym.

Please give the upcoming 5.0.4.BUILD-SNAPSHOT a try and let me know whether it works for you! I'll backport the change to the 4.3.x branch ASAP.

[spring-projects-issues]

Ewgenij Sokolovski commented

That sounds great :-) Where can I get this 5.0.4.BUILD-SNAPSHOT?

I'll test it probably next week wednesday or thursday, since I leave for holiday now and will not have access to my project.

[spring-projects-issues]

Juergen Hoeller commented

The easiest way to get snapshot guidance is on https://projects.spring.io/spring-framework/, choosing 5.0.4 snapshot from the "Quick Start" dropdown box. Simply speaking, you just need to add the https://repo.spring.io/libs-snapshot repository to your Maven setup and choose "5.0.4.BUILD-SNAPSHOT" as your version.

It'd be great to have your feedback late next week at least. We are going to release 5.0.4 in the morning of Monday February 19th.

[spring-projects-issues]

Ewgenij Sokolovski commented

Hello, I tested with 5.0.4.BUILD-SNAPSHOT today. It worked out with synonyms! Thank you a lot for the prompt fix :-)