My Oracle Support Banner

PL/SQL Function Returning Refcursor Produces Error In BI Publisher Data Set When A Parameter Is Used (Doc ID 2473317.1)

Last updated on AUGUST 20, 2019

Applies to:

BI Publisher (formerly XML Publisher) - Version 12.2 to 12.2 [Release 12.2]
Information in this document applies to any platform.

Goal

12.2 version, Enterprise : Datamodel
PL/SQL function returning refcursor produces error in BI Publisher data set when a parameter is used.

Test case to reproduce error:

Create a PL/SQL function that returns a ref cursor;

  create or replace function fn_refcursor(p_number in number)
  return sys_refcursor is
  type t_refcursor is ref cursor;
  l_refcursor t_refcursor;
  begin
  open l_refcursor for
  select dummy
  from dual
  where 1 = p_number;
  return l_refcursor;
  end fn_refcursor;

Test the function in a BIP data set as Standard SQL, successfully viewed the data;

  select fn_refcursor(p_number => 1) from dual

Replace the literal with a valid parameter in the BIP function call, error viewing the data;

  select fn_refcursor(p_number => :p_number) from dual
  
  ERROR: Missing IN or OUT parameter at index:: 2

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.