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