How to write Java that calls a package function? Making a slight change to working Java code

Issue

With the help provided in the response to my previous question:

Invalid Index trying to call a simple Oracle PROCEDURE from Java

I was able to call an Oracle package procedure that returns a single varchar2(1) output parameter but now I would like to change the procedure to a FUNCTION with the following SPEC that returns a CHAR:

FUNCTION is_running_in_production RETURN CHAR;

How do I modify the code below that called the earlier version of the procedure that returned a single output parameter to instead calll this procedure which calls a FUNCTION that returns a CHAR field?

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

public String isRunningInProduction() throws DaoException {
        
        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);

        }
                
        return inProduction;    
        
    }

Since but the jdbcTemplate.call procedure seems to require a paramList, I commented out the adding isProd to paramList and passed a paramList with zero parameters added. I also changed callableStatement.registerOutParameter to callableStatement.registerReturnParameter and changed the data type from Types.VARCHAR to Types.CHAR. Iif any of this is right, I'm not sure how I would extract the result to populate the return variable inProduction.

public String isRunningInProduction() throws DaoException {
        
        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);
                    callableStatement.registerReturnParameter(1, Types.CHAR);

                    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);
        }
        
        return inProduction;    
        
    }

I get the following error on the initialization of the resultMap object, before I even have to deal with getting the result.

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL []; SQL state [99999]; error code [17090]; operation not allowed; nested exception is java.sql.SQLException: operation not allowed

Solution

In order to invoke a function, I would suggest following a similar but different approach of the one you followed in the linked question based on jdbcTemplate.call.

For example, you could try using the execute method of JdbcTemplate and CallableStatementCreator, in a very similar way to in your example, but with more control of the output result:

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

try {
  String result = jdbcTemplate.<String>execute( new CallableStatementCreator() {
        public CallableStatement createCallableStatement(Connection connection)
          throws SQLException {
          CallableStatement cs = connection.prepareCall("{? = call FDS_APPS.FDS_USR_SEC_PKG2.is_running_in_production2}");
          // The first out parameter is the result of the function
          // Set the appropriate type
          cs.registerOutParameter(1, Types.VARCHAR);
          // Set the rest of the arguments, if required
          return cs;
        }
      },
    new CallableStatementCallback<String>() {
      public String doInCallableStatement(CallableStatement cs)  throws SQLException {
        cs.execute();
        String result = cs.getString(1);
        // The value eturned here is the one returned by the execute method
        return result;
      }
    }
  );

  System.out.printf("Result with CSC: '%s'", result);

} catch (Throwable t) {
  // Please, forgive me for this
  t.printStackTrace();
}

Or, simplified with lambda expressions:

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

try {
  String result = jdbcTemplate.<String>execute(connection -> {
    CallableStatement cs = connection.prepareCall("{? = call FDS_APPS.FDS_USR_SEC_PKG2.is_running_in_production2}");
    // The first out parameter is the result of the function
    // Set the appropriate type
    cs.registerOutParameter(1, Types.VARCHAR);
    // Set the rest of the arguments, if required
    return cs;
  },
    (CallableStatementCallback) cs -> {
      cs.execute();
      String result1 = cs.getString(1);
      // The value returned here is the one returned by the execute method
      return result1;
    }
  );

  System.out.printf("Result with CSC: '%s'", result);

} catch (Throwable t) {
  // Please, forgive me for this
  t.printStackTrace();
}

Please, note how the actual SQL expression is contructed:

{? = call FDS_APPS.FDS_USR_SEC_PKG2.is_running_in_production2}

This code can be greatly simplified using the SimpleJdbcCall class. Please, consider the following example:

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

try {
  SimpleJdbcCall isRunningInProductionFunction = new SimpleJdbcCall(jdbcTemplate)
    .withSchemaName("FDS_APPS")
    .withCatalogName("FDS_USR_SEC_PKG2")
    .withFunctionName("is_running_in_production2");

  // Indicate the return type. You can pass additional arguments if you need to
  String result = isRunningInProductionFunction.executeFunction(String.class);
  System.out.printf("Result as simple JDBC call: '%s'", result);
} catch (Throwable t) {
  // Please, forgive me for this
  t.printStackTrace();
}

Please, consider review the Spring Data Access documentation: it is plenty of examples and alternative solutions.

Answered By - jccampanero