Search for a specific text in the whole Oracle schema

gistfile1.txt

set serveroutput on size 100000;
declare
    --Text to find, case insensitive. Use % for like.
    V_TEXT varchar(50) := '%what I want to locate%';
    --Tables schema
    V_SCHEMA varchar(50) := 'SCHEMA_NAME';
    --List of tables to skip; comma-separated or NONE
    V_SKIP_NAMES varchar(200) := 'NONE';
    --Using temporary table
    V_TEMPORARY_TABLE number := 0;
    -----------------------------
    V_TOTAL_TABLES number := 0;
    V_TOTAL_COLUMNS number := 0;
    V_TOTAL_RESULT number := 0;
    V_TIMER number;
    V_QUERY varchar(4000);
    --Select all schema's tables
    cursor 
        V_TABLE_LIST
    is
        select 
            OBJECT_NAME
        from 
            ALL_OBJECTS
        where 
            (OBJECT_TYPE = 'TABLE')
            and (OWNER = V_SCHEMA)
            --Eventual exclusions
            and (OBJECT_NAME not in (V_SKIP_NAMES))
            --Debug purposes
            --and (OBJECT_NAME in ('GM_SFL_SERVICE_FEE_DETAILS'))
        order by 
            OBJECT_NAME
    ;
begin
    V_TIMER := dbms_utility.get_time();
    begin
        if (V_TEMPORARY_TABLE = 1) then
            execute immediate 'create global temporary table TMP_LOOKUP_TEXT (SCHEMA_NAME varchar(50), TABLE_NAME varchar2(50), COLUMN_NAME varchar2(50), COLUMN_VALUE varchar2(1000)) on commit preserve rows';
        end if;
    exception when others then
        dbms_output.put_line('Fail to create temporary table: ' || sqlerrm);
    end;
    --For each table
    for V_TABLE_ITEM in V_TABLE_LIST loop
        V_TOTAL_TABLES := V_TOTAL_TABLES + 1;
        --dbms_output.put_line('Checking table: ' || V_TABLE_ITEM.OBJECT_NAME);
        declare
            --Select all table's columns
            cursor 
                V_COLUMN_LIST
            is
                select 
                    OWNER as SCHEMA_NAME
                    , TABLE_NAME
                    , COLUMN_NAME
                    , DATA_TYPE
                from 
                    SYS.ALL_TAB_COLUMNS 
                where 
                    (OWNER = V_SCHEMA) 
                    and (TABLE_NAME = V_TABLE_ITEM.OBJECT_NAME) 
                    and (COLUMN_NAME not in (V_SKIP_NAMES))
                    and (DATA_TYPE in ('NVARCHAR2', 'CHAR', 'VARCHAR2'))
                order by
                    COLUMN_NAME
            ;
        begin
            --For each column
            for V_COLUMN_ITEM in V_COLUMN_LIST loop
                V_TOTAL_COLUMNS := V_TOTAL_COLUMNS + 1;
                --dbms_output.put_line('Checking column: ' || V_COLUMN_ITEM.COLUMN_NAME);
                V_QUERY := 'select ' || V_COLUMN_ITEM.COLUMN_NAME || ' from ' || V_COLUMN_ITEM.SCHEMA_NAME || '.' || V_TABLE_ITEM.OBJECT_NAME || ' where upper(' || V_COLUMN_ITEM.COLUMN_NAME || ') like upper(' || chr(39) || V_TEXT || chr(39) || ')';
                --dbms_output.put_line('Query: ' || V_QUERY);
                declare
                    type V_MATCH_RECORD is record 
                    (
                        V_VALUE varchar2(10000)
                    );
                    type V_MATCH_TABLE is table of V_MATCH_RECORD;
                    type V_MATCH_CURSOR_TYPE is ref cursor;
                    V_MATCH_LIST V_MATCH_TABLE;
                    V_MATCH_SELECT V_MATCH_CURSOR_TYPE;                    
                    V_INDEX binary_integer;
                begin
                    --Select columns where the value matches
                    open V_MATCH_SELECT for V_QUERY;
                    fetch V_MATCH_SELECT bulk collect into V_MATCH_LIST;
                    close V_MATCH_SELECT;
                    --Found?
                    if (V_MATCH_LIST.count > 0) then
                        V_INDEX := V_MATCH_LIST.first;
                        while V_INDEX is not null loop
                            V_TOTAL_RESULT := V_TOTAL_RESULT + 1;
                            if (V_TEMPORARY_TABLE = 0) then
                                dbms_output.put_line(V_COLUMN_ITEM.SCHEMA_NAME || '.' || V_COLUMN_ITEM.TABLE_NAME || '.' || V_COLUMN_ITEM.COLUMN_NAME || ' = ' || substr(V_MATCH_LIST(V_INDEX).V_VALUE, 1, 50));
                            else
                                execute immediate 'insert into TMP_LOOKUP_TEXT (SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, COLUMN_VALUE) values (V_COLUMN_ITEM.SCHEMA_NAME, V_COLUMN_ITEM.TABLE_NAME, V_COLUMN_ITEM.COLUMN_NAME, V_MATCH_LIST(V_INDEX).V_VALUE)';
                            end if;
                            V_INDEX := V_MATCH_LIST.next(V_INDEX);
                        end loop;
                    end if;
                exception when others then
                    dbms_output.put_line('Fail to report match: ' || sqlerrm);
                end;
            end loop;
        end;
    end loop;
    V_TIMER := dbms_utility.get_time() - V_TIMER;
    dbms_output.put_line('Read ' || V_TOTAL_COLUMNS || ' columns from ' || V_TOTAL_TABLES || ' tables in ' || (V_TIMER / 1000) || ' seconds. Found ' || V_TOTAL_RESULT || ' match to ' || chr(39) || V_TEXT || chr(39) || ' in schema ' || V_SCHEMA || '.');
    begin
        if (V_TEMPORARY_TABLE = 1) then
            execute immediate 'select * from TMP_LOOKUP_TEXT order by TABLE_NAME, COLUMN_NAME';
        end if;
    exception when others then
        dbms_output.put_line('Fail to list result in temporary table: ' || sqlerrm);
    end;
end;