The simple example of PIPELINED function.

How to return the multiple rows from a stored procedure?

Sometimes, we need a stored procedure which returns multiple rows. On the SQLServer’s side, this is a very simple question. Basically, the procedure of SQLServer can return multiple rows. But, on the side of Oracle, this isn’t a simple question. Basically, the procedure of Oracle can’t return multiple rows. So if you want to do that, you have to use a REF CURSOR or PIPLELINED function. In my case, I use PIPLELINED function to return multiple rows. A simple example is as follows:

First of all, we create record and table TYPE. And then create PIPLELINED function.

 CREATE OR REPLACE PACKAGE get_active_sess_pkg
 IS
   TYPE my_rec IS RECORD
   (
      session_id NUMBER ,
      wait_class VARCHAR2( 64 ) ,
      program    VARCHAR2( 64 )
   );
 
   TYPE my_rec_tab IS TABLE OF my_rec;
   
   FUNCTION get_multi_rows (p_program varchar2, p_start_date varchar2, p_end_date varchar2)
   RETURN my_rec_tab PIPELINED;
 END get_active_sess_pkg;
/

 CREATE OR REPLACE PACKAGE BODY get_active_sess_pkg
 IS
   FUNCTION get_multi_rows (p_program varchar2, p_start_date varchar2, p_end_date varchar2) 
   RETURN my_rec_tab PIPELINED
   IS BEGIN
 
     FOR c_cur IN (SELECT session_id, wait_class, program
                  FROM   dba_hist_active_sess_history
                  WHERE  program LIKE p_program||'%'
                  AND    sample_time BETWEEN TO_DATE( p_start_date , 'yyyymmdd' )
                                     AND     TO_DATE( p_end_date ,   'yyyymmdd' ))
     LOOP
       PIPE ROW (c_cur);
     END LOOP;
 
     RETURN;
  END get_multi_rows;
 END get_active_sess_pkg;
/ 

We just run query, as follows:

SELECT *
FROM TABLE( get_active_sess_pkg.get_multi_rows( 'sqlplus' , '20130527' , '20130529' ) );