Find table name in scheme using string in Oracle pl/sql

Hi Code seeker,

I attend Data Science interview, they ask sql and pl/sql question. but they ask one IMP question.
suppose in one schema, there is 1000 table. and i gave you string 'SMITH' to find table name,
which store string 'SMITH' value.
Means you don't know table name and find table name who store 'SMITH' value.

Below is example of find table name using string.

CREATE OR REPLACE FUNCTION ALAM.FIND_IN_SCHEMA_NEW(VAL VARCHAR2)
RETURN VARCHAR2 IS
  V_OLD_TABLE USER_TAB_COLUMNS.TABLE_NAME%TYPE;
  V_WHERE     VARCHAR2(4000);
  V_FIRST_COL BOOLEAN := TRUE;
  TYPE RC     IS REF CURSOR;
  C           RC;
  V_ROWID     VARCHAR2(20);
BEGIN
  FOR R IN (
    SELECT
      T.*
    FROM
      USER_TAB_COLS T, USER_ALL_TABLES A
    WHERE T.TABLE_NAME = A.TABLE_NAME
      AND T.DATA_TYPE LIKE '%CHAR%'
    ORDER BY T.TABLE_NAME) LOOP
    IF V_OLD_TABLE IS NULL THEN
      V_OLD_TABLE := R.TABLE_NAME;
    END IF;
    IF V_OLD_TABLE <> R.TABLE_NAME THEN
      V_FIRST_COL := TRUE;
      DBMS_OUTPUT.PUT_LINE('searching ' || V_OLD_TABLE);
      OPEN C FOR 'select rowid from "' || V_OLD_TABLE || '" ' || V_WHERE;
      FETCH C INTO V_ROWID;
      LOOP
        EXIT WHEN C%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(VAL ||' Value in '||'  rowid: ' || V_ROWID || ' in ' || V_OLD_TABLE);
        FETCH C INTO V_ROWID;
      END LOOP;
      V_OLD_TABLE := R.TABLE_NAME;
    END IF;
    IF V_FIRST_COL THEN
      V_WHERE := ' where ' || R.COLUMN_NAME || ' like ''%' || VAL || '%''';
      V_FIRST_COL := FALSE;
    ELSE
      V_WHERE := V_WHERE || ' or ' || R.COLUMN_NAME || ' like ''%' || VAL || '%''';
    END IF;
  END LOOP;
  RETURN 'Success';
END;
/
SQL> set serveroutput on size 1000000 format wrapped

SQL> select FIND_IN_SCHEMA_NEW ('SMITH') from dual;

FIND_IN_SCHEMA_NEW('SMITH')
--------------------------------------------------------------------------------
Success

searching ABC
searching ACCUMULATED_RECEIPT
searching BADLOG
searching CUSTOMER_BATCH_SELECTION
searching CUSTOMER_MARGIN
searching CUSTOMER_ORDER_EXCEL_UPLOAD
searching DEPT
searching DEPT_COPY
searching DIR_LIST_JAVA
searching EMP
SMITH Value in   rowid: AAAHpSAAFAAAB11AAA in EMP
searching EMP_NEW
SMITH Value in   rowid: AAAG+fAAEAAAAHfAAA in EMP_NEW
searching EMP_NEW_TEST
SMITH Value in   rowid: AAAG+hAAEAAAAHrAAA in EMP_NEW_TEST
searching EMP_ORIGNAL
SMITH Value in   rowid: AAAG+gAAEAAAAHjAAA in EMP_ORIGNAL
searching EMP_PAYLOAD