SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
my_rating_list DWH_RATED_USAGE.RATED_USAGE_API.cursor_type;
rc NUMBER;
rd VARCHAR2(200);
my_margin_h DWH_RATED_USAGE.RATED_USAGE_API.ID_ARRAY_TYPE;
TYPE rec_type IS RECORD (
VPLMN_COUNTRY_NAME DWH_RATED_USAGE.RM_VPLMN_CLIENT.VPLMN_COUNTRY_NAME%TYPE,
H_NETWORK_ID DWH_RATED_USAGE.RM_VPLMN_CLIENT.H_NETWORK_ID%TYPE
);
TYPE rec_array IS TABLE OF rec_type;
my_rec_array rec_array;
BEGIN
my_margin_h(1) := 'VPLMN_COUNTRY_NAME';
my_margin_h(2) := 'H_NETWORK_ID';
rc := DWH_RATED_USAGE.RATED_USAGE_API.get_margin_list(my_margin_h, '01/09/2013', '01/10/2013', my_rating_list, rd);
IF 1 != rc THEN DBMS_OUTPUT.put_line(rd); RETURN; END IF;
IF my_rating_list IS NULL THEN DBMS_OUTPUT.put_line('Empty cursor'); RETURN; END IF;
SELECT t.COLUMN_VALUE.EXTRACT('ROW/VPLMN_COUNTRY_NAME/text()').getstringval(),
t.COLUMN_VALUE.EXTRACT('ROW/H_NETWORK_ID/text()').getstringval()
BULK COLLECT INTO my_rec_array
FROM TABLE(XMLSEQUENCE(my_rating_list)) t;
FOR i IN 1..my_rec_array.COUNT LOOP
DBMS_OUTPUT.put_line(my_rec_array(i).VPLMN_COUNTRY_NAME||' - '||my_rec_array(i).H_NETWORK_ID);
END LOOP;
END;