plsql - Oracle query logic to identify email addresses across the dwh schemas

Question

I was performing an activity to identify eMail addresses based on certain pattern (@xyz.de). I initially tried checking the DBA_TAB_COLS [data dictionary] view but this just finds email column names and I manually need to check the big list of tables. Instead of doing that, is there is a more effective way to just fetch the the pattern value @xyz.de ?

Database - oracle 11g

Query used

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;

Please note that

1. I don't exactly know the table or column names.
2. The value @xyz.de can be in any schema and any table and any column. This has to be identified but in an effective way.

Any suggestions?

i have used the above block query to fetch the email column along with the table name , but how can i achieve by searching certain value @xyz.de using the dynamic sql ?

question from:https://stackoverflow.com/questions/66060719/oracle-query-logic-to-identify-email-addresses-across-the-dwh-schemas

Answer 1

I don't know what you want to do with the values that you are trying to extract, so the below code just prints them. Refer to PL/SQL Dynamic SQL from the Oracle documentation.

declare
  type EMAILS is ref cursor;
  L_CURSOR  EMAILS;
  cursor EMAIL_COLS is
select OWNER
      ,TABLE_NAME
      ,COLUMN_NAME
  from DBA_TAB_COLS
 where COLUMN_NAME like '%EMAIL%'
   and OWNER <> 'SYS';
  L_SQL  varchar2(200);
  L_EMAIL  varchar2(500);
begin
  for REC in EMAIL_COLS
  loop
    L_SQL := 'select ' || REC.COLUMN_NAME || ' from ' || REC.OWNER || '.' || REC.TABLE_NAME || ' where ' || REC.COLUMN_NAME || ' like ''%xyz.de''';
    open L_CURSOR for L_SQL;
    loop
      fetch L_CURSOR into L_EMAIL;
      exit when L_CURSOR%notfound;
      DBMS_OUTPUT.PUT_LINE(L_EMAIL);
    end loop;
  end loop;
end;