Nice business requirement.

What you need is a query that would write the query you need, here are the specs:

1- select from dba_tab_columns where schema is one of the schemas you want to search AND the column has a datatype like varchar2 -I suppose you don't want to search date and number columns, don't you?

2- rework your query to add the "select" syntax including a where clause in the form "liike '%JOHN SMITH%'.

3- last but not least don't forget to wrap column name on upper() function.

Your query should provide you one query for each searchable column.