Nested tables example with multiset processing

A lot of people don't use array processing in PL/SQL. I don't know why. If they were programming in some other language they would probably use them all the time. The PL/SQL implementation uses different names like nested table but essentially these are just arrays and have some really useful features.
Like the ability to do complex comparisons of the entire contents of the array with a single line of code.

Here is an example where nested tables are used to work out if a role has any tables that have not been granted select access to a particular role.

What this does is select all the tables that exists in the example_owner schema and put them in one nested table.
Then get a list of all the the tables that have been granted to the ex_tabs_role and put those in another nested table.
Then see if there is any difference between the two nested tables.
If they are not exactly the same then using MULTISET EXCEPT DISTINCT find out which values are in the first nested table (hold the full list of tables) that are not in the second nested table (holds the list of tables that have already been granted to the role).
The resulting nested table (new_tables_nt) is the list of missing tables that we want to grant to the role.

DECLARE
cst_role     CONSTANT varchar2(20) := 'EX_TABS_ROLE';
-- set up a type that will hold a list of table names
TYPE tables_nt IS TABLE OF VARCHAR2(100);
-- set up array to hold tables the role already has
role_tables_nt  tables_nt;
-- set up array to hold tables that exist for schema 
 -- owner
all_tables_nt    tables_nt;
-- set up array to hold the tables that need to be 
 -- added to the role
new_tables_nt   tables_nt := tables_nt();

BEGIN
-- select the full list of tables we want to grant
SELECT  alt.owner || '.' || alt.table_name
BULK COLLECT INTO all_tables_nt
FROM   all_tables alt
WHERE   alt.owner = 'EXAMPLE_OWNER'
AND     alt.iot_type IS NULL;

-- select the list of tables we have already granted
SELECT  dtp.table_schema || '.' || dtp.table_name
BULK COLLECT INTO role_tables_nt
FROM    all_tab_privs dtp
WHERE   grantee = cst_role;

-- We only need to keep processing if there is a 
 -- difference between the sets
-- As we are using nested tables we can just compare 
 -- if the whole table is the same
IF all_tables_nt != role_tables_nt THEN
-- with set logic find the list of tables that have 
  -- not been granted
-- This is full list of tables minus the granted list
-- then store this difference in the new_tables_nt
  -- array
new_tables_nt := all_tables_nt 
               MULTISET EXCEPT DISTINCT role_tables_nt;

-- check if the difference is that we actually need 
  -- to add tables
-- if it is a postive number it means we need to 
  -- add tables
 if new_tables_nt.COUNT > 0 THEN
-- Now actually do the grants of the new tables 
   -- to the role
FOR i IN new_tables_nt.FIRST..new_tables_nt.LAST LOOP
-- display what the differences are
dbms_output.put_line(new_tables_nt(i));
-- Now grant the differences to the role.
EXECUTE IMMEDIATE 'GRANT SELECT ON ' ||
new_tables_nt(i) || ' TO ' ||
cst_role;
END LOOP;
 END IF; -- there are values to add
END IF; -- sets are different
END;

Note that if you don't check for new_tables_nt.COUNT > 0 then when you try to excute the for loop with no details in it you will get

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 40

Because it cannot loop from first to last as new_tables_nt.FIRST and new_tables_nt.LAST will both be NULL.