To return a data collection (or array – i.e. a record or table)

First define the data structures required:

CREATE OR REPLACE TYPE o_po_info AS OBJECT
(po_num VARCHAR2(20)
,po_line_num NUMBER
,po_ship_num NUMBER
);

…used by the PL/SQL function, and…

CREATE OR REPLACE TYPE t_po_info AS TABLE OF o_po_info;

…used by the query invoking the function.

Alternatively, declare RECORD and TABLE types in the PL/SQL function itself:

TYPE r_po_info IS RECORD (po_num VARCHAR2(20)
 ,po_line_num VARCHAR2(100)
 ,po_ship_num VARCHAR2(100)
 ,agent_name VARCHAR(240)
 ,receipt_num VARCHAR(30)
 );
 TYPE t_po_info IS TABLE OF r_po_info;
 

Then create the function. The following returns PO information for a specific supplier invoice:

CREATE OR REPLACE FUNCTION dt_test
(p_invoice_id IN NUMBER)
RETURN t_po_info
PIPELINED
AS
CURSOR c_po_info
(p_invoice_id IN NUMBER)
IS
SELECT pha.segment1
, pla.line_num
, plla.shipment_num
, COUNT(DISTINCT pha.segment1)
, COUNT(DISTINCT pla.line_num)
, COUNT(DISTINCT plla.shipment_num)
FROM po_headers_all pha
, po_lines_all pla
, po_line_locations_all plla
, po_distributions_all pda
, ap_invoice_distributions_all aida
WHERE pha.po_header_id = pla.po_header_id
AND pha.org_id = pla.org_id
AND plla.po_header_id = pla.po_header_id
AND plla.po_line_id = pla.po_line_id
AND plla.org_id = pla.org_id
AND pda.po_header_id = pla.po_header_id
AND pda.po_line_id = pda.po_line_id
AND pda.org_id = pla.org_id
AND aida.org_id = pda.org_id
AND aida.po_distribution_id = pda.po_distribution_id
AND aida.invoice_id = p_invoice_id
AND ROWNUM = 1
GROUP BY pha.segment1
, pha.creation_date
, pla.line_num
, plla.shipment_num
ORDER BY pha.creation_date DESC
, pla.line_num
, plla.shipment_num
;

v_po_info o_po_info := o_po_info(NULL,NULL,NULL);
v_po_num VARCHAR2(20);
v_po_line_num NUMBER;
v_po_ship_num NUMBER;
v_po_count NUMBER;
v_po_line_count NUMBER;
v_po_ship_count NUMBER;

BEGIN
OPEN c_po_info(p_invoice_id);
FETCH c_po_info
INTO v_po_num
, v_po_line_num
, v_po_ship_num
, v_po_count
, v_po_line_count
, v_po_ship_count
;

IF (c_po_info%NOTFOUND) THEN
  CLOSE c_po_info;
ELSE
  IF (v_po_count = 1) THEN
    v_po_info.po_num := v_po_num;
  ELSIF (v_po_count > 1) THEN
    v_po_info.po_num := v_po_num||' + more';
  END IF;

  IF (v_po_line_count = 1) THEN
    v_po_info.po_line_num := v_po_line_num;
  ELSIF (v_po_line_count > 1) THEN
    v_po_info.po_line_num := v_po_line_num||' + more';
  END IF;

  IF (v_po_ship_count = 1) THEN
    v_po_info.po_ship_num := v_po_ship_num;
  ELSIF (v_po_ship_count > 1) THEN
    v_po_info.po_ship_num := v_po_ship_num||' + more';
  END IF;

  CLOSE c_po_info;
END IF;
PIPE ROW (v_po_info);
RETURN;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END dt_test;

The function can be used in a conventional query:

SELECT table_alias.*
FROM   TABLE(CAST(dt_test(2572423) AS t_po_info)) table_alias

Or, if the custom data TYPEs are declared inside the PL/SQL package or function, the query does not not require the CAST clause:

SELECT table_alias.*
FROM   TABLE(dt_test(2572423)) table_alias

 

Here is another example, to show that table rows are not overwritten by values derived in subsequent loop iterations:

First the data structures

CREATE OR REPLACE TYPE o_user AS OBJECT
(user_name VARCHAR2(50));

CREATE OR REPLACE TYPE t_user AS TABLE OF o_user;

Then create the function. The following returns 3 user names from fnd_user:

CREATE OR REPLACE FUNCTION dt_test
RETURN t_user
PIPELINED
AS

CURSOR c_user
IS
SELECT user_id
FROM   fnd_user
WHERE  user_name IN ('DAVE_TAYLOR','ELLIOTT_JONES','MANOGNA_VADAPALLI')
;

v_user_name    VARCHAR2(50);
v_user  o_user := o_user(NULL);

BEGIN

FOR i_user IN c_user LOOP

  SELECT user_name
  INTO   v_user_name
  FROM   fnd_user
  WHERE  user_id = i_user.user_id
  ;

  v_user.user_name := v_user_name;

  PIPE ROW (v_user);

  dbms_output.put_line('User : '||v_user_name);

END LOOP;

RETURN;

END;