Search for a string across all table in Oracle

search.sql

-- Source of inspiration of this snippet
-- https://www.experts-exchange.com/questions/27396707/Search-for-a-string-across-all-tables-in-oracle.html#a42581782

declare
    num_rows number;
    sql_text varchar2(2500);
    sql_info varchar2(1000);
    v_text varchar2(100) := '''%value_in_lowercase%''';
begin
    dbms_output.enable(1000000);
    for x in (select table_name, column_name,OWNER, data_type, data_length from ALL_tab_columns
               where data_type in ('VARCHAR','VARCHAR2')
                 and column_name IS NOT NULL
                 -- put your rules here to limit tables
                 and table_name NOT LIKE 'TMP_%'
--                  and table_name NOT IN ('SIS_CONF')
                 and owner = 'SYSTEM' -- change to your schema owner name
            order by owner,table_name,column_name)
    loop
        sql_text:='SELECT COUNT(*) INTO :num_rows FROM '||X.OWNER||'.'||x.table_name||' WHERE LOWER('||x.column_name||') LIKE '||v_text|| ' -- ' || x.data_type || ' ' || x.data_length;
--         dbms_output.put_line (sql_text); -- uncomment this line to debug and discover what table to add in filters
        execute immediate sql_text into num_rows;
        if num_rows>0
        then
            sql_info:='SELECT X.'||X.COLUMN_NAME||', X.* FROM '||X.OWNER||'.'||x.table_name||' X ' ||
                      'WHERE LOWER('||x.column_name||') LIKE '||v_text ||' ORDER BY X.'||X.COLUMN_NAME||'; -- '||X.COLUMN_NAME;
            dbms_output.put_line (sql_info);
        end if;
    end loop;
end;