Bulk Collect with Sum function

I am trying to use Bulk all and Forall in Oracle database:

Original code from Procedure is as below:

IF NVL(v_mc,0) != 0 THEN
        FOR rec IN
        (SELECT a.testid,
            SUM(pct * NVL(cap,0))/v_mc lead1
            BULK COLLECT INTO testids1, testids2
        FROM testtable a
        WHERE a.id      = n_id
        AND a.type      =n_type

        GROUP BY a.testid;
        )
        LOOP
            UPDATE testtable
            SET LEAD1    =ROUND(testids2(i),2)
            WHERE tid  = n_id
            AND type  = n_type
            AND testid   =testids1(i);
        END LOOP;
END IF;

So In select statement , I am using Sum function and also using aliasing here .

Code , I have written which use Bulk collect and Forall is as follows:

PROCEDURE test
IS

    TYPE test1Tab IS TABLE OF sh_rpt_temp_peer_wip.test1%TYPE;
    TYPE test2Tab IS TABLE OF testtable.lead1%TYPE;
    testids1 testidTab; --Error 1 and Error 2
    testids2 LeadTab;

BEGIN

IF NVL(v_mc,0) != 0 THEN

     SELECT testid,
            SUM(pct * NVL(cap,0))/v_mc lead1
            BULK COLLECT INTO testids1, testids2
        FROM testtable a               --Error 3
        WHERE a.id      = n_id
        AND a.type      =n_type

        GROUP BY a.testid ORDER BY a.testid;

        FORALL i IN testids1.FIRST..testids1.LAST
          UPDATE testtable
            SET LEAD1    =ROUND(testids2(i),2)
            WHERE tid  = n_id  --Error 3
            AND type  = n_type
            AND testid   =testids1(i);

END IF;

END;

But while I am compiling procedure , I am getting multiple errors. I am very new to PL/SQL. Please let me know if I can retrieve calculated value as a Column in Bulk Collect? I am getting below errors in procedure:

• Error 1) PL/SQL: Item ignored
• Error 2) component 'LEAD' must be declared
• Error 3) expression is of wrong type

Please let me know what is wrong here

Thanks

As I identified that the collection type that you are referring is not in scope in the procedure, might be you have declared globally. I modified your code get a try it once, hopefully, it works for you.

PROCEDURE test
IS

    TYPE test1Tab IS TABLE OF testtable.testid%TYPE;
    TYPE test2Tab IS TABLE OF number;
    testids1 test1Tab; //Error 1 and Error 2
    testids2 test2Tab;

BEGIN

IF NVL(v_mc,0) != 0 THEN

     SELECT testid,
            SUM(pct * NVL(cap,0))/v_mc lead
            BULK COLLECT INTO testids1, testids2
        FROM testtable a               //Error 3
        WHERE a.id      = n_id
        AND a.type      =n_type
        GROUP BY a.testid ORDER BY a.testid;

        FORALL i IN testids1.FIRST..testids1.LAST
          UPDATE testtable
            SET LEAD    = ROUND(testids2(i),2)
            WHERE tid  = n_id   //Error 3
            AND type  = n_type
            AND testid   = testids1(i);

END IF;
END;