Find string in current schema

In some cases developer ask us to find some string in the particular schema. So, I have develop one anonymous block to find the string in tables. It will return the table name and column name in which value is existing.

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     string_to_search_v      VARCHAR(200) := UPPER('KING');
  3     datatype_V              VARCHAR(200) := 'CHAR';
  4     exists_v                NUMBER;
  5  BEGIN
  6  FOR i IN (SELECT
  7                     table_name, column_name
  8       FROM
  9             user_tab_columns
 10       WHERE
 11             data_type LIKE '%'||datatype_v||'%' AND
 12             data_length >= LENGTH(string_to_search_v) AND
 13             table_name NOT LIKE 'BIN$%' )
 14  LOOP
 15     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' ||i.table_name ||' WHERE UPPER('|| i.column_name
 16     ||') like ''%'|| string_to_search_v||'%'''  INTO exists_v;
 17
 18     IF exists_v > 0 THEN
 19        dbms_output.put_line ('String: ' || string_to_search_v || ' Exists in ' || i.table_name
 20        ||':'||i.column_name);
 21     END IF;
 22     END LOOP;
 23  END;
 24  /
String: KING Exists in EMP:ENAME

PL/SQL procedure successfully completed.

SQL>
SQL> 

Hope it will help.