Comparing the result of two queries using set operators

As you certainly know, set operators (intersect, union, union all, minus) allow to perform powerful tasks in an Oracle database.

As i am greatly deeply involved in the development of applications based on Oracle Application Express, you'll forgive me if i exploit this opportunity to use this generic technique to solve a very practical problem:
to compare two versions of the same components of the same application.

Note that the SQL statement i am going to use can be easily adapted to any other similar problem, like comparing two price lists of two different stores or checking the objects belonging to two different schemas.

So, back to my apex application comparison problem, this rather pesky job can be greatly simplified using Oracle database's built-in features since the introduction of the public views for reading the content of Apex's repository.

Suppose that i have a large application containing dozen of pages
I can quickly isolate the pages present in the first application but not in the second with the following query:

select page_id, page_name from apex_application_pages
where application_id = 21670
minus
select page_id, page_name from apex_application_pages
where application_id = 459;

Likewise i can easily drill down the page content and quickly identify the new or missing page items (depending on whether the newer version comes first or last):

select page_id, page_name, item_name, displays_as from apex_application_page_items
where application_id = 21670
minus
select page_id, page_name, item_name, displays_as from apex_application_page_items
where application_id = 459;

Note, as i said before that the order in which you process the two queries is important, so if application 21670 is older than 459, so, presumably containing fewer items, the result set may be empty. This means that if you want to show the newest additions, you should swap the application_ID values:

select page_id, page_name, item_name, displays_as from apex_application_page_items
where application_id = 459
minus
select page_id, page_name, item_name, displays_as from apex_application_page_items
where application_id = 21670;

It's also important to decide which columns must be taken into account in the comparison, if case sensitivity is important or any other details that can make a difference for you, for instance, in the following query i want to check if the page item type is the same or if the source is the same, regardless of the letter case:

select page_id, page_name, item_name, display_as, lower(item_source) as source
from apex_application_page_items
where application_id = 21670
minus
select page_id, page_name, item_name, display_as, lower(item_source) as source
from apex_application_page_items
where application_id = 459;

So, as you see, you can do this kind of comparisons very easily and on arbitrary number of columns, applying the desired "noise filtering" technique.