Procedure to find a specific EXPRESSION in all tables in a specific schema in ORACLE
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!!