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
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.