Spring Boot调用带IN/OUT参数的Oracle存储过程时抛出invalidDataAccessApiUsageException异常：OUT/INOUT参数不可用: ent_mensaje

Hey there, let's work through that invalidDataAccessApiUsageException you're getting when trying to retrieve the ent_mensaje INOUT parameter from your Oracle stored procedure in Spring Boot. I’ve looked over your code, and here are the key issues and fixes to get this working:

1. Remove the unnecessary resultClasses configuration #

Your stored procedure returns data exclusively via IN/OUT parameters, not a result set of entities. But in your @NamedStoredProcedureQuery, you’ve set resultClasses = DTOAnularAdicionSIF.class—this confuses JPA, because it tries to map a non-existent result set to your entity, which disrupts the parameter handling logic and makes JPA unable to recognize your output parameters.

Fix: Delete the resultClasses attribute entirely. Since you’re manually fetching output parameters in your generic execution method, you don’t need this mapping. Here’s the corrected query annotation:

@NamedStoredProcedureQuery(
    name = "Valores.AnularAdicionFondoSif", 
    procedureName = "FON.FON_ANULA_PROGRAMACION", 
    parameters = {
        @StoredProcedureParameter(name = "ent_numero_operacion", type = BigDecimal.class, mode = ParameterMode.IN),
        @StoredProcedureParameter(name = "ent_numero_fisico", type = String.class, mode = ParameterMode.IN),
        @StoredProcedureParameter(name = "ent_mensaje", type = String.class, mode = ParameterMode.INOUT),
        @StoredProcedureParameter(name = "ent_canal_origen", type = String.class, mode = ParameterMode.INOUT),
        @StoredProcedureParameter(name = "ent_senal_proceso", type = String.class, mode = ParameterMode.INOUT),
        @StoredProcedureParameter(name = "ent_senal_commit", type = String.class, mode = ParameterMode.INOUT),
        @StoredProcedureParameter(name = "ent_sw_pos", type = BigDecimal.class, mode = ParameterMode.INOUT)
    }
)

2. Fix parameter name case sensitivity #

Oracle converts all identifiers (including stored procedure parameter names) to uppercase by default. If your JPA setup is case-sensitive, using lowercase ent_mensaje in your code won’t match the uppercase ENT_MENSAJE that Oracle recognizes.

Fix options:

• Use uppercase parameter names everywhere in your annotations and code (e.g., ENT_MENSAJE instead of ent_mensaje);
• Add this Spring Boot configuration to make Hibernate preserve your specified case:

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

3. Fix bugs in your generic stored procedure execution method #

Your runNamedStoredProcedure has a few issues that can break parameter handling:

• When a parameter is null, you’re setting it to an empty string ""—this causes type conversion errors for numeric parameters like ent_sw_pos, which can prevent the stored procedure from executing properly and initializing output parameters;
• You’re unnecessarily including fields from the parent class, which adds unnecessary complexity;
• There’s no error handling for missing parameters.

Fixed version of the method:

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
import java.lang.reflect.Field;
import javax.persistence.EntityManager;
import javax.persistence.StoredProcedureQuery;

public class YourBaseRepository<E> {
    private static final Logger log = LoggerFactory.getLogger(YourBaseRepository.class);
    private final EntityManager em;

    // Constructor to inject EntityManager
    public YourBaseRepository(EntityManager em) {
        this.em = em;
    }

    public ResponseProcedure<E> runNamedStoredProcedure(String nameProcedure, Object params) throws IllegalAccessException {
        StoredProcedureQuery storedProcedureQuery = em.createNamedStoredProcedureQuery(nameProcedure);
        Map<String, Object> output = new HashMap<>();
        Class<?> objectClass = params.getClass();
        Field[] allFields = objectClass.getDeclaredFields();
        
        // Handle IN/INOUT parameters with proper null handling per type
        for (Field field : allFields) {
            field.setAccessible(true);
            if (field.isAnnotationPresent(Params.class)) {
                Params param = field.getAnnotation(Params.class);
                Object value = field.get(params);
                
                if (param.direction() == Params.Direction.IN || param.direction() == Params.Direction.INOUT) {
                    if (value == null) {
                        // Set type-appropriate default values instead of a universal empty string
                        if (field.getType() == String.class) {
                            storedProcedureQuery.setParameter(param.name(), "");
                        } else if (field.getType() == BigDecimal.class) {
                            storedProcedureQuery.setParameter(param.name(), BigDecimal.ZERO);
                        } else {
                            storedProcedureQuery.setParameter(param.name(), null);
                        }
                    } else {
                        storedProcedureQuery.setParameter(param.name(), value);
                    }
                }
            }
        }
        
        // Execute the procedure
        storedProcedureQuery.execute();
        
        // Fetch OUT/INOUT parameters with error handling
        for (Field field : allFields) {
            field.setAccessible(true);
            if (field.isAnnotationPresent(Params.class)) {
                Params param = field.getAnnotation(Params.class);
                if (param.direction() == Params.Direction.OUT || param.direction() == Params.Direction.INOUT) {
                    try {
                        Object outputValue = storedProcedureQuery.getOutputParameterValue(param.name());
                        output.put(param.name(), outputValue);
                    } catch (IllegalArgumentException e) {
                        // Log the issue instead of crashing the whole method
                        log.warn("Failed to retrieve output parameter: {}", param.name(), e);
                        output.put(param.name(), null);
                    }
                }
            }
        }
        
        // Return empty list since your procedure doesn't return a result set
        return new ResponseProcedure<>(Collections.emptyList(), output);
    }
}

4. Verify stored procedure permissions and existence #

Double-check that your application’s database user has EXECUTE permissions on FON.FON_ANULA_PROGRAMACION, and that the procedure actually exists in the FON schema. You can verify this with a quick SQL query:

SELECT * FROM ALL_PROCEDURES WHERE PROCEDURE_NAME = 'FON_ANULA_PROGRAMACION' AND OWNER = 'FON';

Final Notes #

The most likely culprit here is the incorrect resultClasses configuration—start by removing that, then check the parameter case sensitivity. Those two fixes alone should resolve the "OUT/INOUT Parameter is not available" error. If you still run into issues, apply the fixes to your generic execution method to ensure proper parameter handling.

内容的提问来源于stack exchange，提问作者Daniel Fernández