Easy Compare Differences in Tables

SELECT * FROM TABLE_A
MINUS
SELECT * FROM TABLE_B

This can be expanded out to include the actual rows that are different between the two tables, or missing:




SELECT * FROM (

SELECT
'T906_TIME_CODE' AS TABLE_NAME
, '@AURPREP' AS DATABASE_NAME
, DECODE( -- Check if rows are different or missing. Missing if no row found with matching keys.
(SELECT 1 FROM T906_TIME_CODE B
WHERE NVL(B.T906F370_TRAN_CODE_TERM, 'X') = NVL(A.T906F370_TRAN_CODE_TERM, 'X')
AND NVL(B.T906F005_AWARD_CODE, 'X') = NVL(A.T906F005_AWARD_CODE, 'X')
AND NVL(B.T906F010_TIME_CODE, 'X') = NVL(A.T906F010_TIME_CODE, 'X')
AND NVL(B.T906F075_COSTING_ACCOUNT, 'X') = NVL(A.T906F075_COSTING_ACCOUNT, 'X')
AND NVL(B.T906F310_ACCOUNT, 'X') = NVL(A.T906F310_ACCOUNT, 'X')
AND NVL(B.T906F074_PC, 'X') = NVL(A.T906F074_PC, 'X')
AND NVL(B.T906F020_LEAVE_TYPE_AWARD, 'X') = NVL(A.T906F020_LEAVE_TYPE_AWARD, 'X')
AND NVL(B.T906F025_LEAVE_TYPE_CODE, 'X') = NVL(A.T906F025_LEAVE_TYPE_CODE, 'X')
AND NVL(B.T906F060_SECURITY_TASK, 'X') = NVL(A.T906F060_SECURITY_TASK, 'X')
) , 1, 'DIFFERENCES'
, 'MISSING IN CURRENT') AS STATUS
, A.*
FROM
(SELECT * FROM T906_TIME_CODE@AURPREP
MINUS
SELECT * FROM T906_TIME_CODE) A

UNION

SELECT
'T906_TIME_CODE' AS TABLE_NAME
, 'CURRENT' AS DATABASE_NAME
, DECODE( -- Check if rows are different or missing. Missing if no row found with matching keys.
(SELECT 1 FROM T906_TIME_CODE@AURPREP B
WHERE NVL(B.T906F370_TRAN_CODE_TERM, 'X') = NVL(A.T906F370_TRAN_CODE_TERM, 'X')
AND NVL(B.T906F005_AWARD_CODE, 'X') = NVL(A.T906F005_AWARD_CODE, 'X')
AND NVL(B.T906F010_TIME_CODE, 'X') = NVL(A.T906F010_TIME_CODE, 'X')
AND NVL(B.T906F075_COSTING_ACCOUNT, 'X') = NVL(A.T906F075_COSTING_ACCOUNT, 'X')
AND NVL(B.T906F310_ACCOUNT, 'X') = NVL(A.T906F310_ACCOUNT, 'X')
AND NVL(B.T906F074_PC, 'X') = NVL(A.T906F074_PC, 'X')
AND NVL(B.T906F020_LEAVE_TYPE_AWARD, 'X') = NVL(A.T906F020_LEAVE_TYPE_AWARD, 'X')
AND NVL(B.T906F025_LEAVE_TYPE_CODE, 'X') = NVL(A.T906F025_LEAVE_TYPE_CODE, 'X')
AND NVL(B.T906F060_SECURITY_TASK, 'X') = NVL(A.T906F060_SECURITY_TASK, 'X')
) , 1, 'DIFFERENCES'
, 'MISSING IN @AURPREP') AS STATUS
, A.*
FROM
(SELECT * FROM T906_TIME_CODE
MINUS
SELECT * FROM T906_TIME_CODE@AURPREP) A

)
ORDER BY -- (keys)
T906F370_TRAN_CODE_TERM
, T906F005_AWARD_CODE
, T906F010_TIME_CODE
, T906F075_COSTING_ACCOUNT
, T906F310_ACCOUNT
, T906F074_PC
, T906F020_LEAVE_TYPE_AWARD
, T906F025_LEAVE_TYPE_CODE
, T906F060_SECURITY_TASK
, 2
;

PL/SQL Array example




declare

type table_list is table of varchar(128) index by binary_integer;
tabs table_list;
i number;

begin

tabs(1) := 'aaa';
tabs(2) := 'bbb';
tabs(3) := 'ccc';

for i in 1..3
loop
dbms_output.put_line(tabs(i));
end loop;

end;

Simple Select and Loop in PL/SQL

Simple example of using PL/SQL to select and loop through a cursor.
Note: Results are output to the output window.





CREATE OR REPLACE PROCEDURE test(p_table_name in user_tables.table_name%type) IS

TYPE ref_cursor IS REF CURSOR;
l_cursor ref_cursor;

v_query varchar2(5000);
v_name varchar2(64);

BEGIN
v_query := 'select T120F005_COST_CATEGORY_CODE from ' || p_table_name;
OPEN l_cursor FOR v_query;
LOOP
FETCH l_cursor INTO v_name;
EXIT WHEN l_cursor%NOTFOUND;
dbms_output.put_line('Category Code: ' || v_name);
END LOOP;
CLOSE l_cursor;
END;
/

-- Test.
EXEC test('T120_COSTING_CATEGORY');