Spring JDBC Template - Oracle DB Function returns null

Question

I have my Oracle DB Stored Function as below:

CREATE OR REPLACE FUNCTION FN_EMP_CNT (EMP_ID NUMBER) RETURN NUMBER
IS
  OLD_COUNT NUMBER(5) DEFAULT 0;
  NEW_COUNT NUMBER(5) DEFAULT 0;
BEGIN

  SELECT
    COUNT(EMP_ID) INTO OLD_COUNT
  FROM
    OLD_DEPT
  WHERE
    EID = EMP_ID
    AND DEPT_STAT='Closed';
  SELECT
    COUNT(EMP_ID) INTO NEW_COUNT
  FROM
    NEW_DEPT
  WHERE
    EID = EMP_ID
    AND DEPT_STAT='Closed'
  RETURN (NEW_COUNT + OLD_COUNT);
END;

When I use the below sql query directly it returns the correct number as 2:

SELECT FN_EMP_CNT(123) FROM DUAL;

But when I use Spring JDBC Template for retrieving the data it returns null.

int noOfEmps = jdbcTemplate.queryForObject("SELECT FN_EMP_CNT(?) FROM DUAL", new Object[] { empID}, Integer.class);

are you shure that the program is in the same schema as the function? — watchme
I'm not an expert at this, but why are you passing an args array when you don't have any bind variables in your sql statement? Have you tried queryForObject("SELECT FN_EMP_CNT(123) FROM DUAL", Integer.class)? — kfinity
To troubleshoot further, I'd suggest changing the function to just return 2; and see if that works. Or try changing your query to "SELECT ? FROM DUAL" to make sure your bind variable works. — kfinity
@kfinity the problem was not in the SQL but in the parameters of queryForObjectsee update below... — Marmite Bomber

Marmite Bomber Marmite Bomber · Accepted Answer · 2018-03-26T17:29:37

The most probable cause is that you use a wrong order of parameters, see Javadoc queryForObject

queryForObject(java.lang.String sql, java.lang.Class<T> requiredType, java.lang.Object... args)
Query given SQL to create a prepared statement from SQL and a list of 
 arguments to bind to the query, expecting a result object.

So use first the required return type followed by the parameter

This works for my fine

sql = 'SELECT FN_EMP_CNT(?) FROM DUAL'
res = jdbcTemplate.queryForObject(sql, Integer.class, 4)

HTH

Spring JDBC Template - Oracle DB Function returns null

1 Answers