Tuesday, November 13, 2012

PL/SQL SELECT into array

DECLARE
    TYPE ARRAY_TYPE IS TABLE OF VARCHAR2(20);
    mno_array ARRAY_TYPE;
    mno_id VARCHAR2(20);
BEGIN
     SELECT *
        BULK COLLECT
        INTO mno_array
        FROM (SELECT DT_PARTY.HOST_MNO_ID FROM RESOURCE_DB.HT_SIM_PARTY, RESOURCE_DB.DT_PARTY
                    WHERE HT_SIM_PARTY.PARTY_ID = DT_PARTY.PARTY_ID
                        AND HT_SIM_PARTY.SIM_ID = 'SIM_0000000000000184');
    DBMS_OUTPUT.PUT_LINE('count='||mno_array.COUNT);
    FOR i in 1 .. mno_array.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(mno_array(i));
    END LOOP;
END;