I would like to report a potential bug/regression occurred after upgrading Spring Boot (and its dependencies) to 3.5.x from 3.4.x. I know that the problem is not in Boot itself, maybe in a subproject or in the change log.
I subclass the StoredProcedure class in order to invoke stored procedures
@Repository
@Transactional(propagation = Propagation.MANDATORY, transactionManager = "mdbTransactionManager")
public class GetAccountFromIbanProcedure extends StoredProcedure {
@Autowired
protected GetAccountFromIbanProcedure(@Qualifier("mdbJdbcTemplate") JdbcTemplate jdbcTemplate) {
super(jdbcTemplate, "MASTERDATA.GETACCOUNTFROMIBAN");
}
@Override
public void afterPropertiesSet() {
declareParameter(new SqlParameter("iban", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("returnCode", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("msgCode", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("institution", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("branchCode", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("accountType", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("accountNumber", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("realIban", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("disposizione", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("validationStatus", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("validationSource", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("postvalSource", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("postvalTrace", VARCHAR.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("masterdataLapse", DECIMAL.getVendorTypeNumber()));
declareParameter(new SqlOutParameter("failbackLapse", DECIMAL.getVendorTypeNumber()));
declareParameter(new SqlParameter("failbackEnabled", BOOLEAN.getVendorTypeNumber()));
declareParameter(new SqlParameter("errorRetry", DECIMAL.getVendorTypeNumber()));
declareParameter(new SqlParameter("cacheEnabled", BOOLEAN.getVendorTypeNumber()));
declareParameter(new SqlParameter("debugEnabled", BOOLEAN.getVendorTypeNumber()));
super.afterPropertiesSet();
}
public AccountFromIbanDto execute(String iban) {
var out = execute(Map.of("iban", iban,
"failbackEnabled", false,
"errorRetry", 1,
"cacheEnabled", true,
"debugEnabled", false
));
AccountFromIbanDto.AccountFromIbanDtoBuilder accountFromIbanDtoBuilder = AccountFromIbanDto.builder()
.iban(iban)
.returnCode((String) out.get("returnCode"))
.msgCode((String) out.get("msgCode"))
.institution((String) out.get("institution"))
.branchCode((String) out.get("branchCode"))
.accountType((String) out.get("accountType"))
.accountNumber((String) out.get("accountNumber"))
.realIban((String) out.get("realIban"))
.disposizione((String) out.get("disposizione"))
.validationStatus((String) out.get("validationStatus"))
.validationSource((String) out.get("validationSource"))
.postvalSource((String) out.get("postvalSource"))
.postvalTrace((String) out.get("postvalTrace"));
if (out.get("masterdataLapse") != null) {
accountFromIbanDtoBuilder
.masterdataLapse(((BigDecimal) out.get("masterdataLapse")).intValue());
}
if (out.get("failbackLapse") != null) {
accountFromIbanDtoBuilder
.fallbackLapse(((BigDecimal) out.get("failbackLapse")).intValue());
}
return accountFromIbanDtoBuilder.build();
}
}
Using Spring Boot 3.4.x, up to 3.4.8, the above code yields the desired results. From 3.5, instead, I consistently experience the following especially with all procedures
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETACCOUNTFROMIBAN'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETACCOUNTFROMIBAN'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETACCOUNTFROMIBAN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Note that, in reality, the argument name doesn't perfectly match the name declared at stored procedure level
declare
I_IBAN VARCHAR2(4000) := '';
O_RETCODE VARCHAR2(4000);
O_MSGCODE VARCHAR2(4000);
O_ISTITUTO VARCHAR2(4000);
O_FILIALE VARCHAR2(4000);
O_ACCNTTYPE VARCHAR2(4000);
O_ACCNTNR VARCHAR2(4000);
O_IBAN_REALE VARCHAR2(4000);
O_DISPOSIZIONE VARCHAR2(4000);
O_VALIDATION_STATUS VARCHAR2(4000);
O_VALIDATION_SOURCE VARCHAR2(4000);
O_POSTVAL_SOURCE VARCHAR2(4000);
O_POSTVAL_TRACE VARCHAR2(4000);
O_MASTERDATA_LAPSE NUMBER;
O_FAILBACK_LAPSE NUMBER;
I_FAILBACK_ENABLED BOOLEAN := null;
I_ERROR_RETRY NUMBER := 0;
I_CACHE_ENABLED BOOLEAN := null;
I_DEBUG_ENABLED BOOLEAN := null;
The point for this issue is: was I supposed to know that from 3.4.8 to 3.5.x Spring JDBC starts to validate exact parameter name correspondence with the stored procedure? Or is it a side effect of something else? Since the error is generated at Oracle driver level, I wonder, for example, if the upgrade of the Oracle driver causes this behaviour indirectly.
Thanks for the help, I thought I reviewed the change log enough
Comment From: snicoll
Since the error is generated at Oracle driver level, I wonder, for example, if the upgrade of the Oracle driver causes this behaviour indirectly.
I don't know but, as you've alluded yourself, this isn't the right place to ask. You haven't say which version of Spring Boot 3.5 is triggering the issue. If the Oracle driver is different, downgrading it should help you figure out if it is the culprit. If that doesn't help, it could be a regression in Spring Framework, which isn't handled here.
If downgrading the Oracle driver doesn't help, please create a small sample and open a Spring Framework issue.