Pipelined Function with Pipe Row() Pipes out 15 Rows at a Time (Doc ID 1265916.1)

Last updated on JULY 17, 2017

Applies to:

PL/SQL - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

When called in SQL*Plus, using a pipelined function does not actually return the
values piped via pipe row() except in sets of 15 rows.

Here is an example scenario.
Create a table and sequence.  Then populate the table.

drop sequence a_seq;
drop table A;

CREATE SEQUENCE A_SEQ
START WITH 1
MAXVALUE 999999999999999999999999999
NOCYCLE
NOCACHE
ORDER;

CREATE TABLE a (
SEQ_NUM number NOT NULL,
PROCESS_FLAG VARCHAR2(1 BYTE) DEFAULT 'N');

BEGIN
 FOR i in 1..20 loop
  INSERT INTO a(SEQ_NUM) VALUES (a_seq.NEXTVAL);
  Commit;
 End loop;
END;
/

Now, create the pipelined function and use it in a query. 
You will find 15 rows returned while the function awaits enough rows for the next batch.
If you choose to insert (and commit) another 10 rows in a separate session, the next batch will return.

CREATE OR REPLACE PACKAGE a_pkg AS
  TYPE tab_type IS TABLE OF varchar2(32767);
  FUNCTION a_pipelined RETURN a_pkg.tab_type PIPELINED;
END a_pkg;
/
CREATE or replace PACKAGE BODY a_pkg AS
FUNCTION a_pipelined RETURN a_pkg.tab_type PIPELINED IS
  v_seq_num number := 0;
  cursor c(par_seq_num number) is
    select seq_num from a
    where seq_num > par_seq_num order by seq_num;
BEGIN
Loop
  open c(v_seq_num);
   loop
    Fetch c into v_seq_num;
    exit when c%notfound;
    pipe row(v_seq_num);
   End Loop;
  close c;
End Loop;
RETURN;
END a_pipelined;
END a_pkg;
/

SELECT *
FROM TABLE(a_pkg.a_pipelined) pe;

--> Issue <CTRL C > to exit

===DEFAULT OUTPUT===

COLUMN_VALUE
--------------------------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11

COLUMN_VALUE
--------------------------------------------------------------------------------
12
13
14
15
--wait until issue ctrl-c  then
ERROR:
ORA-01013: user requested cancel of current operation
ORA-06512: at "USERX.A_PKG", line 12

15 rows selected.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms