After upgrade of Oracle JDBC driver in a Spring Boot application from 23.5.0.24.07 to 23.7.0.25.01, all stored procedure invocations in Spring Boot (via StoredProcedure beans) ceased to work because of
2025-07-29 17:58:16.664 DEBUG 160 --- [nio-8080-exec-1] [6888ef9719a877a87c75f7ef369cb6a1-ad56af7e86f8b2d1] n.t.d.l.l.SLF4JQueryLoggingListener : {"name":"mdb", "connection":49, "time":13, "success":false, "type":"Callable", "batch":false, "querySize":1, "batchSize":0, "query":["{call MASTERDATA.GETACCOUNTFROMIBAN(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}"], "params":[{"1":"[REDACTED]","2":"OUTPUT(VARCHAR[12])","3":"OUTPUT(VARCHAR[12])","4":"OUTPUT(VARCHAR[12])","5":"OUTPUT(VARCHAR[12])","6":"OUTPUT(VARCHAR[12])","7":"OUTPUT(VARCHAR[12])","8":"OUTPUT(VARCHAR[12])","9":"OUTPUT(VARCHAR[12])","10":"OUTPUT(VARCHAR[12])","11":"OUTPUT(VARCHAR[12])","12":"OUTPUT(VARCHAR[12])","13":"OUTPUT(VARCHAR[12])","14":"OUTPUT(DECIMAL[3])","15":"OUTPUT(DECIMAL[3])","16":"false","17":"1","18":"true","19":"false"}]}
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call MASTERDATA.GETACCOUNTFROMIBAN(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:112)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1191)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1230)
at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:140)
.....
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
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:723)
... 644 common frames omitted
It used to work in the past, until today.
In my application, I wrap stored procedure calls in dedicated beans that extend org.springframework.jdbc.object.StoredProcedure
Example Spring bean
@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();
}
}
create PACKAGE MASTERDATA AS
PROCEDURE GETACCOUNTFROMIBAN(I_IBAN IN VARCHAR2,
O_RETCODE OUT VARCHAR2,
O_MSGCODE OUT VARCHAR2,
O_ISTITUTO OUT VARCHAR2,
O_FILIALE OUT VARCHAR2,
O_ACCNTTYPE OUT VARCHAR2,
O_ACCNTNR OUT VARCHAR2,
O_IBAN_REALE OUT VARCHAR2,
O_DISPOSIZIONE OUT VARCHAR2,
O_VALIDATION_STATUS OUT VARCHAR2,
O_VALIDATION_SOURCE OUT VARCHAR2,
O_POSTVAL_SOURCE OUT VARCHAR2,
O_POSTVAL_TRACE OUT VARCHAR2,
O_MASTERDATA_LAPSE OUT NUMBER,
O_FAILBACK_LAPSE OUT NUMBER,
I_FAILBACK_ENABLED IN BOOLEAN DEFAULT TRUE,
I_ERROR_RETRY IN NUMBER DEFAULT 1,
I_CACHE_ENABLED IN BOOLEAN DEFAULT TRUE,
I_DEBUG_ENABLED IN BOOLEAN DEFAULT FALSE)
PARALLEL_ENABLE DETERMINISTIC;
As I said, this problem started to occur after Oracle driver upgrade (as result of Spring Boot upgrade from 3.4.8 to 3.5.4, because of transitive dependencies).
Investigation led me to the declareParameter() invocations. I tried to change the parameter names in the Java class to match exactly the names declared on the package, but did not work.
Then I noticed the SQL types
My question becomes:
declareParameter?DECIMAL to NUMBER but same result. I looked at this tableI tested here against a 19C and a 23ai with same ojdbc version as you, but in straight JDBC: it fails under 19c but succeeds in 23ai
create or replace procedure test_booleani (p_inbool IN INTEGER, p_outbool OUT INTEGER)
IS
BEGIN
p_outbool := p_inbool ;
END ;
/
create or replace procedure test_booleanb (p_inbool IN BOOLEAN, p_outbool OUT BOOLEAN)
IS
BEGIN
p_outbool := p_inbool ;
END ;
/
public static void testBooleanStoredProcedure(final Connection conn) throws SQLException {
try (final CallableStatement cs = conn.prepareCall("{call test_booleani(?, ?)}")) {
cs.registerOutParameter("p_outbool", OracleTypes.INTEGER);
cs.setInt("p_inbool", Integer.valueOf(1));
cs.execute();
System.out.println("test_booleani p_outbool: " + cs.getInt("p_outbool"));
}
try (final CallableStatement cs = conn.prepareCall("{call test_booleanb(?, ?)}")) {
cs.registerOutParameter("p_outbool", OracleTypes.BOOLEAN);
cs.setBoolean("p_inbool", Boolean.TRUE);
cs.execute();
System.out.println("test_booleanb p_outbool: " + cs.getInt("p_outbool"));
}
}
in 19c:
test_booleani p_outbool: 1
Exception in thread "main" java.sql.SQLException: ORA-06550: Ligne 1, colonne 7 :
PLS-00306: numéro ou types d'arguments erronés dans appel à 'TEST_BOOLEANB'
ORA-06550: Ligne 1, colonne 7 :
PL/SQL: Statement ignored
in 23ai:
test_booleani p_outbool: 1
test_booleanb p_outbool: 1