CREATE OR REPLACE PACKAGE BODY vergleich_11g AS v_vergleichsname VARCHAR2(30); v_scan_info DBMS_COMPARISON.COMPARISON_TYPE; /* diese private Funktion überprüft, ob der Vergleichsname schon existiert. Wenn das der Fall ist, wird der Vergleich gelöscht und neu angelegt.*/ FUNCTION name_suchen (p_vergleichsname IN VARCHAR2) RETURN VARCHAR2 AS v_namecount NUMBER; BEGIN SELECT COUNT(*) INTO v_namecount FROM user_comparison WHERE comparison_name = UPPER(p_vergleichsname); IF v_namecount <> 0 THEN DBMS_OUTPUT.PUT_LINE ('Vergleichsname schon vorhanden. Vergleich wird gelöscht und neu erstellt.'); loeschen(p_vergleichsname); END IF; v_vergleichsname := p_vergleichsname; RETURN v_vergleichsname; END name_suchen; /* Die Prozedur vergleichen erstellt ein Vergleichstemplat, führt den Vergleich durch, gibt optional einen Bericht über die Differenzen aus und synchronisiert die Tabellen (ebenfalls optional. Per Default werden alle Spalten der Tabele verglichen, ansonsten kann man eine Spaltenliste angeben, hier als Beispiel 4 Spalten, wobei der Primärschlüssel unbedingt angegeben werden muss.*/ PROCEDURE vergleichen (p_vergleichsname IN VARCHAR2, p_schema_lokal IN VARCHAR2, p_basistabelle IN VARCHAR2, p_schema_remote IN VARCHAR2 DEFAULT NULL, -- NULL für den Vergleich im selben Schema p_vergleichstabelle IN VARCHAR2, p_dblinkname IN VARCHAR2 DEFAULT NULL, p_spaltepk IN VARCHAR2 DEFAULT NULL, p_spalte1 IN VARCHAR2 DEFAULT NULL, p_spalte2 IN VARCHAR2 DEFAULT NULL, p_spalte3 IN VARCHAR2 DEFAULT NULL, p_diff_bericht IN BOOLEAN DEFAULT FALSE, p_sync IN NUMBER DEFAULT 0) AS v_spaltenliste VARCHAR2(3000); v_scan_id NUMBER; v_gleich BOOLEAN; v_zahl NUMBER; BEGIN /* Die angegebenen Spalten werden konkateniert, Replace entfernt doppelte Kommata, falls z.B. p_spalte1 ausgelassen wurde, RTRIM entfernt Kommas von rechts, falls z.B. p_spalte3 fehlt*/ v_spaltenliste := RTRIM(REPLACE(p_spaltepk||','||p_spalte1||','||p_spalte2||','||p_spalte3, ',,', ','),','); v_vergleichsname := name_suchen(p_vergleichsname); -- Das Templat wird angelegt DBMS_COMPARISON.create_comparison ( comparison_name => v_vergleichsname, schema_name => p_schema_lokal, object_name => p_basistabelle, remote_schema_name => p_schema_remote, dblink_name => p_dblinkname, remote_object_name => p_vergleichstabelle, column_list => v_spaltenliste); /* Die Funktion DBMS_COMPARISON.compare vergleicht die Hash-Werte der beiden Objekte und gibt die Information zurück, ob sie identisch (v_gleich ist dann TRUE) oder verschieden (v_gleich = FALSE) sowie die ID des Scans (Sie wird im Feld scan_info.scan_id des OUT-Parameters scan_info gespeichert. Die anderen Spalten dieses vordefinierten Records bleiben zunächst leer). Um Zeit zu sparen, wird der Parameter perform_row_dif zuerst auf seinem DEFAULT-Wert FALSE belassen und erst auf TRUE gesetzt, wenn Unterschiede gefunden wurden. Die Unterschiede werden in der View user/dba_comparison_row_dif gespeichert. */ v_gleich := DBMS_COMPARISON.compare( comparison_name => v_vergleichsname, scan_info => v_scan_info, perform_row_dif => FALSE ); v_scan_id := v_scan_info.scan_id; DBMS_OUTPUT.PUT_LINE('aktuelle Scan-Nr: '||v_scan_id); IF v_gleich = TRUE THEN DBMS_OUTPUT.PUT_LINE('Die Objekte stimmen überein'); ELSE v_gleich := DBMS_COMPARISON.compare( comparison_name => v_vergleichsname, scan_info => v_scan_info , perform_row_dif => TRUE ); SELECT current_dif_count INTO v_zahl FROM user_comparison_scan_summary WHERE scan_id = v_scan_info.scan_id; DBMS_OUTPUT.PUT_LINE('Es wurden '||v_zahl||' Unterschiede gefunden.'); END IF; /* falls eine Ausgabe gewünscht ist, muss p_diff_bericht auf TRUE gesetzt werden. Dann werden die Unterschiede über die Prozedur diff_uebersicht ermittelt und in die Tabelle Ausgabe geschriebent */ IF p_diff_bericht THEN diff_uebersicht (v_vergleichsname); END IF; /* falls eine Synchronisation gewünscht ist, muss der Parameter p_sync auf 1 (Vergleichstabelle wird überschrieben) oder 2 (Basistabelle wird überschrieben) gesetzt werden. */ IF p_sync IN (1,2) THEN synchronisieren( p_vergleichsname => v_vergleichsname, p_scan_id => v_scan_id, p_richtung => p_sync); END IF; EXCEPTION WHEN OTHERS THEN RAISE; END vergleichen; /* Die Prozedur diff_übersicht stellt Informationen aus dem Vergleich zusammen und schreibt sie in die Tabelle Ausgabe */ PROCEDURE diff_uebersicht (p_vergleichsname IN VARCHAR2) AS CURSOR vergl_cur IS /* Ein Join der Views user_comparison_row_dif und user_comparison liefert Informationen, für welche Index-Werte sich die Datensätze in den 2 Tabellen unterscheiden bzw. wo Datensätze fehlen. */ SELECT c.comparison_name, c.schema_name, c.object_name, c.remote_schema_name, c.dblink_name, c.remote_object_name, r.index_value, r.scan_id, DECODE (r.local_rowid, NULL, 'nein', 'ja') in_basis, DECODE (r.remote_rowid, NULL, 'nein', 'ja') in_vergleich FROM user_comparison_row_dif r JOIN user_comparison c ON r.comparison_name = c.comparison_name WHERE r.comparison_name = UPPER(p_vergleichsname); TYPE vergl_t_type IS TABLE OF vergl_cur%ROWTYPE INDEX BY BINARY_INTEGER; vergl_t vergl_t_type; v_nein_lokal NUMBER := 0; v_nein_remote NUMBER := 0; v_diff_werte NUMBER := 0; BEGIN -- die Tabelle Ausgabe wird geleert EXECUTE IMMEDIATE 'TRUNCATE TABLE ausgabe'; -- Das associative Array vergl_t wird über den Cursor gefüllt OPEN vergl_cur; FETCH vergl_cur BULK COLLECT INTO vergl_t; CLOSE vergl_cur; FORALL i IN INDICES OF vergl_t INSERT INTO ausgabe VALUES vergl_t(i); COMMIT; IF vergl_t.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('Vergleichsname falsch oder Tabellen identisch !'); ELSE FOR i IN 1..vergl_t.COUNT LOOP IF vergl_t(i).in_basis = 'ja' AND vergl_t(i).in_vergleich = 'ja' THEN v_diff_werte := v_diff_werte + 1; ELSIF vergl_t(i).in_basis = 'ja' AND vergl_t(i).in_vergleich = 'nein' THEN v_nein_remote := v_nein_remote + 1; ELSIF vergl_t(i).in_basis = 'nein' AND vergl_t(i).in_vergleich = 'ja' THEN v_nein_lokal := v_nein_lokal + 1; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(v_diff_werte ||' Zeilen haben unterschiedliche Werte'); DBMS_OUTPUT.PUT_LINE(v_nein_lokal ||' Zeilen fehlen in der Basistabelle'); DBMS_OUTPUT.PUT_LINE(v_nein_remote||' Zeilen fehlen in der Vergleichstabelle'); END IF; EXCEPTION WHEN OTHERS THEN RAISE; END diff_uebersicht; PROCEDURE synchronisieren( p_vergleichsname IN VARCHAR2, p_scan_id IN NUMBER , p_richtung IN NUMBER DEFAULT 1 ) AS BEGIN IF p_richtung = 1 THEN -- Vergleichstabelle überschreiben /* Die Prozedur DBMS_COMPARISON.converge synchronisiert die beiden Tabellen. Per default wird die Vergleichstabelle (remote_object) überschrieben. */ DBMS_COMPARISON.converge( comparison_name => p_vergleichsname, scan_id => p_scan_id, scan_info => v_scan_info, converge_options => DBMS_COMPARISON.cmp_converge_local_wins, perform_commit => FALSE); DBMS_OUTPUT.PUT_LINE (v_scan_info.rmt_rows_merged ||' Zeilen in der Vergleichstabelle überschrieben'); DBMS_OUTPUT.PUT_LINE (v_scan_info.rmt_rows_deleted||' Zeilen in der Vergleichstabelle gelöscht'); ELSIF p_richtung = 2 THEN -- Basistabelle überschreiben DBMS_COMPARISON.converge( comparison_name => p_vergleichsname, scan_id => p_scan_id, scan_info => v_scan_info, converge_options => DBMS_COMPARISON.cmp_converge_remote_wins, perform_commit => FALSE); DBMS_OUTPUT.PUT_LINE (v_scan_info.loc_rows_merged ||' Zeilen in der Basistabelle überschrieben'); DBMS_OUTPUT.PUT_LINE (v_scan_info.loc_rows_deleted||' Zeilen in der Basistabelle gelöscht'); END IF; COMMIT; EXCEPTION WHEN OTHERS THEN RAISE; END synchronisieren; PROCEDURE loeschen(p_vergleichsname IN VARCHAR2) AS v_namecount NUMBER; BEGIN SELECT COUNT(*) INTO v_namecount FROM user_comparison WHERE comparison_name = UPPER(p_vergleichsname); IF v_namecount = 0 THEN DBMS_OUTPUT.PUT_LINE('Vergleich nicht vorhanden'); END IF; DBMS_COMPARISON.drop_comparison (UPPER(p_vergleichsname)); EXCEPTION WHEN OTHERS THEN RAISE; END loeschen; END vergleich_11g;