How to use an outerjoin to synchronise data

There is often a need to synchronize data from a query. For example reading external data and update the tables. Sometimes it can be achieved by a single merge statement, but this is not always possible. Mind, for example, the need to update more than one table, or updating the data using an api.
As an example we want to grant and revoke privileges on database objects. Tables needing select, insert, update and delete privileges, packages execute and views select privileges. The table and packages also get the grant option for the select and execute privilege.

Sure this can be achieved by deleting all privileges and grant the correct privileges, but this will invalidate al dependent objects. Especially when there are little or no changes.
When using a full outerjoin query and single a loop it’s possible to update only the changes. The source query wich contains all the necessary privileges is joined, by a cartesian product, with the users who need this privileges, to list all privileges for all users. The other side is a query with the current privileges. A where clause is added to only select the differences to process.

select db.object_name db_object_name, req.object_name req_object_name,
       nvl(db.privilege, req.privilege) privilege,
       nvl(db.grantee, req.grantee) grantee,
       req.grantable grantable
from (select object_name, privilege, grantable, grantee
      from (select obj.object_name, 'SELECT' privilege, 'YES' grantable
            from user_objects obj
            where obj.object_type = 'TABLE'
            union all
            select obj.object_name, 'SELECT', 'NO'
            from user_objects obj
            where obj.object_type = 'VIEW'
            union all
            select obj.object_name, 'INSERT', 'NO'
            from user_objects obj
            where obj.object_type = 'TABLE'
            union all
            select obj.object_name, 'UPDATE', 'NO'
            from user_objects obj
            where obj.object_type = 'TABLE'
            union all
            select obj.object_name, 'DELETE', 'NO'
            from user_objects obj
            where obj.object_type = 'TABLE'
            union all
            select obj.object_name, 'EXECUTE', 'YES'
            from user_objects obj
            where obj.object_type = 'PACKAGE'),
          (select username grantee
           from all_users usr
           where usr.username in ('USER_A', 'USER_B'))) req
     full outer join
     (select prv.table_name object_name, prv.privilege, prv.grantable, prv.grantee
      from user_tab_privs prv
      where prv.grantee in ('USER_A', 'USER_B')) db
     on (db.object_name = req.object_name and db.privilege = req.privilege)
where (db.object_name is null or req.object_name is null)
   or (db.grantable != req.grantable)

When looping over this records we can recognize 3 different situations.

db_object is null and req_object is not null: Privilege is not present in the database, action: Grant the privilege
db_object is not null and req_object is null: Privilege is not desired , action: Revoke the privilege
otherwise: The privilege is present in the database, but the grant option is not set correct.

See the privs.sql script for the complete example.

This technique is simple en leverages easy understanding code what’s applicable for many synchronization challenges. Beside this it’s much more effective than loop over an input table/query and firing individual queries and update the data when necessary.