Return dataset from function using pipelined table function

In this post I am going to demonstrate how to return dataset from function in PL/SQL using pipelined table function. For this we need to perform below steps.

  1. Create TYPE object as OBJECT of the structure of required output format
  2. Create another TYPE object which will be collection of above created type
  3. Return dataset from function by creating PIPELINED Function

I am going to use DB Time query for this demonstration.

 

Create TYPE object as OBJECT of the structure of required output format

 

SQL> create or replace TYPE DB_TIME_ROW AS OBJECT
  2  (
  3          DBID                             NUMBER,
  4          INSTANCE_NUMBER                  NUMBER,
  5          BEGIN_SNAP_ID                    NUMBER,
  6          END_SNAP_ID                      NUMBER,
  7          BEGIN_INTERVAL_TIME              TIMESTAMP,
  8          END_INTERVAL_TIME                TIMESTAMP,
  9          DB_TIME_IN_MINUTES               NUMBER
 10  );
 11  /

Type created.

 

Create TYPE Object which will be collection of above created TYPE object

 

SQL> create or replace TYPE DB_TIME_SQLSET IS TABLE OF DB_TIME_ROW;
  2  /

Type created.

 

Return dataset from function by creating PIPELINED Function

 

SQL> CREATE OR REPLACE FUNCTION GET_DB_TIME(DAYS IN NUMBER DEFAULT 7)
  2  RETURN DB_TIME_SQLSET
  3  PIPELINED  
  4  IS
  5  BEGIN
  6
  7  FOR I IN
  8  (
  9          SELECT * FROM
 10            (
 11            SELECT
 12              A.DBID,
 13              A.INSTANCE_NUMBER,
 14              LAG(A.SNAP_ID) OVER (ORDER BY A.SNAP_ID) BEGIN_SNAP_ID,
 15              A.SNAP_ID END_SNAP_ID,
 16              B.BEGIN_INTERVAL_TIME,
 17              B.END_INTERVAL_TIME,
 18              ROUND((A.VALUE-LAG(A.VALUE) OVER (ORDER BY A.SNAP_ID ))/1000000/60,2) DB_TIME_MIN
 19            FROM
 20            DBA_HIST_SYS_TIME_MODEL A, DBA_HIST_SNAPSHOT B
 21            WHERE
 22            A.SNAP_ID = B.SNAP_ID AND
 23            A.DBID = B.DBID AND
 24            A.INSTANCE_NUMBER = B.INSTANCE_NUMBER AND
 25            B.BEGIN_INTERVAL_TIME > TRUNC(SYSDATE-DAYS) AND
 26            A.STAT_NAME = 'DB time'
 27            )
 28            WHERE DB_TIME_MIN IS NOT NULL AND DB_TIME_MIN > 0
 29            ORDER BY 3
 30          )
 31          LOOP
 32            PIPE ROW(DB_TIME_ROW(I.DBID,I.INSTANCE_NUMBER,I.BEGIN_SNAP_ID,I.END_SNAP_ID,I.BEGIN_INTERVAL_TIME,I.END_INTERVAL_TIME,I.DB_TIME_MIN));
 33          END LOOP;
 34          RETURN;
 35        END;
 36  /

Function created.

 

Now we will query this function using table function.

 

SQL> select * from table(get_db_time(1));

      DBID INSTANCE_NUMBER BEGIN_SNAP_ID END_SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME              DB_TIME_IN_MINUTES
---------- --------------- ------------- ----------- ------------------------------ ------------------------------ ------------------
1417147728               1           737         738 04-OCT-15 01.30.56.594000 AM   04-OCT-15 02.30.09.737000 AM                  .02
1417147728               1           738         739 04-OCT-15 02.30.09.737000 AM   04-OCT-15 03.30.23.296000 AM                  .02
1417147728               1           739         740 04-OCT-15 03.30.23.296000 AM   04-OCT-15 04.30.38.366000 AM                  .02
1417147728               1           740         741 04-OCT-15 04.30.38.366000 AM   04-OCT-15 05.30.52.310000 AM                  .02
1417147728               1           741         742 04-OCT-15 05.30.52.310000 AM   04-OCT-15 06.30.05.462000 AM                  1.1
1417147728               1           742         743 04-OCT-15 06.30.05.462000 AM   04-OCT-15 07.30.17.882000 AM                  .02
1417147728               1           743         744 04-OCT-15 07.30.17.882000 AM   04-OCT-15 08.30.30.165000 AM                  .02
1417147728               1           744         745 04-OCT-15 08.30.30.165000 AM   04-OCT-15 09.30.42.291000 AM                  .02
1417147728               1           745         746 04-OCT-15 09.30.42.291000 AM   04-OCT-15 10.30.54.821000 AM                  .56
1417147728               1           746         747 04-OCT-15 10.30.54.821000 AM   04-OCT-15 11.30.07.224000 AM                  .02
1417147728               1           747         748 04-OCT-15 11.30.07.224000 AM   04-OCT-15 12.30.19.381000 PM                  .11
1417147728               1           748         749 04-OCT-15 12.30.19.381000 PM   04-OCT-15 01.30.31.828000 PM                  .03
1417147728               1           749         750 04-OCT-15 01.30.31.828000 PM   04-OCT-15 02.30.44.204000 PM                  .59
1417147728               1           750         751 04-OCT-15 02.30.44.204000 PM   04-OCT-15 03.30.56.460000 PM                  .03
1417147728               1           751         752 04-OCT-15 03.30.56.460000 PM   04-OCT-15 04.30.08.487000 PM                  .02
1417147728               1           752         753 04-OCT-15 04.30.08.487000 PM   04-OCT-15 05.30.21.002000 PM                  .02
1417147728               1           753         754 04-OCT-15 05.30.21.002000 PM   04-OCT-15 06.30.33.350000 PM                  .29
1417147728               1           754         755 04-OCT-15 06.30.33.350000 PM   04-OCT-15 07.30.45.811000 PM                  .02
1417147728               1           755         756 04-OCT-15 07.30.45.811000 PM   04-OCT-15 08.30.58.186000 PM                  .02
1417147728               1           756         757 04-OCT-15 08.30.58.186000 PM   04-OCT-15 09.30.10.246000 PM                  .02
1417147728               1           757         758 04-OCT-15 09.30.10.246000 PM   04-OCT-15 10.30.22.693000 PM                  .56
1417147728               1           758         759 04-OCT-15 10.30.22.693000 PM   04-OCT-15 11.30.35.036000 PM                  .02
1417147728               1           759         760 04-OCT-15 11.30.35.036000 PM   05-OCT-15 12.30.47.452000 AM                  .02
1417147728               1           760         761 05-OCT-15 12.30.47.452000 AM   05-OCT-15 01.30.59.934000 AM                  .02
1417147728               1           761         762 05-OCT-15 01.30.59.934000 AM   05-OCT-15 02.30.13.124000 AM                  .02
1417147728               1           762         763 05-OCT-15 02.30.13.124000 AM   05-OCT-15 03.30.27.431000 AM                  .02
1417147728               1           763         764 05-OCT-15 03.30.27.431000 AM   05-OCT-15 04.30.41.304000 AM                  .02
1417147728               1           764         765 05-OCT-15 04.30.41.304000 AM   05-OCT-15 05.30.55.906000 AM                  .02
1417147728               1           765         766 05-OCT-15 05.30.55.906000 AM   05-OCT-15 06.30.10.257000 AM                  .01
1417147728               1           766         767 05-OCT-15 06.30.10.257000 AM   05-OCT-15 07.30.24.692000 AM                  .02
1417147728               1           767         768 05-OCT-15 07.30.24.692000 AM   05-OCT-15 08.30.39.029000 AM                  .02
1417147728               1           768         769 05-OCT-15 08.30.39.029000 AM   05-OCT-15 09.30.53.564000 AM                  .02
1417147728               1           769         770 05-OCT-15 09.30.53.564000 AM   05-OCT-15 10.30.07.454000 AM                  .02
1417147728               1           770         771 05-OCT-15 10.30.07.454000 AM   05-OCT-15 11.30.21.849000 AM                  .02
1417147728               1           771         772 05-OCT-15 11.30.21.849000 AM   05-OCT-15 12.30.35.270000 PM                  .02
1417147728               1           772         773 05-OCT-15 12.30.35.270000 PM   05-OCT-15 01.30.48.237000 PM                  .02
1417147728               1           773         774 05-OCT-15 01.30.48.237000 PM   05-OCT-15 02.30.02.503000 PM                  .03

37 rows selected.
Return dataset from function using pipelined table function

Leave a Reply