Affects: Spring Boot 2.7.12, spring-jdbc 5.3.27
Hi! I have two oracle functions with same name, but different signatures:
FUNCTION getRolesShortName
(
login varchar2 := null
)
return sys_refcursor;
FUNCTION getRolesShortName
(
operatorID integer := null
)
return sys_refcursor;
and this code for call function:
public List<Role> getRoles(Integer operatorId) {
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withCatalogName("pkg_Operator")
.withFunctionName("getRolesShortName")
.declareParameters(
new SqlParameter("operatorId", Types.NUMERIC)
)
//.withoutProcedureColumnMetaDataAccess()
.returningResultSet("retCursor", BeanPropertyRowMapper.newInstance(Role.class));
return simpleJdbcCall.executeFunction(List.class, new MapSqlParameterSource()
.addValue("operatorId", operatorId));
}
Without withoutProcedureColumnMetaDataAccess I have error "Unable to determine the correct call signature - multiple signatures for getRolesShortName" With - "wrong number or types of arguments in call"
Trace log:
2023-06-01 17:56:06.753 INFO 17648 --- [o-8081-exec-136] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2023-06-01 17:56:06.755 INFO 17648 --- [o-8081-exec-136] o.s.web.servlet.DispatcherServlet : Completed initialization in 1 ms
2023-06-01 17:56:07.040 DEBUG 17648 --- [o-8081-exec-136] o.s.jdbc.core.simple.SimpleJdbcCall : Added declared parameter for [getRoles]: operatorId
2023-06-01 17:56:07.046 DEBUG 17648 --- [o-8081-exec-136] o.s.jdbc.core.simple.SimpleJdbcCall : Added row mapper for [getRoles]: sys_refcursor
2023-06-01 17:56:07.049 DEBUG 17648 --- [o-8081-exec-136] o.s.jdbc.core.simple.SimpleJdbcCall : JdbcCall call not compiled before execution - invoking compile
2023-06-01 17:56:07.142 DEBUG 17648 --- [o-8081-exec-136] o.s.j.c.m.CallMetaDataProviderFactory : Using org.springframework.jdbc.core.metadata.OracleCallMetaDataProvider
2023-06-01 17:56:07.142 DEBUG 17648 --- [o-8081-exec-136] o.s.j.c.metadata.CallMetaDataProvider : Retrieving meta-data for PKG_OPERATOR/SHEMA/GETROLES
2023-06-01 17:56:07.202 ERROR 17648 --- [o-8081-exec-136] o.s.b.w.servlet.support.ErrorPageFilter : Forwarding to error page from request [/v0/create] due to exception [Unable to determine the correct call signature - multiple signatures for 'GETROLES': found [PKG_OPERATOR.SHEMA.GETROLES, PKG_OPERATOR.SHEMA.GETROLES] procedures]
org.springframework.dao.InvalidDataAccessApiUsageException: Unable to determine the correct call signature - multiple signatures for 'GETROLES': found [PKG_OPERATOR.SHEMA.GETROLES, PKG_OPERATOR.SHEMA.GETROLES] procedures
2023-06-01 17:29:29.720 INFO 17648 --- [o-8081-exec-116] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2023-06-01 17:29:29.723 INFO 17648 --- [o-8081-exec-116] o.s.web.servlet.DispatcherServlet : Completed initialization in 1 ms
2023-06-01 17:29:30.007 DEBUG 17648 --- [o-8081-exec-116] o.s.jdbc.core.simple.SimpleJdbcCall : Added declared parameter for [getRolesShortName]: operatorId
2023-06-01 17:29:30.013 DEBUG 17648 --- [o-8081-exec-116] o.s.jdbc.core.simple.SimpleJdbcCall : Added row mapper for [getRolesShortName]: retCursor
2023-06-01 17:29:30.015 DEBUG 17648 --- [o-8081-exec-116] o.s.jdbc.core.simple.SimpleJdbcCall : JdbcCall call not compiled before execution - invoking compile
2023-06-01 17:29:30.111 DEBUG 17648 --- [o-8081-exec-116] o.s.j.c.m.CallMetaDataProviderFactory : Using org.springframework.jdbc.core.metadata.OracleCallMetaDataProvider
2023-06-01 17:29:30.119 DEBUG 17648 --- [o-8081-exec-116] o.s.j.core.metadata.CallMetaDataContext : Using declared out parameter 'retCursor' for function return value
2023-06-01 17:29:30.119 DEBUG 17648 --- [o-8081-exec-116] o.s.jdbc.core.simple.SimpleJdbcCall : Compiled stored procedure. Call string is [{? = call PKG_OPERATOR.GETROLESSHORTNAME(?)}]
2023-06-01 17:29:30.119 DEBUG 17648 --- [o-8081-exec-116] o.s.jdbc.core.simple.SimpleJdbcCall : SqlCall for function [getRolesShortName] compiled
2023-06-01 17:29:30.121 DEBUG 17648 --- [o-8081-exec-116] o.s.j.core.metadata.CallMetaDataContext : Matching [operatorId] with [operatorId]
2023-06-01 17:29:30.121 DEBUG 17648 --- [o-8081-exec-116] o.s.j.core.metadata.CallMetaDataContext : Found match for [operatorId]
2023-06-01 17:29:30.121 DEBUG 17648 --- [o-8081-exec-116] o.s.jdbc.core.simple.SimpleJdbcCall : The following parameters are used for call {? = call PKG_OPERATOR.GETROLESSHORTNAME(?)} with {operatorId=1076297}
2023-06-01 17:29:30.121 DEBUG 17648 --- [o-8081-exec-116] o.s.jdbc.core.simple.SimpleJdbcCall : 1: operatorId, SQL type 2, type name null, parameter class [org.springframework.jdbc.core.SqlParameter]
2023-06-01 17:29:30.121 DEBUG 17648 --- [o-8081-exec-116] o.s.jdbc.core.simple.SimpleJdbcCall : 2: retCursor, SQL type -10, type name null, parameter class [org.springframework.jdbc.core.SqlOutParameter]
2023-06-01 17:29:30.121 DEBUG 17648 --- [o-8081-exec-116] o.s.jdbc.core.JdbcTemplate : Calling stored procedure [{? = call PKG_OPERATOR.GETROLESSHORTNAME(?)}]
2023-06-01 17:29:32.500 ERROR 17648 --- [o-8081-exec-116] o.s.b.w.servlet.support.ErrorPageFilter : Forwarding to error page from request [/v0/create] due to exception [CallableStatementCallback; bad SQL grammar [{? = call PKG_OPERATOR.GETROLESSHORTNAME(?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 14:
PLS-00306: wrong number or types of arguments in call to 'GETROLESSHORTNAME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
]
Comment From: almirus
Solution is here:
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withCatalogName("pkg_Operator")
.withFunctionName("getRolesShortName")
.withoutProcedureColumnMetaDataAccess()
.returningResultSet("retCursor", BeanPropertyRowMapper.newInstance(Role.class))
.declareParameters(
new SqlOutParameter("retCursor", Types.REF_CURSOR),// <------!!!!!
new SqlParameter("operatorId", Types.NUMERIC)
);
Comment From: almirus
public List<Role> getRoles(Integer operatorId) {
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withCatalogName("pkg_Operator")
.withFunctionName("getRolesShortName")
.declareParameters(
new SqlParameter("operatorId", Types.NUMERIC)
)
.withoutProcedureColumnMetaDataAccess()
.returningResultSet("retCursor", BeanPropertyRowMapper.newInstance(Role.class));
return simpleJdbcCall.executeFunction(List.class, new MapSqlParameterSource()
.addValue("operatorId", operatorId));
}
returningResultSet adds call parameter to the end of declareParameters. And "retCursor" gets wrong index = 1 instead 0 The call looks like this: {?operatorId = call PKG_OPERATOR.GETROLESSHORTNAME(?retCursor) because of that Oracle throws an error ORA-06550