{"record":{"id":"16262657c60fc917","repo":"hibernate/hibernate-orm","slug":"postgresql-supports-only-one-ref-cursor-parameter","errorCode":null,"errorMessage":"PostgreSQL supports only one REF_CURSOR parameter, but multiple were registered","messagePattern":"PostgreSQL supports only one REF_CURSOR parameter, but multiple were registered","errorType":"exception","errorClass":"HibernateException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/procedure/internal/PostgreSQLCallableStatementSupport.java","lineNumber":147,"sourceCode":"\t\telse {\n\t\t\tjdbcParameterOffset = 1;\n\t\t\tstartIndex = 0;\n\t\t\tcallMode = CallMode.CALL;\n\t\t}\n\n\t\tfinal StringBuilder buffer = new StringBuilder( callMode.start.length() + callMode.end.length() + procedureName.length() + paramStringSizeEstimate )\n\t\t\t\t.append( callMode.start );\n\t\tbuffer.append( procedureName );\n\n\t\tif ( startIndex == registrations.size() ) {\n\t\t\tbuffer.append( '(' );\n\t\t}\n\t\telse {\n\t\t\tchar sep = '(';\n\t\t\tfor ( int i = startIndex; i < registrations.size(); i++ ) {\n\t\t\t\tfinal ProcedureParameterImplementor<?> parameter = registrations.get( i );\n\t\t\t\tif ( !supportsProcedures && parameter.getMode() == ParameterMode.REF_CURSOR ) {\n\t\t\t\t\tthrow new HibernateException(\n\t\t\t\t\t\t\t\"PostgreSQL supports only one REF_CURSOR parameter, but multiple were registered\" );\n\t\t\t\t}\n\t\t\t\tbuffer.append( sep );\n\t\t\t\tfinal JdbcCallParameterRegistration registration = parameter.toJdbcParameterRegistration(\n\t\t\t\t\t\ti + jdbcParameterOffset,\n\t\t\t\t\t\tprocedureCall\n\t\t\t\t);\n\t\t\t\tfinal OutputableType<?> type = registration.getParameterType();\n\t\t\t\tfinal String castType;\n\t\t\t\tif ( parameter.getName() != null ) {\n\t\t\t\t\tbuffer.append( parameter.getName() ).append( \" => \" );\n\t\t\t\t}\n\t\t\t\tif ( type != null\n\t\t\t\t\t&& type.getJdbcType() instanceof AbstractPostgreSQLStructJdbcType structJdbcType ) {\n\t\t\t\t\t// We have to cast struct type parameters so that PostgreSQL understands nulls\n\t\t\t\t\tcastType = structJdbcType.getStructTypeName();\n\t\t\t\t\tbuffer.append( \"cast(\" );\n\t\t\t\t}","sourceCodeStart":129,"sourceCodeEnd":165,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/procedure/internal/PostgreSQLCallableStatementSupport.java#L129-L165","documentation":"When rendering function-style calls on PostgreSQL, PostgreSQLCallableStatementSupport iterates remaining parameter registrations and rejects any additional ParameterMode.REF_CURSOR beyond the special first-position one, because a PostgreSQL function call cannot materialize multiple refcursor results in one callable statement. The check is explicit (supportsProcedures == false branch) and throws HibernateException.","triggerScenarios":"Registering two or more REF_CURSOR parameters, e.g. Oracle-style proc.registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR) and again at position 2, on PostgreSQL before procedure support applies.","commonSituations":"Direct ports of Oracle reporting packages that return several cursors (header + lines pattern); cross-dialect DAOs written against Oracle and pointed at PostgreSQL.","solutions":["Collapse to one cursor returning a composite/RECORD row type that carries what the multiple cursors carried.","Return TABLE(...) / SETOF and read results with getResultList(), or issue one call per cursor.","If multiple refcursors are unavoidable, use native SQL with explicit cursors (BEGIN; SELECT f(); FETCH ALL IN \"cur1\"; COMMIT;) instead of the procedure API."],"exampleFix":"-- before: function returning two refcursors (Oracle style)\nCREATE FUNCTION order_report(uid bigint) RETURNS refcursor AS ...\n\n-- after: single cursor of a composite type\nCREATE TYPE order_line AS (id bigint, qty int);\nCREATE FUNCTION order_report(uid bigint)\n    RETURNS TABLE(order_id bigint, lines order_line[]) AS $$ ... $$ LANGUAGE plpgsql;","handlingStrategy":"validation","validationCode":"long refCursorCount = proc.getParameters().stream()\n        .filter( p -> p.getMode() == ParameterMode.REF_CURSOR )\n        .count();\nif ( refCursorCount > 1 ) {\n    throw new IllegalArgumentException(\n            \"PostgreSQL supports one REF_CURSOR per call; restructure to a single cursor or multiple calls\" );\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Design PostgreSQL functions to return a single cursor/SETOF result, not multiple cursors.","Add a dialect-aware build check that rejects multi-cursor registrations when targeting PostgreSQL.","Keep Oracle-specific multi-cursor procedures behind Oracle-only code paths."],"tags":["hibernate","postgresql","ref-cursor","stored-procedure","multi-cursor"],"backgroundTag":"postgres-ref-cursor-limitations","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}