Oracle Minus Functionality

At work we often run into user requests which require us to update a long-standing system, while still preserving the legacy data in its original state. Like any good developer, we go through a significant testing phase to ensure that the new tables still match the old tables. Inevitably this involves using row counts and minuses to determine validity.

The provided 'minus' function works like so:
select x from tab1
minus
select y from tab2
This would show all the rows in tab1 that aren't in tab2, in other words, a one-way minus. Rather than copy-pasting yet again, I wrote a PL/SQL procedure that can easily be re-used.

CREATE OR REPLACE PROCEDURE MINUS_BOTH

(

              QUERY1  VARCHAR2

             ,QUERY2  VARCHAR2

             ,RESULT1 IN OUT SYS_REFCURSOR

             ,RESULT2 IN OUT SYS_REFCURSOR

) IS

     /***

     ** Procedure: MINUS_BOTH

     ** Params:  QUERY1 - The first query to be used, QUERY2 - Second query to be used, RESULT1 - Cursor containing rows of first

     **              minus, RESULT2 - Cursor containing rows of second minus. 

     ** Returns: Two sys_refcursors which contain the results of their respective minuses

     ** Description: Takes in two sql queries, and minuses them against each other. The first result is QUERY1 minus QUERY2, the second

     **                  is the opposite. 

     ***/

              V_SQL CLOB;

BEGIN

              V_SQL := QUERY1 || ' minus ' || QUERY2;

              OPEN RESULT1 FOR V_SQL;

              V_SQL := QUERY2 || ' minus ' || QUERY1;

              OPEN RESULT2 FOR V_SQL;

EXCEPTION

              WHEN OTHERS THEN

                            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END MINUS_BOTH;

------------------------

It's pretty straightforward to use, just pass in your sql queries, and watch Oracle magic happen. I plan to add some exception handling in order to make the procedure more robust, and will update the post accordingly. 

Enjoy!