Two Cursors With Bulk Collect

 CREATE OR REPLACE PROCEDURE two_cursor_with_bulk_collect (

    P_Status_Out     OUT VARCHAR2,

    P_Error_Msg_Out  OUT VARCHAR2

)

IS

    l_error_msg      VARCHAR2(3000);

    V_supplier_name  Syb_Po_Header_Table.Supplier_name%TYPE;

    -- Cursor to fetch header records

    CURSOR C1 IS

        SELECT *

        FROM Syb_Po_Header_Table

        WHERE NVL(Process_Status, 'V') = 'V';

    -- Cursor to fetch line records based on INTERFACE_HEADER_KEY

    CURSOR C2(P_INTERFACE_HEADER_KEY VARCHAR2) IS

        SELECT *

        FROM Syb_Po_Header_Table

        WHERE NVL(Process_Status, 'V') = 'V'

          AND INTERFACE_HEADER_KEY = P_INTERFACE_HEADER_KEY;

    -- Collections for bulk collect

    TYPE Po_Headers_Type IS TABLE OF C1%ROWTYPE;

    Lv_Po_Headers  Po_Headers_Type;

    TYPE Po_Lines_Type IS TABLE OF C2%ROWTYPE;

    Lv_Po_Lines    Po_Lines_Type;

BEGIN

    -- Fetch header-level data

    OPEN C1;

    FETCH C1 BULK COLLECT INTO Lv_Po_Headers;

    CLOSE C1;

    -- Loop through header records

    FOR I IN 1 .. Lv_Po_Headers.COUNT LOOP

        -- Header level processing logic here

        NULL;

    END LOOP;

    -- Loop through header records again to fetch corresponding lines

    FOR I IN 1 .. Lv_Po_Headers.COUNT LOOP

        -- Fetch line-level data based on INTERFACE_HEADER_KEY

        OPEN C2(Lv_Po_Headers(I).INTERFACE_HEADER_KEY);

        FETCH C2 BULK COLLECT INTO Lv_Po_Lines;

        CLOSE C2;

        -- Process each line record

        FOR J IN 1 .. Lv_Po_Lines.COUNT LOOP

            -- Line level processing logic here

            NULL;

        END LOOP;

    END LOOP;

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

        P_Status_Out    := 'E';

        P_Error_Msg_Out := 'Error Code: ' || SQLCODE || ' - ' || SQLERRM;

END two_cursor_with_bulk_collect;