CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
gunaseelan thirumal
gunaseelan thirumal

Reputation: 23

Why the pipelined function working like normal table function?

I have created one pipelined function and another non-pipelined function.
But both functions when called from select statement, all results are shown only after complete execution of loop.

Why the pipelined function is not returning value as soon as the data for the each row is ready?

SELECT * FROM TABLE(GET_TAB(10,1));--Normal function call
SELECT * FROM TABLE(GET_TAB_P(10,1));--Pipelined function call

create or replace TYPE T_TF_ROW AS OBJECT(CNT NUMBER, DESCRIPTION VARCHAR2(50));
create or replace TYPE T_TF_TAB AS TABLE OF T_TF_ROW;

create or replace FUNCTION GET_TAB(P_ROWS IN NUMBER, P_SLEEP IN NUMBER)
RETURN T_TF_TAB
AS
V_T_TF_TAB T_TF_TAB:=T_TF_TAB();
BEGIN
FOR I IN 1..P_ROWS LOOP
DBMS_LOCK.SLEEP(P_SLEEP);
V_T_TF_TAB.EXTEND;
V_T_TF_TAB(V_T_TF_TAB.LAST):=T_TF_ROW(I,'DESCRIPTION OF : '||I);
END LOOP;
RETURN V_T_TF_TAB;
END;


create or replace FUNCTION GET_TAB_P(P_ROWS IN NUMBER, P_SLEEP IN NUMBER)
RETURN T_TF_TAB PIPELINED
AS
BEGIN
FOR I IN 1..P_ROWS LOOP
DBMS_LOCK.SLEEP(P_SLEEP);
PIPE ROW(T_TF_ROW(I,'DESCRIPTION FOR ' || I));
END LOOP;
RETURN;
END;

Normal function result

Pipelined function result

Upvotes: 2

Views: 49

Answers (1)

Jon Heller
Jon Heller

Reputation: 36902

Your pipelined function is working correctly, but your client's fetch size optimization is retrieving N rows at-a-time before it returns anything. Disable or lower the fetch size and you should start seeing individual rows returned. However, in production you do not want to disable that optimization.

In SQL*Plus, you would run this command to see one-row-at-a-time:

set arraysize 1;

(That actually returns two rows at a time for a reason I can't remember. There is a workaround, but unless you are writing some sort of progress-bar program you probably don't care about getting exactly one row-at-a-time.)

Upvotes: 1

Related Questions