Hi All,
I am trying to perform an activity in order to fetch email address based on the search pattern '@xyz.de'.
Unfortunately i have to scan through the complete 83 schemas in order to fetch the above value where ever it exists in which ever column and table it is residing , ideally this activity is to list out inactive email addresses (inactive eamil address i need to check seperately in the other system not by querying)
I have initially tried searching
Query 1select * from dba_tab_cols
where column_name like '%EMAIL%'
order by column_name;
I have come to know that the above pattern is not only specifically residing in a defined column either begins with EMAIL or ends with EMAIL in the column name there are various column which are not even known ,since the Tables and columns have been designed 15 years or more in the past.
so i am trying to search for a the solution with some dynamic sql which must scan through all the columns in order to identify the above mentioned pattern (@xyz.de) whether present in any of the DWH tables
below is my dynamic sql but seems this is not correct i need to search the value of the column not the column name
Query 2SET SERVEROUTPUT ON 100000
DECLARE
lv_count number(10):=0;
l_str varchar2 (1000);
lv_col_name varchar2(255) :='EMAIL';
BEGIN
FOR V1 IN
(select distinct table_name
from dba_tab_columns
where column_name = lv_col_name
order by table_name)
LOOP
dbms_output.put_line(lv_col_name||' '||v1.table_name);
END LOOP;
END;
Something like this should do...You'll get a bitmap of the columns it was found in
SQL> set serverout on
SQL> declare
2 l_sql clob;
3 l_where clob;
4 l_result int;
5 begin
6 for i in (
7 select table_name,
8 column_name,
9 row_number() over ( partition by table_name order by column_name ) as seq,
10 count(*) over ( partition by table_name ) as cnt
11 from user_tab_columns
12 where data_type in ('CHAR','CLOB','NCHAR','NVARCHAR2','VARCHAR2')
13 ) loop
14 if i.seq = 1 then
15 l_sql := 'select '||chr(10);
16 l_where := 'where '||chr(10);
17 end if;
18
19 l_sql := l_sql || ' max(case when '||i.column_name||' like ''%SCO%'' then '||power(2,i.seq-1)||' else 0 end)+'||chr(10);
20 l_where := l_where || ' '||i.column_name||' is not null or'||chr(10);
21
22 if i.seq = i.cnt then
23 l_sql := rtrim(l_sql,'+'||chr(10))||chr(10)||'from '||i.table_name||chr(10)||substr(l_where,1,length(l_where)-4);
24 dbms_output.put_line('---------------------------------------');
25 dbms_output.put_line(l_sql);
26
27 execute immediate l_sql into l_result;
28 if l_result > 0 then
29 dbms_output.put_line('Found!!! l_result='||l_result);
30 end if;
31 end if;
32 end loop;
33 end;
34 /
---------------------------------------
select
max(case when ENAME like '%SCO%' then 1 else 0 end)+
max(case when JOB like '%SCO%' then 2 else 0 end)
from BONUS
where
ENAME is not null or
JOB is not null
---------------------------------------
select
max(case when DNAME like '%SCO%' then 1 else 0 end)+
max(case when LOC like '%SCO%' then 2 else 0 end)
from DEPT
where
DNAME is not null or
LOC is not null
---------------------------------------
select
max(case when ENAME like '%SCO%' then 1 else 0 end)+
max(case when JOB like '%SCO%' then 2 else 0 end)
from EMP
where
ENAME is not null or
JOB is not null
Found!!! l_result=1
PL/SQL procedure successfully completed.
SQL> set serverout on
SQL> declare
2 l_sql clob;
3 l_where clob;
4 l_result int;
5 begin
6 for i in (
7 select table_name,
8 column_name,
9 row_number() over ( partition by table_name order by column_name ) as seq,
10 count(*) over ( partition by table_name ) as cnt
11 from user_tab_columns
12 where data_type in ('CHAR','CLOB','NCHAR','NVARCHAR2','VARCHAR2')
13 ) loop
14 if i.seq = 1 then
15 l_sql := 'select '||chr(10);
16 l_where := 'where '||chr(10);
17 end if;
18
19 l_sql := l_sql || ' max(case when '||i.column_name||' like ''%CLER%'' then '||power(2,i.seq-1)||' else 0 end)+'||chr(10);
20 l_where := l_where || ' '||i.column_name||' is not null or'||chr(10);
21
22 if i.seq = i.cnt then
23 l_sql := rtrim(l_sql,'+'||chr(10))||chr(10)||'from '||i.table_name||chr(10)||substr(l_where,1,length(l_where)-4);
24 dbms_output.put_line('---------------------------------------');
25 dbms_output.put_line(l_sql);
26
27 execute immediate l_sql into l_result;
28 if l_result > 0 then
29 dbms_output.put_line('Found!!! l_result='||l_result);
30 end if;
31 end if;
32 end loop;
33 end;
34 /
---------------------------------------
select
max(case when ENAME like '%CLER%' then 1 else 0 end)+
max(case when JOB like '%CLER%' then 2 else 0 end)
from BONUS
where
ENAME is not null or
JOB is not null
---------------------------------------
select
max(case when DNAME like '%CLER%' then 1 else 0 end)+
max(case when LOC like '%CLER%' then 2 else 0 end)
from DEPT
where
DNAME is not null or
LOC is not null
---------------------------------------
select
max(case when ENAME like '%CLER%' then 1 else 0 end)+
max(case when JOB like '%CLER%' then 2 else 0 end)
from EMP
where
ENAME is not null or
JOB is not null
Found!!! l_result=2
PL/SQL procedure successfully completed.
So
- found in column1 returns 1
- found in column2 returns 2
- found in column3 returns 4
- found in column4 returns 8
and so forth. So if you got a result of (say) 10, it meant we found the string in columns 2 and 4