Wednesday, October 2, 2013

Oracle: Read some columns from returned ref cursor

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;