Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (abc) AS
SELECT 237001 FROM DUAL;
1 rows affected

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_COL_MATCHES
(
    SCHEMA_NAME VARCHAR2(100),
    TABLE_NAME VARCHAR2(100),
    COLUMN_NAME VARCHAR2(100),
    DATA_TYPE VARCHAR2(100),
    MATCH_COUNT INT
)
ON COMMIT PRESERVE DEFINITION;
DECLARE
  cSQL         VARCHAR2(1000);
  iSearchValue INT;
BEGIN
  iSearchValue := 237001;

  INSERT INTO ORA$PTT_COL_MATCHES(
    SCHEMA_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    MATCH_COUNT
  )
  SELECT  col.owner as schema_name,
          col.table_name, 
          col.column_name,
          col.data_type,
          0
  FROM    all_tab_columns col
  WHERE   col.owner IN (USER) -- NOT IN ('SYS', 'SYSTEM')
  AND     col.DATA_TYPE IN ('INT', 'NUMBER', 'FLOAT');

  FOR rec IN (
    SELECT  *
    FROM    ORA$PTT_COL_MATCHES
  )
  LOOP
      cSQL := 'UPDATE ORA$PTT_COL_MATCHES
               SET    MATCH_COUNT = (SELECT COUNT(*)
                                     FROM   "' || rec.SCHEMA_NAME || '"."' || rec.TABLE_NAME || '"
                                     WHERE  "' || rec.COLUMN_NAME || '" = :search_value
                                    )
               WHERE  SCHEMA_NAME = :schema_name
               AND    TABLE_NAME  = :table_name
               AND    COLUMN_NAME = :column_name';
      EXECUTE IMMEDIATE cSQL
        USING TO_CHAR(iSearchValue), rec.SCHEMA_NAME, rec.TABLE_NAME, rec.COLUMN_NAME;
  END LOOP;
END;
/
1 rows affected

SELECT  *
FROM    ORA$PTT_COL_MATCHES
WHERE   match_count > 0;


SCHEMA_NAME
TABLE_NAME
COLUMN_NAME
DATA_TYPE
MATCH_COUNT


FIDDLE_RHDWVTWTSWKDIFFFWWER
TABLE_NAME
ABC
NUMBER
1