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;
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