Als PDF Downloaden!
Werbung
Tipps & Tricks: April 2006
Bereich:
PL/SQL
Erstellung:
04/2006
Versionsinfo:
8.1, 9.2, 10.2, 11.1, 11.2
Letzte Überarbeitung:
06/2009 MP
Suche Datensatz in einem Schema
Hatten Sie auch schon einmal das Problem, dass Sie einen Datensatz gesucht haben, aber nicht wussten, in
welcher Spalte dieser steht? Wir haben noch eines drauf gesetzt und können sogar Werte suchen, von denen
man nicht weiß, in welcher Tabelle sie stehen. Diese Fragestellung kann auftreten, wenn man mit einer
Applikation arbeitet, deren genaues Datenbank-Layout nicht bekannt ist.
Es sollte jedoch erwähnt werden, dass hier mehrfach ein Full-Tablescan erzeugt wird, der bei einer großen
Tabellenanzahl zu einer SEHR GROSSEN Performance-Belastung führen kann. Besonders die Option ALL wird
sehr viel Ressourcen verbrauchen, deswegen sollte man die Procedure nur auf Test-Maschinen absetzen.
Vorbereitung
In dieser Procedure werden zwei Data-Dictionary Tabellen (dba_objects und dba_tab_columns) benutzt. Leider
sind Rechte an DD-Objekten über Rollen an die Benutzer vergeben, die wiederum in PL/SQL nicht verwendet
werden können.
Drei Möglichkeiten stehen als Problemlösung zur Verfügung:
1.Sie kompilieren die Procedure als Benutzer SYS, denn der hat alle Rechte. :-)
2.Sie vergeben die beiden Rechte DIREKT an den Benutzer, der dann die Procedure kompiliert:
GRANT SELECT on dba_tab_columns TO system;
GRANT SELECT on dba_objects TO system;
3.Sie schreiben die Procedure in einen anonymen Block um. Ersetzen Sie den Bereich "CREATE OR
REPLACE bis "IS" durch:
ACCEPT v_schema_name
PROMPT "Schema eingeben (ALL für Alle): "
ACCEPT v_search_string PROMPT "Suchstring eingeben (Bsp: Hallo%): "
ACCEPT v_table_name "Tabellennamen eingeben (Leer = alle Tabellen): "
ACCEPT v_type "Datentyp STRING oder NUMBER eingeben: "
DECLARE
p_schema_name VARCHAR2(30):=upper('&&v_schema_name');
p_search_string VARCHAR2(30):='&&v_search_string';
p_table_name VARCHAR2(30):=upper('&&v_table_name');
p_type VARCHAR2(30):='&&v_type';
TYPE ref_curs ...
Folgende Parameter können übergeben werden:
p_search_string
Nimmt den gesuchten String auf. Folgende Möglichkeiten stehen zur Verfügung:
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 4
"STRING", "%STRING" oder "%STRING%"
p_schema_name
Name des Schema, in dem gesucht werden soll. Default ist das aktuelle. Mit der Option ALL werden alle
Schematas durchsucht. Ausgeschlossen sind die Schematas von SYS, SYSTEM und allen SYS-nahen
Benutzern
p_table_name
Hier kann optiomal der Tabellenname angegebn werden, dann werden nur deren Spalten durchsucht.
p_type
Wenn es sich um eine String Spalte (char, varchar2,clob) handelt, wo der gesuchte Wert enthalten ist,
können Sie hier die Option 'STRING' angeben. Die Option 'NUMBER' durchsucht nummerische Spalten.
Procedure:
CREATE OR REPLACE PROCEDURE find_in_tables
(p_search_string IN VARCHAR2,
p_schema_name IN VARCHAR2 DEFAULT NULL,
p_table_name
IN VARCHAR2 DEFAULT NULL,
p_type IN VARCHAR2 DEFAULT 'STRING')
uthid current_user
IS
TYPE ref_curs_type IS REF CURSOR;
refc ref_curs_type;
sql_str
VARCHAR2(200);
row_ret
ROWID;
v_user VARCHAR2(30);
BEGIN
/* Wenn Benutzer NULL ist wir aktueller Benutzer verwendet */
SELECT sys_context('USERENV', 'SESSION_USER') INTO v_user FROM dual;
v_user := nvl(p_schema_name, v_user);
dbms_output.put_line
('Suche wurde ausgeführt mit: User='||v_user||' Table='||p_table_name);
FOR rec IN (select do.owner,table_name, column_name, data_type
FROM sys.dba_tab_columns dtc, dba_objects do
WHERE dtc.table_name = do.object_name
and do.object_type = 'TABLE'
and dtc.owner = decode(v_user,'ALL',dtc.owner,v_user)
and dtc.owner not in ('SYS','SYSTEM','OUTLN','SYSMAN',
'MDSYS','ORDSYS','WMSYS','CTXSYS',
'OLAPSYS')
and do.object_name = nvl(p_table_name, do.object_name)) LOOP
IF upper(p_type) = 'STRING' AND
(rec.data_type = 'CHAR' OR rec.data_type = 'VARCHAR2' or
rec.data_type = 'CLOB') THEN
sql_str := 'SELECT rowid FROM ' || rec.owner || '.' ||
rec.table_name ||' WHERE ' || rec.column_name || ' like ' ||
chr(39) || p_search_string || chr(39);
BEGIN
OPEN refc FOR sql_str;
LOOP
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 4
FETCH refc
INTO row_ret;
EXIT WHEN refc%NOTFOUND;
dbms_output.put_line('Owner='||rec.owner||' Table=' ||
rec.table_name || ' Col=' ||rec.column_name || ' Rowid=' ||
row_ret);
END LOOP;
row_ret := null;
CLOSE refc;
EXCEPTION
WHEN OTHERS THEN
IF v_user<>'ALL' THEN
dbms_output.put_line('Fehler in Table=' || rec.table_name);
END IF;
END;
elsif p_type = 'Number' and rec.data_type = 'NUMBER' THEN
sql_str := 'SELECT rowid FROM ' || p_schema_name || '.' ||
rec.table_name || ' WHERE ' || rec.column_name || '='
|| p_search_string;
BEGIN
OPEN refc FOR sql_str;
LOOP
FETCH refc
INTO row_ret;
EXIT WHEN refc%NOTFOUND;
dbms_output.put_line('Owner='||rec.owner||' Table=' ||
rec.table_name || ' Col=' ||rec.column_name || ' Rowid='
|| row_ret);
END LOOP;
row_ret := null;
CLOSE refc;
EXCEPTION
WHEN OTHERS THEN
IF v_user<>'ALL' THEN
dbms_output.put_line('Fehler in Table=' || rec.table_name);
END IF;
END;
END IF;
END LOOP;
END;
/
Beispielaufrufe:
SQL> EXEC find_in_tables ('PRESI%','SCOTT','EMP');
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 4
Ausgabe: Table=EMP Col=JOB Rowid=AAANOxAAEAAAAAdAAI
SQL> EXEC find_in_tables ('DALL%','ALL');
Ausgabe: Table=DEPT Col=LOC Rowid=AAANOvAAEAAAAANAAB
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 4
