Issue in Calling PostgreSQL function which returns REF_CURSOR using SimpleJdbcCall

Viewed 26

I want to call a PostgreSQL function with put paramter as REF_CURSOR using SimpleJDBCCall. My function looks like below. I tried to call the function using the following JAVA code

CREATE OR REPLACE FUNCTION fnc_getdropdownlist(
p_reference_id numeric,
p_is_multiselect character,
p_source numeric)
RETURNS refcursor
   LANGUAGE 'plpgsql'
COST 100
VOLATILE 
AS $BODY$
DECLARE

    p_cursor_out   REFCURSOR;
    Lv_Errmsg      text;

BEGIN
    <-----function body----->
    RETURN p_cursor_out;
EXCEPTION
    WHEN OTHERS
    THEN RAISE;
END;

JAVA code

SimpleJdbcCall executor = new SimpleJdbcCall(jdbcTemplate)  
                .withFunctionName("fnc_getdropdownlist").withoutProcedureColumnMetaDataAccess()  
                .declareParameters(  
                        new SqlParameter("P_REFERENCE_ID", Types.INTEGER),  
                        new SqlParameter("P_IS_MULTISELECT", Types.VARCHAR),  
                        new SqlParameter("P_SOURCE", Types.INTEGER),  
                        new SqlOutParameter("results_cursor", Types.OTHER, new   GetDropDownListRowMapper()));  
        executor.compile();  
        SqlParameterSource param = new MapSqlParameterSource()  
                .addValue("P_REFERENCE_ID", 3)  
                .addValue("P_IS_MULTISELECT", "L")  
                .addValue("P_SOURCE", 7);  

        List<CodeDefinitionVO> simpleJdbcCallResult = executor.executeFunction(List.class,param);
        result = new LinkedHashMap<>();
            if(simpleJdbcCallResult !=null) {
                for (CodeDefinitionVO def : simpleJdbcCallResult) {
                    if (def.getId() == -1) {
                        result.put(null, def.getDescription());
                    } else {
                        result.put(def.getId(), def.getDescription());
                    }
                }

            }

Maven Dependency

<dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.3.5</version>
    </dependency>

Calling the function from postgreSQL console working fine but Running the above code I received an error message:

Caused by: org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call fnc_getdropdownlist(?, ?, ?)}]; SQL state [34000]; error code [0]; ERROR: cursor __"<cursor_name>" does not exist;__ nested exception is org.postgresql.util.PSQLException: ERROR: cursor "<cursor_name>" does not exist
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1542)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1206)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1245)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:412)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:372)
at org.springframework.jdbc.core.simple.SimpleJdbcCall.executeFunction(SimpleJdbcCall.java:165)
at com.ubs.corc.msct.spring.Hikari.main(Hikari.java:75)

Caused by: org.postgresql.util.PSQLException: ERROR: cursor "<cursor_name>" does not exist at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291) at org.postgresql.jdbc.PgConnection.execSQLQuery(PgConnection.java:476) at org.postgresql.jdbc.PgResultSet.internalGetObject(PgResultSet.java:288) at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:2936) at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:130) at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:156) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java) at org.springframework.jdbc.core.JdbcTemplate.lambda$call$6(JdbcTemplate.java:1246) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1190)

Is there a way to call the PostreSQL function which is returning a REF_CURSOR using SimpleJDBCCall? Thanks in advance

0 Answers
Related