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;