spring oracle-database stored-procedures plsql

Correct syntax to define parameters in a `StoredProcedure` Spring bean, fixing error ORA-06550

Why I am asking this

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.

What I am trying to do

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();

    }

}

Procedure as declared in the database

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;

Question, in short

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:

Is it necessary to match the argument names in the invocation of declareParameter?
Are the JDBC types expressed correctly in my code? I have tried switching from DECIMAL to NUMBER but same result. I looked at this table
Any advice on the exact parameter that is giving trouble?

Solution

I 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