Search all varchar columns in table and output the row

Question

I need an assistance with regard to building a PL/SQL block related to the following query:

SELECT <PRIMARY_KEY_COLUMN>, <VARCHAR_COLUMN> FROM TABLENAME WHERE REGEXP_LIKE(VARCHAR_COLUMN, UNISTR('[\D800-\DFFF]'));

The above query will give an output related to all the UTF8 bytes that are mentioned in the range.

I would request you guys to help me with modifying the above query, so that I can run it on all the VARCHAR/CLOB columns in the table and get an output like this:

ColumnName                   Value                 Primary_key_Column
-----------------------------------------------------------------------
Col1                         v1                     123
Col1                         v2                     124
.
.
Col2                         v1                     167
Col2                         v2                     123
.
.

Kindly review and please share your comments.

UPDATE1:

I was able to build the following block from the comments I received and from one of the posts, but it still requires edits:

set serveroutput on;
DECLARE
  match_count integer;
  v_search_string varchar2(4000) := 'shazamTemplateId';
BEGIN  
  FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns WHERE data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2', 'CLOB', 'NCLOB') AND table_name = 'DECORATION_FIELDS') 
  LOOP   
    BEGIN
      EXECUTE IMMEDIATE    
          'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name || ' WHERE REGEXP_LIKE( '||t.column_name||' = :1)'
          INTO match_count
          USING UNISTR('[\D800-\DFFF]');
      IF match_count > 0 THEN 
        dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
      END IF; 
    EXCEPTION
      WHEN others THEN
        dbms_output.put_line( 'Error encountered trying to read ' || t.column_name || ' from ' || t.owner || '.' || t.table_name );
    END;
  END LOOP;
END;

Answer 1

Here is a static solution (it does not require any PL/SQL code, but it require prior knowledge of the table and column names, and knowing which columns must be included). It also assumes all the "text" columns are VARCHAR2; as I explained in a Comment, you shouldn't expect to be able to return VARCHAR2 and CLOB values in the same column in the output. (Perhaps, if you must do everything in one go, you need several columns in the output: column_name but also column_type, as in VARCHAR2 vs CLOB, and then two value columns, one for VARCHAR2 columns in the original table and the other one for CLOB values.)

You can use something similar with PL/SQL code to make it dynamic; I don't recommend it.

So, anyway, here is the static solution. It uses the EMP table in the SCOTT schema. The PK is EMPNO (NUMBER data type), there are two VARCHAR2 columns, ENAME and JOB. EMP looks like this:

select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE              SAL  COMM     DEPTNO
----- ---------- --------- ----- ------------------- ----- ----- ----------
 7369 SMITH      CLERK      7902 1980-12-17 00:00:00   800               20
 7499 ALLEN      SALESMAN   7698 1981-02-20 00:00:00  1600   300         30
 7521 WARD       SALESMAN   7698 1981-02-22 00:00:00  1250   500         30
 7566 JONES      MANAGER    7839 1981-04-02 00:00:00  2975               20
 7654 MARTIN     SALESMAN   7698 1981-09-28 00:00:00  1250  1400         30
 7698 BLAKE      MANAGER    7839 1981-05-01 00:00:00  2850               30
 7782 CLARK      MANAGER    7839 1981-06-09 00:00:00  2450               10
 7788 SCOTT      ANALYST    7566 1987-04-19 00:00:00  3000               20
 7839 KING       PRESIDENT       1981-11-17 00:00:00  5000               10
 7844 TURNER     SALESMAN   7698 1981-09-08 00:00:00  1500     0         30
 7876 ADAMS      CLERK      7788 1987-05-23 00:00:00  1100               20
 7900 JAMES      CLERK      7698 1981-12-03 00:00:00   950               30
 7902 FORD       ANALYST    7566 1981-12-03 00:00:00  3000               20
 7934 MILLER     CLERK      7782 1982-01-23 00:00:00  1300               10

The query to solve your problem: (the query searches for values that contain the characters from V to Z; adapt to your needs)

select   col_name, val, empno
from     emp
unpivot  ( val for col_name in (ename as 'ENAME', job as 'JOB') )
where    regexp_like( val, '[V-Z]' )
order by col_name, empno -- If needed
;

COL_NAME VAL        EMPNO
-------- ---------- -----
ENAME    WARD        7521
JOB      ANALYST     7788
JOB      ANALYST     7902