Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
951 views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...