Home » SQL & PL/SQL » SQL & PL/SQL » PIPE multiple rows at once in PIPELINED Function. (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit)
PIPE multiple rows at once in PIPELINED Function. [message #566361] Thu, 13 September 2012 08:45 Go to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I've used PIPELINED FUNCTION and I've no issues in using this. Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.

Like we use BULK COLLECT INTO to fetch multiple rows at once instead of fetching one row using SELECT INTO.

Below is the test case:
CREATE TABLE TMP_EMP
(	
      EMP_ID          NUMBER(10,0), 
      EMP_NAME        VARCHAR2(100), 
      DEPT_ID         NUMBER(10,0), 
      SALARY          NUMBER(14,0), 
      COMMISSION_PCT  NUMBER(3,0),
      JOIN_DATE       DATE, 
      CONSTRAINT PK_TMP_EMP PRIMARY KEY( EMP_ID )
) ;

INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (1,'Manoj',10,10324,10,to_date('05-JAN-11','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (2,'Ravi',10,54412,4,to_date('07-FEB-11','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (3,'Harish',10,74548,6,to_date('15-AUG-80','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (4,'Naresh',10,65472,5,to_date('15-MAY-17','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (5,'Rakesh',10,78954,9,to_date('07-MAY-15','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (6,'Hari Ram',10,32145,7,to_date('07-JUL-08','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (7,'Hari Ram',10,4578124,1,to_date('07-OCT-08','DD-MON-RR'));


CREATE OR REPLACE TYPE OBJ_TEST AS OBJECT ( EMP_ID NUMBER(10), EMP_NAME VARCHAR2(100) ) ;

CREATE OR REPLACE TYPE TBL_OBJ_TEST IS TABLE OF OBJ_TEST ;

CREATE OR REPLACE FUNCTION FUN_TEST 
    RETURN TBL_OBJ_TEST PIPELINED
AS
    LVOB_TEST  TBL_OBJ_TEST := TBL_OBJ_TEST() ;
BEGIN

    --Can we pipe all these rows at once which are fetched using BULK COLLECT?
    --I don't want to use loop

    /*
    SELECT OBJ_TEST(EMP_ID, EMP_NAME)
    BULK COLLECT INTO LVOB_TEST
    FROM TMP_EMP ;
    */    
    
    PIPE ROW( OBJ_TEST( 10, 'MANOJ' ) ) ;  --Hard coded for example. Will be dynamic in real world.

    RETURN ;

END ;


Thanks & Regards
Manoj
Re: PIPE multiple rows at once in PIPELINED Function. [message #566364 is a reply to message #566361] Thu, 13 September 2012 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 58635
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.


No.
You can only pipe the row one at a time but you can bulk fetch them.

Regards
Michel
Re: PIPE multiple rows at once in PIPELINED Function. [message #566365 is a reply to message #566364] Thu, 13 September 2012 08:59 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi,

Thanks Michel...

Understood...Bulk Fetch rows from source (table,view,MV etc.) and then use loop to pipe them one by one.

Correct?

Regards
Manoj
Re: PIPE multiple rows at once in PIPELINED Function. [message #566373 is a reply to message #566365] Thu, 13 September 2012 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 58635
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes and use bulk collect (any version, array fetch...) to select/fetch from the pipelined function.

Regards
Michel
Re: PIPE multiple rows at once in PIPELINED Function. [message #566390 is a reply to message #566361] Thu, 13 September 2012 15:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1966
Registered: January 2010
Senior Member
Manoj.Gupta.91 wrote on Thu, 13 September 2012 09:45
Hi All,

I've used PIPELINED FUNCTION and I've no issues in using this. Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.

Like we use BULK COLLECT INTO to fetch multiple rows at once instead of fetching one row using SELECT INTO.


BULK COLLECT + PIPE ROW in a loop within pipelined function never makes sense. What is the advantage of of pipelined function? For a regular table function SQL must wait till table function produces complete collection and only then SQL can start fetching rows and passing it back to client. With pipelined function we calculate one element and pipe it back to SQL right away so SQL fetches is right away and client who issued SQL can process it right away. So statement is processed faster and we don't need to allocate memory for collection, which by itself can be large resource. By using BULK COLLECT we (both function, SQL and client) again wait till complete collection is produced and then start piping them one by one which negates all the savings we could have allowing client to process rows in parallel with function preparing next row(s).

SY.
Re: PIPE multiple rows at once in PIPELINED Function. [message #566401 is a reply to message #566390] Fri, 14 September 2012 01:39 Go to previous messageGo to next message
Maaher
Messages: 7041
Registered: December 2001
Senior Member
I agree. It seems to me that Manoj.Gupta.91 is looking for a function that returns a data set (table type ?) instead of a single row.

MHE
Re: PIPE multiple rows at once in PIPELINED Function. [message #566403 is a reply to message #566390] Fri, 14 September 2012 01:52 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi,

As per my understanding there can be two ways of working. I'm describing both of these below. Second way is correct because it fulfills the purpose of pipelined function as it has no wait time for the client issuing SQL.


Please confirm my understanding.

First Way :
CREATE OR REPLACE
FUNCTION FUN_TEST 
    RETURN TBL_OBJ_TEST PIPELINED
AS
    LVOB_TEST  TBL_OBJ_TEST := TBL_OBJ_TEST() ;

BEGIN

    ------------------------------------------------------------------------
    --Bulk Fetch and Pipe one by one thereafter. 
    --Client who issued SQL must wait till table function produces complete collection.

    SELECT OBJ_TEST(EMP_ID, EMP_NAME)
    BULK COLLECT INTO LVOB_TEST
    FROM TMP_EMP ;
    
    FOR I IN 1..LVOB_TEST.COUNT 
    LOOP    
        PIPE ROW( OBJ_TEST( LVOB_TEST(I).EMP_ID, LVOB_TEST(I).EMP_NAME ) ) ;
    END LOOP ;

    RETURN ;
    ------------------------------------------------------------------------

END ;


Second Way :
CREATE OR REPLACE
FUNCTION FUN_TEST 
    RETURN TBL_OBJ_TEST PIPELINED
AS
    LVOB_TEST  TBL_OBJ_TEST := TBL_OBJ_TEST() ;
    
    CURSOR CUR_EMP
    IS
        SELECT EMP_ID, EMP_NAME
        FROM TMP_EMP ;
BEGIN

    --**********************************************************************

    --Fetching one row and Pipe it at same time. 
    --Client who issued SQL can process it right away.

    FOR REC_EMP IN CUR_EMP
    LOOP
        PIPE ROW( OBJ_TEST( LVOB_TEST( CUR_EMP%ROWCOUNT ).EMP_ID, LVOB_TEST( CUR_EMP%ROWCOUNT ).EMP_NAME ) ) ;
    END LOOP ;
    
    RETURN ;

    --**********************************************************************
END ;


Thanks & Regards
Manoj

[Updated on: Fri, 14 September 2012 01:54]

Report message to a moderator

Re: PIPE multiple rows at once in PIPELINED Function. [message #566405 is a reply to message #566403] Fri, 14 September 2012 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58635
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just:
CREATE OR REPLACE FUNCTION FUN_TEST RETURN SYS_REFCURSOR
IS
  l_res SYS_REFCURSOR;
BEGIN
  open l_res for SELECT EMPNO, ENAME FROM EMP ;
  RETURN l_res;
END;
/

SQL> CREATE OR REPLACE FUNCTION FUN_TEST RETURN SYS_REFCURSOR
  2  IS
  3    l_res SYS_REFCURSOR;
  4  BEGIN
  5    open l_res for SELECT EMPNO, ENAME FROM EMP ;
  6    RETURN l_res;
  7  END;
  8  /

Function created.

SQL> select fun_test from dual;
FUN_TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

Regards
Michel

Re: PIPE multiple rows at once in PIPELINED Function. [message #566407 is a reply to message #566405] Fri, 14 September 2012 02:25 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi,

Michel, your example is better. There is always more than one way to solve a problem.

Sorry, but I'm actually talking about PIPELINED functios this time.

Thanks & Regards
Manoj
Re: PIPE multiple rows at once in PIPELINED Function. [message #566422 is a reply to message #566407] Fri, 14 September 2012 07:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1966
Registered: January 2010
Senior Member
Your pipelined function code simply makes no sense. There is nothing in it that warrants not even pipelined not even table function but function alltogether. It would start to make sense if, for example, within FOR CURSOR loop there would be some complex processing that can't be done in SQL. And then you'd see what I was talking about in my first reply.

SY.
Re: PIPE multiple rows at once in PIPELINED Function. [message #566423 is a reply to message #566422] Fri, 14 September 2012 07:50 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi,

@Solomon

Quote:
Your pipelined function code simply makes no sense. There is nothing in it that warrants not even pipelined not even table function but function alltogether.
I don't understand why it is not pipelined. Please help to understand.


Quote:
It would start to make sense if, for example, within FOR CURSOR loop there would be some complex processing that can't be done in SQL.
Posting an example that has complex logic is not the right thing. I wanted to discuss the concept so I posted a simple example code. I understand the basic rule is "If something can be done in SQL do it there".

Can you post some example that helps me to understand more.

Thanks & Regards
Manoj
Re: PIPE multiple rows at once in PIPELINED Function. [message #566425 is a reply to message #566390] Fri, 14 September 2012 07:56 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
Solomon Yakobson wrote on Thu, 13 September 2012 22:44

BULK COLLECT + PIPE ROW in a loop within pipelined function never makes sense.

See a contradictory example here: tuning pipelined functions with array fetches

Re: PIPE multiple rows at once in PIPELINED Function. [message #566463 is a reply to message #566425] Fri, 14 September 2012 16:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1966
Registered: January 2010
Senior Member
mnitu wrote on Fri, 14 September 2012 08:56
See a contradictory example here: tuning pipelined functions with array fetches


A) Example in the article isn't a good candidate for a function at all. Look at original code. It selects ticker, open_price, close_price, trade_date from stocktable. For each fetched row it inserts two rows into tickertable - one with operining price and other with closing price. So all that lines and lines of code could be replaced with:

INSERT
  INTO tickertable
  SELECT  ticker,
          case lvl when 1 then 'O' else 'C' end,
          case lvl when 1 then open_price else close_price end,
          trade_date
    FROM  stocktable,
          (select level lvl from dual connect by level <= 2)
/


B) Now BULK COLLECT LIMIT + PIPE ROW is different from BULK COLLECT + PIPE ROW. By using LIMIT we are "mimicking" client side prefetch and possibly saving time of context swithing.
C) And keep attention to "The optimal array fetch size will vary according to your specific data-processing requirements". This also implies there might be no improvement and even the opposite result. Everything is fine with those "book examples" when we select all rows from the table. In other words interval between fetching current and next rows is mimimal and context swithing is an impact. Now assume you have a complex query with complex where clause. So there might be (and most likely will be) time gaps in fetching e.g. we are going through rows that do not fit where clause. In such case context switch time in less or no impact and we will spend more time waithing to fetch 100 rows and then piping 100rows in a loop then fetching rows and piping them one at a time.

SY.
Re: PIPE multiple rows at once in PIPELINED Function. [message #566464 is a reply to message #566463] Fri, 14 September 2012 16:26 Go to previous message
Solomon Yakobson
Messages: 1966
Registered: January 2010
Senior Member
And to reiterate, if pipelined function does nothing but selects rows from a cursor and pipes them back, such function is not needed at all - same can be done with plain select.

SY.
Previous Topic: Signatory_Sequence
Next Topic: Date when attributes were update
Goto Forum:
  


Current Time: Thu Jul 31 13:41:41 CDT 2014

Total time taken to generate the page: 0.11347 seconds