Need help on getting the updated count while using the FORALL

Steps to replicate Create table:

    create table TEST_UPDATE (name_id number(9), name_name varchar2(40));

Insert data

insert into TEST_UPDATE values (1,'VVV');
insert into TEST_UPDATE values (2,'VVV');
insert into TEST_UPDATE values (3,'VVV');
insert into TEST_UPDATE values (1,'VVV');
insert into TEST_UPDATE values (2,'VVV');
insert into TEST_UPDATE values (3,'VVV');
insert into TEST_UPDATE values (1,'VVV');
insert into TEST_UPDATE values (2,'VVV');
insert into TEST_UPDATE values (1,'VVV');

execute the FORALL update

DECLARE
    TYPE t_ids IS TABLE OF test_update.name_id%TYPE;
    TYPE t_name IS TABLE OF test_update.name_name%TYPE;
    
    l_ids t_ids := t_ids(1,2,3); -- Example employee IDs
    l_name t_name := t_name('XXX','YYY','ZZZ'); -- New salaries
    
    l_updated_ids t_ids;
    l_updated_name t_name;
BEGIN
    FORALL i IN INDICES OF l_ids
        UPDATE test_update
        SET name_name = l_name(i)
        WHERE name_id = l_ids(i)
        RETURNING name_id, name_name BULK COLLECT INTO l_updated_ids, l_updated_name;
    
    -- Output the results
    FOR i IN 1..l_updated_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Updated Employee ID: ' || l_updated_ids(i) || ', name: ' || l_updated_name(i));
    END LOOP;

      
END;

With this getting below output

Updated Employee ID: 1, name: XXX
Updated Employee ID: 1, name: XXX
Updated Employee ID: 1, name: XXX
Updated Employee ID: 1, name: XXX
Updated Employee ID: 2, name: YYY
Updated Employee ID: 2, name: YYY
Updated Employee ID: 2, name: YYY
Updated Employee ID: 3, name: ZZZ
Updated Employee ID: 3, name: ZZZ


I need to get output like 
Total update for Id 1: 4
Total update for Id 2 :3
Total update for Id 3 :2

With sql%count, it gives the total count as 9. But I am not able to find a way to get the output for each entry wise.

Need help on getting the updated count while using the FORALL

Steps to replicate Create table:

    create table TEST_UPDATE (name_id number(9), name_name varchar2(40));

Insert data

insert into TEST_UPDATE values (1,'VVV');
insert into TEST_UPDATE values (2,'VVV');
insert into TEST_UPDATE values (3,'VVV');
insert into TEST_UPDATE values (1,'VVV');
insert into TEST_UPDATE values (2,'VVV');
insert into TEST_UPDATE values (3,'VVV');
insert into TEST_UPDATE values (1,'VVV');
insert into TEST_UPDATE values (2,'VVV');
insert into TEST_UPDATE values (1,'VVV');

execute the FORALL update

DECLARE
    TYPE t_ids IS TABLE OF test_update.name_id%TYPE;
    TYPE t_name IS TABLE OF test_update.name_name%TYPE;
    
    l_ids t_ids := t_ids(1,2,3); -- Example employee IDs
    l_name t_name := t_name('XXX','YYY','ZZZ'); -- New salaries
    
    l_updated_ids t_ids;
    l_updated_name t_name;
BEGIN
    FORALL i IN INDICES OF l_ids
        UPDATE test_update
        SET name_name = l_name(i)
        WHERE name_id = l_ids(i)
        RETURNING name_id, name_name BULK COLLECT INTO l_updated_ids, l_updated_name;
    
    -- Output the results
    FOR i IN 1..l_updated_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Updated Employee ID: ' || l_updated_ids(i) || ', name: ' || l_updated_name(i));
    END LOOP;

      
END;

With this getting below output

Updated Employee ID: 1, name: XXX
Updated Employee ID: 1, name: XXX
Updated Employee ID: 1, name: XXX
Updated Employee ID: 1, name: XXX
Updated Employee ID: 2, name: YYY
Updated Employee ID: 2, name: YYY
Updated Employee ID: 2, name: YYY
Updated Employee ID: 3, name: ZZZ
Updated Employee ID: 3, name: ZZZ


I need to get output like 
Total update for Id 1: 4
Total update for Id 2 :3
Total update for Id 3 :2

With sql%count, it gives the total count as 9. But I am not able to find a way to get the output for each entry wise.

• oracle-database
• plsql
• oracle19c

2 Answers 2

You can use aggregates in the RETURNING clause (though that doesn't seem to be documented clearly). But you can't have a group-by clause, so you need aggregate all of the returned columns. In this case that's OK, you can just do a max or min of the ID; so instead of:

        RETURNING name_id, name_name BULK COLLECT INTO l_updated_ids, l_updated_name;

you can aggregate both:

        RETURNING max(name_id), count(name_name) BULK COLLECT INTO l_updated_ids, l_updated_name;

giving you:

DECLARE
    TYPE t_ids IS TABLE OF test_update.name_id%TYPE;
    TYPE t_name IS TABLE OF test_update.name_name%TYPE;
    
    l_ids t_ids := t_ids(1,2,3); -- Example employee IDs
    l_name t_name := t_name('XXX','YYY','ZZZ'); -- New salaries
    
    l_updated_ids t_ids;
    l_updated_name t_name;
BEGIN
    FORALL i IN INDICES OF l_ids
        UPDATE test_update
        SET name_name = l_name(i)
        WHERE name_id = l_ids(i)
        RETURNING max(name_id), count(name_name) BULK COLLECT INTO l_updated_ids, l_updated_name;
    
    -- Output the results
    FOR i IN 1..l_updated_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Total updates for Employee ID ' || l_updated_ids(i) || ': ' || l_updated_name(i));
    END LOOP;     
END;
/

Total updates for Employee ID 1: 4
Total updates for Employee ID 2: 3
Total updates for Employee ID 3: 2

fiddle

However, if one if the IDs has no matches, the output looks a little odd; but you can refer to the indexed value without aggregating that:

        RETURNING l_ids(i), count(name_name) BULK COLLECT INTO l_updated_ids, l_updated_name;

Then with another pair of values without an ID that doesn't match any rows:

    l_ids t_ids := t_ids(1,2,3,4); -- Example employee IDs
    l_name t_name := t_name('XXX','YYY','ZZZ', 'AAA'); -- New salaries

You see:

Total updates for Employee ID 1: 4
Total updates for Employee ID 2: 3
Total updates for Employee ID 3: 2
Total updates for Employee ID 4: 0

fiddle

One option is to not use FORALL at all, but instead a normal one-row-at-a-time FOR loop, in conjunction with the pseudo variable SQL%ROWCOUNT that gets populated after each DML operation.

DECLARE
    TYPE t_ids IS TABLE OF test_update.name_id%TYPE;
    TYPE t_name IS TABLE OF test_update.name_name%TYPE;
    
    l_ids t_ids := t_ids(1,2,3); -- Example employee IDs
    l_name t_name := t_name('XXX','YYY','ZZZ'); -- New salaries
 BEGIN
    FOR i IN l_ids.FIRST .. l_ids.LAST
    LOOP
        UPDATE test_update
        SET name_name = l_name(i)
        WHERE name_id = l_ids(i);
        
        DBMS_OUTPUT.PUT_LINE('Total update for Id ' || l_ids(i) || ': '||SQL%ROWCOUNT);
    END LOOP;
END;

Output:

Total update for Id 1: 4
Total update for Id 2: 3
Total update for Id 3: 2

Side note: There's really little discernible benefit to FORALL over FOR until your volume gets very large (as it reduces the # of bind and exec operations internally). But the overhead of those calls, especially locally in PL/SQL, is so miniscule that unless you're dealing with something like millions of rows it really isn't worth the reduced control it gives you. And if you do get to volumes that large, there are more efficient mechanisms for mass updates available (e.g. pdml-enabled merge that pulls the collection into SQL, or [when truly massive] redesigning to not do updates or use PL/SQL collections at all, but rather only bulk inserts on work tables). So I personally find FORALL to be of somewhat narrow usefulness. If your volume is moderately low, FOR will suit your needs just fine.