Bulk Collect with Sum function

Question

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

In your existing code `Lead` is alias of the sum you did. How you are updating an alias in loop. That's not possible. Your existing code itself will fail. You can only update a column of a table. Update of alias are not allowed — XING, Jun 13 '18 at 06:47
Errors 1 and 2 are the same error. The compiler gives two messages for unrecognised components: *'xxx must be declared'* followed by *'Item ignored'*. And it's right, `testidTab` doesn't seem to be declared. — William Robertson, Jun 13 '18 at 07:02
There is an existing built-in SQL function named [`LEAD`](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/LEAD.html), so that is not a good name for a table column. Even if you get away with it here it is likely to give syntax problems sooner or later. — William Robertson, Jun 13 '18 at 07:06
By the way, comments in PL/SQL use `--` (or `/* ... */`), not `//`. I realise you added them for posting but it's confusing the site doesn't highlight them as comments, and we can't copy & paste your code and see the same errors without removing the `// ` comments. — William Robertson, Jun 13 '18 at 07:11
@ William, i have edited my post to include your comments. Sorry if it caused you any inconvenience in reading/executing the code.Thanks. — Khushi, Jun 13 '18 at 07:24

score 0 · Accepted Answer · answered Jun 13 '18 at 05:44

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;

Bulk Collect with Sum function

1 Answers1