Skip to Main Content
  • Questions
  • Missing values using pipelined functions and refcursor

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Adriana.

Asked: April 13, 2018 - 7:58 am UTC

Last updated: April 15, 2018 - 3:50 am UTC

Version: 11c

Viewed 1000+ times

You Asked

Hi,
We are using a several pipelined functions to return values to an AIP based on plan number paramerer (table type). The result of these functions are combined (using table(function)) and returned in an open refcursor to the webservices (API).
In total this refcursor has 90 columns.
So the issue is: when I run the AIP passing 657 plan numbers or less it works fine returning all values as expected. However, when I pass a list of 658 or more, certain elements suddenly stop appearing even though I know that these elements have such data.
A colleague spent a long time splitting the functions that were returning a big number of columns, but the result was that we could get values for the missing columns, but now new columns that had values before suddenly stopped appearing. Basically the issue was moved to other columns. Any idea why this is happening and potentially solution? Is there any limit of columns or data size that can be returned by a refcursor? Any workaround? Thanks for helping.

and Connor said...

I don't know of any limitations.

Just testing with a simple ref cursor, I got to 2000 with no problems

SQL> variable rc refcursor
SQL>
SQL> begin
  2    open :rc for
  3      select
  4  1 c1,
  5  2 c2,
  6  3 c3,
  7  4 c4,
  8  5 c5,
  9  6 c6,
...

2000  1997 c1997,
2001  1998 c1998,
2002  1999 c1999,
2003  2000 c2000
2004  from dual;
2005  end;
2006  /

PL/SQL procedure successfully completed.

SQL> print rc

        C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12        C13
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       C14        C15        C16        C17        C18        C19        C20        C21        C22        C23        C24        C25        C26
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       C27        C28        C29        C30        C31        C32        C33        C34        C35        C36        C37        C38        C39
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       C40        C41        C42        C43        C44        C45        C46        C47        C48        C49        C50        C51        C52
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       C53        C54        C55        C56        C57        C58        C59        C60        C61        C62        C63        C64        C65
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
...
...
...
      1964       1965       1966       1967       1968       1969       1970       1971       1972       1973       1974       1975       1976
      1977       1978       1979       1980       1981       1982       1983       1984       1985       1986       1987       1988       1989
      1990       1991       1992       1993       1994       1995       1996       1997       1998       1999       2000


1 row selected.


I think we'd need to see a more complete test case outlining what you're doing

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library