Schema wide text search

Search full schema for text.

The function 'search' is a simple (but slow) way to find a certain tekst or numer in your Oracle database schema. The result will be in the table 'T_SEARCHRESULT'. Any exceptions will be reported in the 'founderror' column. For example when datatypes are not supported.

drop table t_searchresult;
create table t_searchresult(
	table_name varchar2(50)
	, found NUMBER
	, minrowid rowid
	, founderror clob);

create or replace procedure search(search_txt VARCHAR2) IS
BEGIN
DECLARE
    TYPE curTyp IS REF CURSOR;
    cursor1 curTyp;
    cursor2 curTyp;
    where_txt VARCHAR2(5000);
    v_table_name VARCHAR2(30);
    v_column_name VARCHAR2(100);
    v_column_type VARCHAR2(30);
    v_found number;
    v_message clob;
    v_rowid rowid;
BEGIN 
    delete t_searchresult;
    insert into t_searchresult(table_name,found) values ('search '''||search_txt||'''',0);
    -- all tables:
	OPEN cursor2 FOR 'select tname from tab 
       where tname<>''T_SEARCHRESULT'' and not tname like ''%$%'' and tabtype=''TABLE''
       order by tname';
		LOOP
			FETCH cursor2 INTO v_table_name;
			EXIT WHEN cursor2%NOTFOUND;
      where_txt := NULL;
      -- all columns:
      OPEN cursor1 FOR 'select cname, coltype from col c 
          where tname='''||v_table_name||''' and not coltype in (''BLOB'',''"MDSYS"."SDO_GEOMETRY"'') order by colno';
      LOOP
        FETCH cursor1 INTO v_column_name, v_column_type;
        EXIT WHEN cursor1%NOTFOUND;		
		-- exceptions for certain datatypes
        if v_column_type='NUMBER' then
          v_column_name:='to_char('||v_column_name||')='''||search_txt||'''';
        elsif v_column_type='DATE' then
          v_column_name:='to_char('||v_column_name||',''DD-MM-YYYY'')='''||search_txt||'''';
        else 
          v_column_name:=v_column_name||' like ''%' || search_txt || '%''';
        end if;
        
        IF where_txt is NULL THEN
          where_txt := v_column_name;
        ELSE
          where_txt := where_txt || ' or ' || v_column_name;
        END IF;
      END LOOP;
      CLOSE cursor1;

      BEGIN
        execute immediate 'select count(*), min(rowid) from '||v_table_name||' where '||where_txt into v_found, v_rowid;
        if v_found>0 THEN
          insert into t_searchresult(table_name, found, minrowid) values (v_table_name, v_found, v_rowid);
        END IF;    
      EXCEPTION WHEN OTHERS THEN 
        v_message := 'where=' || where_txt || ', backtrace=' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ', sqlerrm=' || SQLERRM;
        insert into t_searchresult(table_name,founderror) values (v_table_name, v_message);
      END;
	  END LOOP;
	  CLOSE cursor2;
END;
END;
/

call search('Z08.002982');
select * from t_searchresult order by found;