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:

• Sie kompilieren die Procedure als Benutzer SYS, denn der hat alle Rechte. :-)
• 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;

• 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:
  "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 optional der Tabellenname angegeben 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
          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');

Ausgabe: Table=EMP Col=JOB Rowid=AAANOxAAEAAAAAdAAI

SQL> EXEC find_in_tables ('DALL%','ALL');

Ausgabe: Table=DEPT Col=LOC Rowid=AAANOvAAEAAAAANAAB