Schema wide text search

Search full schema for text.

The pl/sql block is a simple (but slow) way to find a certain text or number in your Oracle database schema. The result will be in the table 'tmp_searchresult'. Any exceptions will be reported in the 'found_error' column. For example when datatypes are not supported.

-- create
drop table tmp_searchresult;
create table tmp_searchresult(
    speed timestamp default systimestamp
  , table_name varchar2(32)
  , found_count NUMBER
  , table_count number
  , first_rowid rowid
  , found_error clob)
  ;

-- search
BEGIN
DECLARE
    search_txt VARCHAR2(50) := 'Stichting Waternet';
    search_num NUMBER;
    search_dat date;
    TYPE curTyp IS REF CURSOR;
    cursor1 curTyp;
    cursor2 curTyp;
    where_txt CLOB;
    v_sql VARCHAR2(10000);
    v_table_name VARCHAR2(40);
    v_column_name VARCHAR2(40);
    v_column_where VARCHAR2(100);
    v_column_type VARCHAR2(40);
    v_found number;
    v_message clob;
    v_rowid rowid;
    v_id NUMBER;
    v_tables_count number := 0;
BEGIN
    delete tmp_searchresult;
    OPEN cursor2 FOR 'select table_name 
        from user_tables 
        where not table_name like ''TMP%''
          and not table_name like ''%$%''
        order by table_name';
    LOOP
      FETCH cursor2 INTO v_table_name;
      EXIT WHEN cursor2%NOTFOUND;
      v_tables_count := v_tables_count + 1;
      where_txt := NULL;
      BEGIN
        search_num:=TO_NUMBER(search_txt);
      EXCEPTION WHEN OTHERS THEN
        search_num:=NULL;
      END;
      BEGIN
        search_dat:=TO_DATE(search_txt,'DD-MM-YYYY');
      EXCEPTION WHEN OTHERS THEN
        search_dat:=NULL;
      END;
      OPEN cursor1 FOR 'select column_name, data_type from cols  
            where not DATA_TYPE in (''BLOB'',''SDO_GEOMETRY'',''LONG'')
              and table_name='''||v_table_name||''' order by COLUMN_ID';
      LOOP
          FETCH cursor1 INTO v_column_name, v_column_type;
          EXIT WHEN cursor1%NOTFOUND;
          IF NOT search_num is NULL THEN
            if v_column_type='NUMBER' then
              v_column_where:='"'||v_column_name||'"'||'='||search_txt;
            else
              v_column_where:=NULL;
            end if;
          ELSIF NOT search_dat is NULL THEN
            if v_column_type='DATE' then
              v_column_where:='upper(to_char('||'"'||v_column_name||'"'||',''DD-MM-YYYY''))=upper('''||search_txt||''')';
            else
              v_column_where:=NULL;
            end if;
          ELSIF NOT search_txt is NULL THEN
            if v_column_type='NUMBER' or v_column_type='DATE' then
              v_column_where:=NULL;
            else
              v_column_where:='upper("'||v_column_name||'")'||' like ''%' || upper(search_txt) || '%''';
            end if;
          END IF;
          IF NOT v_column_where is NULL THEN
            IF where_txt is NULL THEN
              where_txt := v_column_where;
            ELSE
              where_txt := where_txt || ' OR ' || v_column_where;
            END IF;
          END IF;
      END LOOP;
      CLOSE cursor1;
      IF NOT where_txt is NULL THEN
        BEGIN
            v_sql:='insert into tmp_searchresult(table_name, found_count, first_rowid)
                    select :v_table_name, count(*), max(rowid)
                      from  "'||v_table_name||'" 
                      where '||where_txt
                      ;
            execute immediate v_sql using v_table_name;
            commit;
        EXCEPTION WHEN OTHERS THEN
            v_message := 'sql=' || v_sql || ', backtrace=' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ', sqlerrm=' || SQLERRM;
            insert into tmp_searchresult(speed,table_name,found_error) values (sysdate,v_table_name, v_message);
            commit;
        END;
      END IF;
    END LOOP;
    CLOSE cursor2;
    insert into tmp_searchresult(table_name, found_count, table_count, first_rowid) values ('<searched>', null, v_tables_count, null);    
END;
END;
/

-- result
select 'select * from "'||table_name||'" where rowid = '''||FIRST_ROWID||'''; --'||FOUND_COUNT 
  from tmp_searchresult 
  where FOUND_COUNT>0
  order by table_name
  ;