how to fetch email address from oracle database

Question

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 ''%@pqr.de%'' 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 ''%@pqr.de%'' 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:

By my count, based on the code in your question, line 54 is: `EXECUTE IMMEDIATE l_sql INTO l_result;` That means that `l_sql` contains invalid SQL. Immediately before that line you print the value of `l_sql` in this line: `dbms_output.Put_line(l_sql);`. Please [edit] your question and post the value of `l_sql` that contains the SQL which is causing the error ORA-00936. — Abra, Feb 13 '21 at 15:12
Do you have tables with quoted identifiers? I suspect one of the constructed queries is seeing a column name it doesn't like. The last printed `l_sql` should show you which? Incidentally, you're looking through the whole database, not at specific schemas; you might want to add an `owner` filter to your cursor, and possibly look at `all_` rather than `dba_` views. — Alex Poole, Feb 13 '21 at 15:13
@AlexPoole basically i am trying to scan through whole dwh in order to identify tables,columns where the email rows exists (@pqr.de) , can you please edit in the query where it must be corrected ? — Data2explore, Feb 13 '21 at 15:25
I get that, but you're looking in internal tables (e.g. SYS and SYSTEM schemas) as well as the ones you're interested in, and also views as well as tables; and by querying a `dba_` view you might pick up tables you don't have permission to query. You can be more selective by modifying your cursor query. The error you are getting is something else; like I said, probably a quoted identifier, from a table or column name using a reserved word. Please edit your question to include the last `l_sql` value it printed. — Alex Poole, Feb 13 '21 at 15:35
I don't understand the premise of this whole thing. (To be blunt: it suggests either an attempt to do something wrong, or severe incompetence, or trying to hide one's mistakes, etc.) Why would you EVER search a column like "driver license number" for an email address? Searching ALL (string) columns in ALL tables for ***whatever*** is never a reasonable thing to do (unless, perhaps, you are looking for virus signatures and such). — , Feb 13 '21 at 17:31

Alex Poole · Accepted Answer · 2021-02-13T17:56:24.597

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 ''%@pqr.de%'' 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.

Thanks i will be test the query but few questions 1) what is the difference in using dba_tab_cols vs all_tab_columns 2) -- add double-quotes around column name here i am interested in searching for a values @pqr.de so the column could be any thing ,may be i misunderstood 3) i think i can also exclude few schemas like SYS — Data2explore, Feb 13 '21 at 16:00
@rakesh - 1) `dba_` views show values for the whole DB, including things you may not be able to access` `all_` views exclude things you can't access. 2) adding double quotes doesn't affect *which* tables and columns you search, it just stops any created with quoted identifiers (e.g. mixed case, or with a reserved word) causing an issue. Usually they get the same result, e.g `select * from dual` and `select * from "DUAL"`. [Read more.](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-75337742-67FD-4EC0-985F-741C93D918DA) — Alex Poole, Feb 13 '21 at 16:06
Many thanks for the explanation , let me try testing with only few schemas — Data2explore, Feb 13 '21 at 16:16
@rakesh - it would have been better to put the error message (without the cause/action part) in a comment. But not sure why you're seeing that; [it's OK with CLOBs](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=f8a6fa2edbe1dc5e46f538b711a3290d). What is line 60 in your code? Oh... is i t the `put_line`? — Alex Poole, Feb 13 '21 at 16:57
Please stop editing your question. Your original issue is resolved, right? I've updated the answer with what is probably the cause of the latest error. If you have further issues please ask a new question. — Alex Poole, Feb 13 '21 at 17:57
ok i stop editing yes there are many tables more than 128 text columns — Data2explore, Feb 13 '21 at 18:07

how to fetch email address from oracle database

1 Answers1