Menu
I am trying to execute a stored procedure using simpleJDBCCall.excute(in) method but it doesn't read the metadata in case of using synonym for the procedure.
Here is the code.
SimpleJdbcCall optOutCall = new SimpleJdbcCall(dataSource)
.withSchemaName("USER_SCH")
.withCatalogName("USER")
.withProcedureName(ADD_ADDRESS)
.declareParameters(
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", "[email protected]");
optOutCall.execute(inputs);
Here "ADD_ADDRESS" procedure exist under "USER" package in a schema called "USER_DATA". I have created a synonym for the same package/procedure in another schema called "USER_SCH". when i execute the code using simpleJDBCCall.execute() method it doesn't read the metatadata and throws the following execption.
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 reads the metadata information correctly and executes the procedure.
user that i am using has the execute permissions for "USER_SCH.USER.ADD_ADDRESS" procedure.
what change do i need to make to read the metadata correctly when executing any procedure using synonyms??
659
2.2. Following is a simple example to call Stored Procedure using Spring SimpleJdbcCall. To initialize SimpleJdbcCall , you need to provide JdbcTemplate or DataSource instance. Since IN and OUT parameters auto detected by SimpleJdbcCall , declaring parameters is optional, so that commented out in following program.
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource). withProcedureName("getRecord"); SqlParameterSource in = new MapSqlParameterSource(). addValue("in_id", id); Map<String, Object> out = jdbcCall. execute(in); Student student = new Student(); student.
We can execute parameterized query using Spring JdbcTemplate by the help of execute() method of JdbcTemplate class.
Add ".withoutProcedureColumnMetaDataAccess();". It will stop spring to read jdbc metadata and will use the details provided by you. It works.
150
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
