ТЕМА:

SELECT *
FROM (SELECT table_name
           , column_name
           , DBMS_XMLGEN.getXmltype(
               'select count(*) cnt from '||table_name||' where '||column_name||' like ''%A%'''
             ).EXTRACT('ROWSET/ROW/CNT/text()').getNumberVal() cnt
      FROM COLS
      WHERE 1=1
        AND COLS.data_type LIKE '%CHAR%'
     )
WHERE cnt > 0
ORDER BY table_name, column_name;

SELECT table_name
     , column_name
     , DBMS_XMLGEN.getxmltype(
         'select count(*) cnt from '||table_name||' where '||column_name||' like ''%a%'''
       ).EXTRACT('rowset/row/cnt/text()').getNumberVal() cnt
FROM COLS
WHERE cols.data_type LIKE '%CHAR%'
ORDER BY cnt DESC;

ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1

ORA-19202: Error occurred in XML processing
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1

CREATE OR REPLACE FUNCTION VALUE_PRESENT
        (table_name IN VARCHAR2
        ,column_name IN VARCHAR2
        ,TEXT IN VARCHAR2
        )
RETURN NUMBER
AS
        nVAL NUMBER(17);
BEGIN
        BEGIN
        EXECUTE IMMEDIATE
                'select count(*)'||
                ' from '||table_name||
                ' where '||column_name||' like ''%'||TEXT||'%'''
                INTO nval;
        EXCEPTION WHEN OTHERS THEN
                RETURN 0;
        END;
        RETURN nVAL;
END;
/
 
SELECT table_name
     , column_name
FROM COLS
WHERE COLS.data_type LIKE '%CHAR%'
  AND VALUE_PRESENT(table_name, column_name, 'SearchText') > 0;