Oracle查询逻辑，用于识别dwh模式中的电子邮件地址

大家好，

我正在尝试执行一项活动，以便根据搜索模式 “@ xyz.de” 获取电子邮件地址。

不幸的是，我必须扫描完整的83模式，以便获取上面的值，无论它存在于哪个列和表，它是驻留的，理想情况下，这个活动是列出非活动的电子邮件地址 (非活动的eamil地址，我需要在其他系统中单独检查而不是通过查询)

我最初尝试搜索

Query 1

select * from dba_tab_cols
where column_name like '%EMAIL%'
order by column_name;

我已经知道，上面的模式不仅是专门居住在一个定义的列，无论是从电子邮件开始或结束与电子邮件在列名有各种列，甚至不知道，因为表和列已经设计了15年或更多在过去。

因此，我正在尝试使用一些动态sql搜索解决方案，该解决方案必须扫描所有列，以识别上述模式 (@ xyz.de) 是否存在于任何DWH表中

下面是我的动态sql，但似乎这是不正确的，我需要搜索列的值而不是列名

Query 2

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

像这样的事情应该做... 你会得到它在

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.

所以

-在列n1中找到返回1
-在列2中找到返回2
-在列3中找到返回4
-在列4中找到返回8

and so forth. 所以if you got a result of (say) 10, it meant we found the string in columns 2 and 4