How to append the records in table type object using bulk collect inside the cursor loop

Question

I am trying to append the records into a table type object using bulk collect inside the cursor loop. But i am getting the last record added in the object.I think it is overwritten on the previous record. How can i append all the record while looping instead of overwriting each time?

My code:

create or replace FUNCTION GET_DEM_CONTAINER_LIST RETURN DEM_CNT_TBL_TYPE AS
    DEM_CNT_LIST            DEM_CNT_TBL_TYPE :=DEM_CNT_TBL_TYPE();
    P_FREE_DAYS             NUMBER;
    P_DEM_REQ_FLAG          CHAR(1);
    P_STORERKEY             VARCHAR2(15);
    P_TOID                  VARCHAR2(30);
    P_SKU                   VARCHAR2(20);
    P_RECVD_DATE            DATE;
    P_DEM_DATE              DATE;
    P_LOT                   VARCHAR2(10);
    P_DEM_DAYS              NUMBER;
    P_DIFF_DAYS             NUMBER;
    
    CURSOR C1 IS        SELECT CCM_FREE_STORE_DAYS,CCM_DEM_BILL_REQUIRED,W.STORERKEY,TOID,SKU,RECVD_DATE,DEM_DATE,LOT
    FROM                CUSTOMER_CONTRACT_MASTER,WEB_BAL_CONTAINER_LIST W
    WHERE               W.STORERKEY=CCM_STORERKEY
    AND                 QTY_BAL>0
    AND                 SKU LIKE 'CNT%'
    ORDER BY            RECVD_DATE;
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 INTO P_FREE_DAYS,P_DEM_REQ_FLAG,P_STORERKEY,P_TOID,P_SKU,P_RECVD_DATE,P_DEM_DATE,P_LOT;
        EXIT WHEN C1%NOTFOUND;
        P_DIFF_DAYS :=(TRUNC(SYSDATE)-TRUNC(P_DEM_DATE))+1;
        IF P_DIFF_DAYS>P_FREE_DAYS THEN
            DEM_CNT_LIST.EXTEND();
            P_DEM_DAYS  :=P_DIFF_DAYS-P_FREE_DAYS;
            --DBMS_OUTPUT.PUT_LINE(P_TOID||','||P_LOT||','||P_DEM_DATE||','||P_FREE_DAYS||','||P_DEM_DAYS);
            SELECT DEM_CNT_OBJ_TYPE(P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS)
            BULK COLLECT INTO    DEM_CNT_LIST
            FROM    (SELECT P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS FROM DUAL);  
        END IF;
    END LOOP;
    CLOSE C1; 
    RETURN DEM_CNT_LIST;
END;

Answer 1

This query completely overwrites the previously stored value of DEM_CNT_LIST:

SELECT DEM_CNT_OBJ_TYPE(P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS)
BULK COLLECT INTO    DEM_CNT_LIST
FROM    (SELECT P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS FROM DUAL);  

Replace it with:

DEM_CNT_LIST(DEM_CNT_LIST.LAST) := DEM_CNT_OBJ_TYPE(P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS);

Answer 2

Initially I thought to UNION the new data with the existing data:

create or replace FUNCTION GET_DEM_CONTAINER_LIST
                                             RETURN DEM_CNT_TBL_TYPE AS
                                             
    DEM_CNT_LIST            DEM_CNT_TBL_TYPE :=DEM_CNT_TBL_TYPE();
    DEM_CNT_LIST_2          DEM_CNT_TBL_TYPE :=DEM_CNT_TBL_TYPE();  -- 2nd var
    P_FREE_DAYS             NUMBER;
    P_DEM_REQ_FLAG          CHAR(1);
    P_STORERKEY             VARCHAR2(15);
    P_TOID                  VARCHAR2(30);
    P_SKU                   VARCHAR2(20);
    P_RECVD_DATE            DATE;
    P_DEM_DATE              DATE;
    P_LOT                   VARCHAR2(10);
    P_DEM_DAYS              NUMBER;
    P_DIFF_DAYS             NUMBER;
    
    CURSOR C1 IS        SELECT CCM_FREE_STORE_DAYS,CCM_DEM_BILL_REQUIRED,W.STORERKEY,TOID,SKU,RECVD_DATE,DEM_DATE,LOT
    FROM                CUSTOMER_CONTRACT_MASTER,WEB_BAL_CONTAINER_LIST W
    WHERE               W.STORERKEY=CCM_STORERKEY
    AND                 QTY_BAL>0
    AND                 SKU LIKE 'CNT%'
    ORDER BY            RECVD_DATE;
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 INTO P_FREE_DAYS,P_DEM_REQ_FLAG,P_STORERKEY,P_TOID,P_SKU,P_RECVD_DATE,P_DEM_DATE,P_LOT;
        EXIT WHEN C1%NOTFOUND;
        P_DIFF_DAYS :=(TRUNC(SYSDATE)-TRUNC(P_DEM_DATE))+1;
        IF P_DIFF_DAYS>P_FREE_DAYS THEN
            DEM_CNT_LIST.EXTEND();
            P_DEM_DAYS  :=P_DIFF_DAYS-P_FREE_DAYS;
            --DBMS_OUTPUT.PUT_LINE(P_TOID||','||P_LOT||','||P_DEM_DATE||','||P_FREE_DAYS||','||P_DEM_DAYS);
            SELECT DEM_CNT_OBJ_TYPE(P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS)
            BULK COLLECT INTO    DEM_CNT_LIST_2
            FROM    (SELECT P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS FROM DUAL)
/** Add this UNION **/
             UNION ALL
            SELECT * FROM TABLE(DEM_CNT_LIST);  
        END IF;
    END LOOP;
    CLOSE C1; 
    RETURN DEM_CNT_LIST_2;
END;

But found there is a direct MULTISET UNION ALL operation for collections:

create or replace FUNCTION GET_DEM_CONTAINER_LIST
                                             RETURN DEM_CNT_TBL_TYPE AS
                                             
    DEM_CNT_LIST            DEM_CNT_TBL_TYPE :=DEM_CNT_TBL_TYPE();
    DEM_CNT_LIST_2          DEM_CNT_TBL_TYPE :=DEM_CNT_TBL_TYPE();  -- 2nd var
    P_FREE_DAYS             NUMBER;
    P_DEM_REQ_FLAG          CHAR(1);
    P_STORERKEY             VARCHAR2(15);
    P_TOID                  VARCHAR2(30);
    P_SKU                   VARCHAR2(20);
    P_RECVD_DATE            DATE;
    P_DEM_DATE              DATE;
    P_LOT                   VARCHAR2(10);
    P_DEM_DAYS              NUMBER;
    P_DIFF_DAYS             NUMBER;
    
    CURSOR C1 IS        SELECT CCM_FREE_STORE_DAYS,CCM_DEM_BILL_REQUIRED,W.STORERKEY,TOID,SKU,RECVD_DATE,DEM_DATE,LOT
    FROM                CUSTOMER_CONTRACT_MASTER,WEB_BAL_CONTAINER_LIST W
    WHERE               W.STORERKEY=CCM_STORERKEY
    AND                 QTY_BAL>0
    AND                 SKU LIKE 'CNT%'
    ORDER BY            RECVD_DATE;
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 INTO P_FREE_DAYS,P_DEM_REQ_FLAG,P_STORERKEY,P_TOID,P_SKU,P_RECVD_DATE,P_DEM_DATE,P_LOT;
        EXIT WHEN C1%NOTFOUND;
        P_DIFF_DAYS :=(TRUNC(SYSDATE)-TRUNC(P_DEM_DATE))+1;
        IF P_DIFF_DAYS>P_FREE_DAYS THEN
            DEM_CNT_LIST.EXTEND();
            P_DEM_DAYS  :=P_DIFF_DAYS-P_FREE_DAYS;
            --DBMS_OUTPUT.PUT_LINE(P_TOID||','||P_LOT||','||P_DEM_DATE||','||P_FREE_DAYS||','||P_DEM_DAYS);
            SELECT DEM_CNT_OBJ_TYPE(P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS)
            BULK COLLECT INTO    DEM_CNT_LIST
            FROM    (SELECT P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS FROM DUAL);
          /** Add this MULTISET UNION **/
            DEM_CNT_LIST_2 := DEM_CNT_LISTmultiset union all DEM_CNT_LIST_2 ;
        END IF;
    END LOOP;
    CLOSE C1; 
    RETURN DEM_CNT_LIST_2;
END;

Answer 3

You can fill temporary collection and then use 'multiset union all' to append collection to the main own:

create or replace FUNCTION GET_DEM_CONTAINER_LIST
                                             RETURN DEM_CNT_TBL_TYPE AS
                                             
    DEM_CNT_LIST            DEM_CNT_TBL_TYPE :=DEM_CNT_TBL_TYPE();
    -- temp variable:
    TMP_DEM_CNT_LIST        DEM_CNT_TBL_TYPE :=DEM_CNT_TBL_TYPE();
    P_FREE_DAYS             NUMBER;
    P_DEM_REQ_FLAG          CHAR(1);
    P_STORERKEY             VARCHAR2(15);
    P_TOID                  VARCHAR2(30);
    P_SKU                   VARCHAR2(20);
    P_RECVD_DATE            DATE;
    P_DEM_DATE              DATE;
    P_LOT                   VARCHAR2(10);
    P_DEM_DAYS              NUMBER;
    P_DIFF_DAYS             NUMBER;
    
    CURSOR C1 IS        SELECT CCM_FREE_STORE_DAYS,CCM_DEM_BILL_REQUIRED,W.STORERKEY,TOID,SKU,RECVD_DATE,DEM_DATE,LOT
    FROM                CUSTOMER_CONTRACT_MASTER,WEB_BAL_CONTAINER_LIST W
    WHERE               W.STORERKEY=CCM_STORERKEY
    AND                 QTY_BAL>0
    AND                 SKU LIKE 'CNT%'
    ORDER BY            RECVD_DATE;
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 INTO P_FREE_DAYS,P_DEM_REQ_FLAG,P_STORERKEY,P_TOID,P_SKU,P_RECVD_DATE,P_DEM_DATE,P_LOT;
        EXIT WHEN C1%NOTFOUND;
        P_DIFF_DAYS :=(TRUNC(SYSDATE)-TRUNC(P_DEM_DATE))+1;
        IF P_DIFF_DAYS>P_FREE_DAYS THEN
            DEM_CNT_LIST.EXTEND();
            P_DEM_DAYS  :=P_DIFF_DAYS-P_FREE_DAYS;
            --DBMS_OUTPUT.PUT_LINE(P_TOID||','||P_LOT||','||P_DEM_DATE||','||P_FREE_DAYS||','||P_DEM_DAYS);
            SELECT DEM_CNT_OBJ_TYPE(P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS)
            BULK COLLECT INTO    TMP_DEM_CNT_LIST
            FROM    (SELECT P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS FROM DUAL);  
            -- adding collection into DEM_CNT_LIST:
            DEM_CNT_LIST:=DEM_CNT_LIST multiset union all TMP_DEM_CNT_LIST;
        END IF;
    END LOOP;
    CLOSE C1; 
    RETURN DEM_CNT_LIST;
END;