I am trying to read a value from oracle anonymous block using JdbcTemplate-CodePudding

I am trying to read a value from oracle anonymous block using JdbcTemplate. Here is my java code:

getJdbcTemplate().queryForObject(sql, Boolean.class);

And here is the sql:

DECLARE
    CRS                SYS_REFCURSOR;

BEGIN

    OPEN CRS FOR SELECT CASE
                            WHEN
                                      1 > 0
                                THEN 1
                            ELSE 0
                            END
                 FROM DUAL;
END;

I am receiving this error:

 SQL state [99999]; error code [17166]; Cannot perform fetch on a PLSQL statement: next; nested exception is java.sql.SQLException: Cannot perform fetch on a PLSQL statement: next

CodePudding user response：

public <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException takes an SQL query that will generate a result set with a single row and constructs an object of the required type using that row from the returned result set.

The code:

DECLARE
  CRS SYS_REFCURSOR;
BEGIN
  OPEN CRS FOR
    SELECT CASE WHEN 1 > 0 THEN 1 ELSE 0 END FROM DUAL;
END;

Opens a cursor that would contain a result set with a single row; however, the cursor is held as a local variable within the PL/SQL anonymous block and is never "returned" to the application code that calls the PL/SQL.

The traditional way would be to use SQL without the PL/SQL wrapper:

String sql = "SELECT CASE WHEN 1 > 0 THEN 1 ELSE 0 END FROM DUAL";
getJdbcTemplate().queryForObject(sql, Boolean.class);

You could try using DBMS_SQL.RETURN_RESULT from the PL/SQL block but that is only available from Oracle 12 and it does not have the same level of support that traditional SQL queries do so it may not work:

DECLARE
  crs SYS_REFCURSOR;
BEGIN
  OPEN crs FOR
    SELECT CASE WHEN 1 > 0 THEN 1 ELSE 0 END FROM DUAL;
  DBMS_SQL.RETURN_RESULT(crs);
END;