SimpleJdbcCall mdata lookup broken for overloaded procedures following, procedure, package, returns, number

amy

Hi,

Spring 2.5.2 can successfully call Oracle PLSQL procedures stored inside PLSQL packages, granted that you use the setCatalogueName() function.

However, whenever your PLSQL procedure is overloaded the metadata lookup returns a procedure that has a number of arguments equal to the sum of all arguments in all versions of the overloaded procedure:

I made a test package with these procs:Code:
PROCEDURE newPlan(p1 IN INTEGER);
PROCEDURE newPlan(p1 IN INTEGER, p2 IN INTEGER);
I get the following error message:

bad SQL grammar [{call CALLTEST.NEWPLAN(?, ?, ?)}];

And it doesn't help to use withoutProcedureColumnMetaDataAccess() and explicitly set types in the MapSqlParameterSource object.

Only workaround for me is to go and create proxy PLSQL procs for each of my overloaded procs and then just have them forward the call

Oh btw, I'm using Oracle jdbc 10.2 driver, Java 1.5

Also, my setup of SimpleJdbcCall looks like this:Code:
this.procTest = new SimpleJdbcCall(dataSource)        .withCatalogName(quot;CALLTESTquot;)        .withProcedureName(quot;NEWPLANquot;);
regards, erik

If you study the SimpleJdbcCalls API you will notice the declareParameters method. If you declare your parameters you want to use (either 1 or 2) then those take precedence over the ones retrieved by the MetaData. (This is also specified within the javadocs).Code:
this.procTest = new SimpleJdbcCall(dataSource)        .withCatalogName(quot;CALLTESTquot;)        .withProcedureName(quot;NEWPLANquot;).declareParameters(new SqlParameter(Types.INTEGER), new SqlParameter(Types.INTEGER));pileInternal(AbstractJdbcCall.java:281)
at org..jdbc.core.simple.AbstractJdbcC  all.compile(AbstractJdbcCall.java:259)
at org..jdbc.core.simple.AbstractJdbcC  all.checkCompiled(AbstractJdbcCall.java:318)
at org..jdbc.core.simple.AbstractJdbcC  all.doExecute(AbstractJdbcCall.java:334)
at org..jdbc.core.simple.SimpleJdbcCal  l.execute(SimpleJdbcCall.java:166)
at no.test.JdbcDirectTest.main(JdbcDirectTest.java:34  )
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ  e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native  MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De  legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.intellij.rt.execution.application.AppMain.main  (AppMain.java:90)

Process finished with exit code 1
Yes, the metadata lookup doesn't handle multiple signatures.  You would have to specify the parameters explicitly.Code:
SimpleJdbcCall call = new SimpleJdbcCall(ds)       .withCatalogName(quot;CALLTESTquot;)       .withProcedureName(quot;NEWPLANquot;)       .withoutProcedureColumnMetaDataAccess()       .declareParameters(       new SqlParameter(quot;p1quot;, Types.INTEGER),       new SqlParameter(quot;p2quot;, Types.INTEGER));
We could improve this in the future and try to handle multiple signatures - could you open a JIRA issue at secure/Dashboard.jspa

What version of Spring are you using - the detection of multiple signatures was improved in 2.5.3.  That is just the detection, not the actual handling of the signatures - you should see an exception saying multiple signatures found.

mons.dbcp.BasicDataSource@1bdc9d8]: name is 'Oracle'
1000   [           main] DEBUG c.support.SQLErrorCodesFactory  - SQL error codes for 'Oracle' found
1015   [           main] DEBUG rrorCodeSQLExceptionTranslator  - Unable to translate SQLException with Error code '17004', will now try the fallback translator
Exception in thread quot;mainquot; org..jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call IRIS_PROM.CALLTEST.NEWPLAN(?, ?, ?)}]; SQL state [null]; error code [17004]; Ugyldig kolonnetype; nested exception is java.sql.SQLException: Ugyldig kolonnetype
at org..jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
at org..jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
at org..jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:949)
at org..jdbc.core.JdbcTemplate.call(JdbcTemplate.java:982)
at org..jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:362)
at org..jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:336)
at org..jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:166)
at no.ica.sm.pepita.JdbcDirectTest.main(JdbcDirectTest.java:38)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: java.sql.SQLException: Ugyldig kolonnetype
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:286)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:10719)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:10300)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:10792)
at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:5729)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:163)
at org..jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:330)
at org..jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:124)
at org..jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:215)
at org..jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:927)
I have not been able to call a simple PROC having  boolean variable... strange
Any idea?
erik

Not strange  - Oracle JDBC doesn't support the use of boolean parameters when calling PL/SQL - see docs/cd/B...h.htm#i1005380

They recommend creating wrapper functions/procedures to convert from numeric 1/0 values to boolean true/false

aah, I feel #%amp;

I've experienced the same thing when using sqlplus.... But didn't think of the issue in jdbc context..

thanks for all your help

We've all been there  - I have created a JIRA issue for the original problem of the metadata processing not being able to detect the various signatures - browse/SPR-4701