how to fetch email address from oracle database

Hi All i am trying to fetch certain email patterns from oracle db 11g i have used the below queries

Unfortunately i have to scan through the complete schemas in order to fetch the value (@pqr.de) 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)

query

--desc dba_tab_cols
SET SERVEROUTPUT ON 100000
DECLARE 
    l_sql    CLOB; 
    l_where  CLOB; 
    l_result INT; 
BEGIN 
    FOR i IN (SELECT table_name, 
                     column_name, 
                     Row_number() 
                       over ( 
                         PARTITION BY table_name 
                         ORDER BY column_name )    AS seq, 
                     Count(*) 
                       over ( 
                         PARTITION BY table_name ) AS cnt 
              FROM   dba_tab_columns 
              WHERE  data_type IN ( 'CHAR', 'CLOB', 'NCHAR', 'NVARCHAR2', 
                                    'VARCHAR2' 
                                  )) LOOP 
        IF i.seq = 1 THEN 
          l_sql := 'select ' 
                   ||Chr(10); 

          l_where := 'where ' 
                     ||Chr(10); 
        END IF; 

        l_sql := l_sql 
                 || '  max(case when ' 
                 ||i.column_name 
                 ||' like ''[e-mail]%'' then ' 
                 ||Power(2, i.seq - 1) 
                 ||' else 0 end)+' 
                 ||Chr(10); 

        l_where := l_where 
                   || '  ' 
                   ||i.column_name 
                   ||' is not null or' 
                   ||Chr(10); 

        IF i.seq = i.cnt THEN 
          l_sql := Rtrim(l_sql, '+' 
                                ||Chr(10)) 
                   ||Chr(10) 
                   ||'from ' 
                   ||i.table_name 
                   ||Chr(10) 
                   ||Substr(l_where, 1, Length(l_where) - 4); 

          dbms_output.Put_line('---------------------------------------'); 

          dbms_output.Put_line(l_sql); 

          EXECUTE IMMEDIATE l_sql INTO l_result; 

          IF l_result > 0 THEN 
            dbms_output.Put_line('Found!!! l_result=' 
                                 ||l_result); 
          END IF; 
        END IF; 
    END LOOP; 
END; 

/ 

i am getting error

Error report –

ORA-00936: missing expression
ORA-06512: at line 54
00936. 00000 -  "missing expression"
*Cause:    
*Action:

how to resolve the error , since i am trying to retreive the list of tables from the above query

TESTED QUERY – Failed at line 60

SET SERVEROUTPUT ON 100000
DECLARE 
    l_sql    CLOB; 
    l_where  CLOB; 
    l_result INT; 
BEGIN 
    FOR i IN (SELECT owner,
                     table_name, 
                     column_name, 
                     Row_number() 
                       over ( 
                         PARTITION BY table_name 
                         ORDER BY column_name )    AS seq, 
                     Count(*) 
                       over ( 
                         PARTITION BY table_name ) AS cnt 
              FROM   all_tab_columns 
              --WHERE  owner not in ('LIST_OF_SCHEMAS') -- list relevant schemas
              AND    data_type IN ( 'CHAR', 'CLOB', 'NCHAR', 'NVARCHAR2', 
                                    'VARCHAR2' 
                                  )) LOOP 
        IF i.seq = 1 THEN 
          l_sql := 'select ' 
                   ||Chr(10); 

          l_where := 'where ' 
                     ||Chr(10); 
        END IF; 

        l_sql := l_sql 
                 || '  max(case when "' 
                 ||i.column_name 
                 ||'" like ''[e-mail]%'' then ' 
                 ||Power(2, i.seq - 1) 
                 ||' else 0 end)+' 
                 ||Chr(10); 

        l_where := l_where 
                   || ' "' 
                   ||i.column_name 
                   ||'" is not null or' 
                   ||Chr(10); 

        IF i.seq = i.cnt THEN 
          l_sql := Rtrim(l_sql, '+' 
                                ||Chr(10)) 
                   ||Chr(10) 
                   ||'from "'
                   ||i.owner
                   ||'"."'
                   ||i.table_name
                   ||'"'
                   ||Chr(10) 
                   ||Substr(l_where, 1, Length(l_where) - 4); 

          dbms_output.Put_line('---------------------------------------'); 

          ---dbms_output.Put_line(l_sql); 
          dbms_output.Put_line(dbms_lob.substr(l_sql, 4000, 1)); 

          EXECUTE IMMEDIATE l_sql INTO l_result; 

          IF l_result > 0 THEN 
            dbms_output.Put_line('Found!!! l_result=' 
                                 ||l_result); 
          END IF; 
        END IF; 
    END LOOP; 
END; 

Error report –

Error report -
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 61
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

Answer

Your approach basically works (though there are simpler methods, if you really have to do this; there are some alternative approaches here for example), so the error you are getting from the execution of a a particular l_sql value suggests a problem with that specific construction. A likely culprit is a table or column name which is a reserved word, which is confusing the parser. You may also have problems with quoted identifiers, and you are looking for tables across all schemas (including internal ones like SYS) but are not specifying the owner of each table.

Those issues can be avoided by adding double-quotes and the owner to your cursor and construction statements:

...
-- add owner to cursor
    FOR i IN (SELECT owner,
                     table_name, 
                     column_name, 
                     Row_number() 
                       over ( 
                         PARTITION BY table_name 
                         ORDER BY column_name )    AS seq, 
                     Count(*) 
                       over ( 
                         PARTITION BY table_name ) AS cnt 
-- possibly query all_ instead of dba_
              FROM   all_tab_columns 
-- limit to schema you're interested in
              WHERE  owner in (USER) -- list relevant schemas
              AND    data_type IN ( 'CHAR', 'CLOB', 'NCHAR', 'NVARCHAR2', 
                                    'VARCHAR2' 
                                  )) LOOP 
...
        l_sql := l_sql 
-- add double-quotes around column name
                 || '  max(case when "' 
                 ||i.column_name 
                 ||'" like ''[e-mail]%'' then ' 
                 ||Power(2, i.seq - 1) 
                 ||' else 0 end)+' 
                 ||Chr(10); 

        l_where := l_where 
-- add double-quotes around column name
                   || ' "' 
                   ||i.column_name 
                   ||'" is not null or' 
                   ||Chr(10); 

        IF i.seq = i.cnt THEN 
          l_sql := Rtrim(l_sql, '+' 
                                ||Chr(10)) 
                   ||Chr(10) 
-- add double-quotes around table name, and prefix with owner, also quoted (just in case!)
                   ||'from "'
                   ||i.owner
                   ||'"."'
                   ||i.table_name
                   ||'"'
                   ||Chr(10) 
                   ||Substr(l_where, 1, Length(l_where) - 4); 
...

db<>fiddle

i have tested got the below error

Error report –

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 19270, maximum: 4000)
ORA-06512: at line 60

The query should be OK with CLOB values, so I suspect line 60 is now the output line, and l_sql is itself too big; in which case you can change that to:

          dbms_output.Put_line(dbms_lob.substr(l_sql, 4000, 1)); 

Error report –

ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 61

This is probably when i.seq reaches 128; power(2, 127) is too big for your int result variable. Which if I’m reading this right means you have a table with 128 text columns? Not sure what the point of that is though.