Spring jdbc: simplejdbcall does not read metadata for parameters when executing stored procedure using synonym

I'm trying to execute a stored procedure using the simpleJDBCCall.excute (in) method, but it doesn't read the metadata when using a synonym for the procedure.

Here is the code.

SimpleJdbcCall optOutCall = new SimpleJdbcCall(dataSource)
                new SqlOutParameter("returnCode", Types.NUMERIC),
                new SqlParameter("product_id", Types.NUMERIC),
                new SqlParameter("user_id", Types.NUMERIC),
                new SqlParameter("email_address", Types.VARCHAR));
    long returnCode = 0;
    inputs.addValue("returnCode", returnCode);
    inputs.addValue("product_id", 1);
    inputs.addValue("user_id", 45673);
    inputs.addValue("email_address", "a.b@abc.com");



Here the procedure < ADD_ADDRESS "is in the USER package " in the USER_DATA schema . I created a synonym for the same package / procedure in a different schema called " USER_SCH ". when i execute the code using simpleJDBCCall.execute () method it doesn't read the metadata and gives the following execution.

    Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:PLS-00306: wrong number or types of arguments in call to 'ADD_ADDRESS'ORA-06550: line 1, column 7:PL/SQL: Statement ignored


Here are the log messages.

[07 Oct 2014 10:32:49,019] [DEBUG] [org.springframework.jdbc.core.simple.SimpleJdbcCall]: [JdbcCall call not compiled before execution - invoking compile]
[07 Oct 2014 10:32:49,019] [DEBUG] [org.springframework.jdbc.datasource.DataSourceUtils]: [Fetching JDBC Connection from DataSource]
[07 Oct 2014 10:32:49,020] [DEBUG] [org.springframework.jdbc.core.metadata.CallMetaDataProviderFactory]: [Using org.springframework.jdbc.core.metadata.OracleCallMetaDataProvider]
[07 Oct 2014 10:32:49,020] [DEBUG] [org.springframework.jdbc.core.metadata.CallMetaDataProvider]: [Retrieving metadata for USER/USER_SCH/ADD_ADDRESS]
[07 Oct 2014 10:32:49,028] [DEBUG] [org.springframework.jdbc.datasource.DataSourceUtils]: [Returning JDBC Connection to DataSource]
[07 Oct 2014 10:32:49,028] [DEBUG] [org.springframework.jdbc.core.simple.SimpleJdbcCall]: [Compiled stored procedure. Call string is [{call USER.ADD_ADDRESS()}]]
[07 Oct 2014 10:32:49,028] [DEBUG] [org.springframework.jdbc.core.simple.SimpleJdbcCall]: [SqlCall for procedure [ADD_ADDRESS] compiled]
[07 Oct 2014 10:32:49,028] [DEBUG] [org.springframework.jdbc.core.metadata.CallMetaDataContext]: [Matching [returnCode, product_id, user_id,email_address] with []]
[07 Oct 2014 10:32:49,028] [DEBUG] [org.springframework.jdbc.core.metadata.CallMetaDataContext]: [Found match for []]
[07 Oct 2014 10:32:49,028] [DEBUG] [org.springframework.jdbc.core.simple.SimpleJdbcCall]: [The following parameters are used for call {call USER.ADD_ADDRESS()} with: {}]
[07 Oct 2014 10:32:49,028] [DEBUG] [org.springframework.jdbc.core.JdbcTemplate]: [Calling stored procedure [{call USER.ADD_ADDRESS()}]]
[07 Oct 2014 10:32:49,028] [DEBUG] [org.springframework.jdbc.datasource.DataSourceUtils]: [Fetching JDBC Connection from DataSource]
[07 Oct 2014 10:32:49,034] [DEBUG] [org.springframework.jdbc.datasource.DataSourceUtils]: [Returning JDBC Connection to DataSource]
[07 Oct 2014 10:32:49,034] [DEBUG] [org.springframework.jdbc.support.SQLErrorCodesFactory]: [Looking up default SQLErrorCodes for DataSource [weblogic.jdbc.common.internal.RmiDataSource@2521ddb8]]
[07 Oct 2014 10:32:49,034] [DEBUG] [org.springframework.jdbc.support.SQLErrorCodesFactory]: [SQLErrorCodes found in cache for DataSource [weblogic.jdbc.common.internal.RmiDataSource@2521ddb8]]
[07 Oct 2014 10:32:49,035] [DEBUG] [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator]: [Unable to translate SQLException with Error code '6550', will now try the fallback translator]
[07 Oct 2014 10:32:49,035] [DEBUG] [org.springframework.jdbc.support.SQLStateSQLExceptionTranslator]: [Extracted SQL state class '65' from value '65000']
[07 Oct 2014 10:32:49,035] [ WARN] [gproducterr]: [Handler execution resulted in exception]
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call USER.ADD_ADDRESS()}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ADD_ADDRESS'ORA-06550: line 1, column :PL/SQL: Statement ignored


But if I execute the same code with schema name "USER_DATA" where this procedure "USER.ADD_ADDRESS" exists, then it correctly reads the metadata information and executes this procedure.

which I am using has execute permissions for the USER_SCH.USER.ADD_ADDRESS procedure.

what change do I need to make to read the metadata correctly when doing any procedure using synonyms?


source to share

2 answers

Add ".withoutProcedureColumnMetaDataAccess ();". It will stop spring to read the jdbc metadata and use the data you provided. He works.



You cannot create a synonym for a wrapped procedure / function. You can create synonyms for:

  • Object table or table
  • Viewing or presenting an object
  • Sequence
  • Stored procedure, function, or package
  • Materialized view
  • Java class schema object
  • Custom object type
  • Synonym

This way you can create a synonym for the package, but you still need to refer to procedures / functions / everything in the package by their declared name.

Link here

Share and enjoy.



All Articles