Procedure to find a specific EXPRESSION in all tables in a specific schema in ORACLE Hope you're doing well.
Since I'm new to Oracle database and PL/SQL it's a bit difficult for me to understand complicated queries and now I'm trying to figure out what this procedure exactly does.I think it tries to find a specific expression but I do not understand the way  it tries to do that
This is the procedure body:

    create or replace procedure find_expr is     
      a number;
      begin
        for rec in (select 'select count(*) from ' || table_name || ' where ' ||
                            column_name || ' like ''%05510%''' col_name
                    from user_tab_columns t) loop
    EXECUTE IMMEDIATE rec.col_name
      into a;

    if a &gt; 0 then
      insert into find_expr_log values (sysdate, substr(rec.col_name, 22, 50)); 
      commit;
    end if;

  end loop;
exception
  when others then
    null;
end;
I have a different approach to the same.

Adding the answer on top of the correct answer, just to reduce the manual work.

The following query will directly identify the number of rows in each table which is matching the criteria.

select table_name, column_name,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(1) as c '
      || 'from ' || table_name || ' WHERE ' || column_name || ' like ''%05510%'''))
  returning content)) as c
from user_tab_columns
WHERE DATA_TYPE IN ('VARCHAR2','NUMBER')  -- ALL DATATYPES YOU WANT TO SEARCH
ORDER BY 3 DESC;


Cheers!!