Invalid Index trying to call a simple Oracle PROCEDURE from Java

Question

The following SQL block successfully displays "Y" or "N" when called based upon whether or not the procedure is running against production database or not.

DECLARE
  v_is_prod VARCHAR2(1);
BEGIN

  FDS_APPS.FDS_USR_SEC_PKG2.IS_RUNNING_IN_PRODUCTION2(v_is_prod);
  dbms_output.put_line('v_is_prod=' || v_is_prod);
END;

I am trying to call this procedure from Java (this is my first ever call). For this question, please disregard that this should probably be a function that returns a char and not a single-output varchar2(1) parameter. I'm taking baby steps.

This is my Java code:

public String isRunningInProduction() throws DaoException {
        
        LoggerUtil.info("Start calling [" + IS_RUNNING_IN_PRODUCTION + "]::", LOGGER);
        String inProduction = "";
        
        try {

            SqlOutParameter isProd = new SqlOutParameter("v_is_prod", OracleTypes.VARCHAR);

            List<SqlParameter> paramList = new ArrayList<SqlParameter>();
            paramList.add(isProd); 
            
            Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {

                public OracleCallableStatement createCallableStatement(Connection connection) throws SQLException {

                    OracleCallableStatement callableStatement = (OracleCallableStatement) connection
                            .prepareCall(IS_RUNNING_IN_PRODUCTION);
                                        
                    callableStatement.registerOutParameter(1, Types.VARCHAR);
                    return callableStatement;
                }
            }, paramList);

            inProduction = (String)resultMap.get("v_is_prod");
                                    
        } catch (Exception e) {
            LOGGER.error("Error while determining if running in prod or not, PROC_NAME::[" + IS_RUNNING_IN_PRODUCTION + "]," + e);
            throw new DaoException("Error while retreiving " + IS_RUNNING_IN_PRODUCTION + e);
        }
        LoggerUtil.info("Finished calling [" + IS_RUNNING_IN_PRODUCTION + "]::", LOGGER);
        
        return inProduction;    
        
    }

I get this error:

org.springframework.jdbc.InvalidResultSetAccessException: CallableStatementCallback; invalid ResultSet access for SQL []; nested exception is java.sql.SQLException: Invalid column index

Apparantly, registerOutParameter is 1-based, so that callableStatement doesn't appear to be the issue, the first parameter has an index of 1.

The following const is defined elsewhere:

public static final String IS_RUNNING_IN_PRODUCTION = "FDS_APPS.FDS_USR_SEC_PKG2.is_running_in_production2";

Any suggestions on what my issue may be?

Answer 1

The problem is that you need to indicate in your statement that an argument is required, like this:

public static final String IS_RUNNING_IN_PRODUCTION = "FDS_APPS.FDS_USR_SEC_PKG2.is_running_in_production2(?);";

Usually the statement is wrapped in a call invocation:

public static final String IS_RUNNING_IN_PRODUCTION = "{call FDS_APPS.FDS_USR_SEC_PKG2.is_running_in_production2(?)}";

Please, note the inclusion of the parenthesis and the ? character.

In Java and JDBC, you need to provide a placeholder, using the ? character, for every parameter required in your SQL statement, no matter whether if it is IN or OUT.

As you don't provide such placeholder in your example, Spring is complaining because you are trying to register an out parameter with index 1- you are right, the first column index is 1 -, but there is no placeholder for that parameter in your SQL statement.

Please, for further examples, consider for instance review this article, it could be of help.