0

I am trying to identify all the tables and columns across Database schemas with a search pattern '@abc.de' , ideally this exercise is to

  1. identify the specific table and associated columns using email patterns (@abc.de)
  2. pickup that list and cross verify whether the email address are valid or not

unfortunately the point 1 is very difficult

upon searching and trying to find

Search All Fields In All Tables For A Specific Value (Oracle)

SQL CODE

SET SERVEROUTPUT ON
DECLARE
    l_cmd     VARCHAR2 (2000);
    l_found   INTEGER;
BEGIN
    FOR eachcol IN (  SELECT *
                        FROM all_tab_cols a
                       WHERE a.data_type = 'VARCHAR2'
                        AND owner = 'schema_name'
                    ORDER BY table_name, column_name)
    LOOP
        l_cmd   :=
               'select count(*) c from '
            || eachcol.owner
            || '.'
            || eachcol.table_name
            || ' where '
            || LOWER (eachcol.column_name)
            || q'[ LIKE '%@abc%.de%' AND ROWNUM = 1]';

        EXECUTE IMMEDIATE l_cmd INTO l_found;

        IF l_found > 0
        THEN
            DBMS_OUTPUT.put_line (
                   RPAD (eachcol.owner || '.' || eachcol.table_name || '.' || eachcol.column_name, 92)
                || ' may contain email addresses'
            );
        END IF;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (l_cmd);
        DBMS_OUTPUT.put_line (SQLERRM);
        RAISE;
END;

I am getting the below error

Error report -
ORA-00911: invalid character
ORA-06512: at line 35
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.

Note : I have tried many options to fetch the email address but not getting the right query logic , any suggestions ?

Data2explore
  • 452
  • 6
  • 16
  • In most systems I have worked on, looping through every single `varchar2` column in the schema one at a time would take more than a human lifetime. Are there really no clues in the column names? You might also exclude columns too small to be an email address - presumably `a@b.cd` is about the smallest an email address can be, so you are looking for 6 characters or more for a start. – William Robertson Feb 04 '21 at 20:27
  • @WilliamRobertson - I can definately refine by specifying the required columns ,but to ensure to identify all the emails across the DWH schemas ,but realisticayllt seems difficult and consumes lot of time and query never ends and retruns numerious errors, i will see if can find columns – Data2explore Feb 04 '21 at 21:39

1 Answers1

2

Remove useless WHEN OTHERS exception. It hides exact error point as line #35 is RAISE.

Then, display table and column name you're currently working with - use DBMS_OUTPUT.PUT_LINE for that, placed right behind LOOP.

Once you hit the error, procedure will stop and display the last table/column which raised the error. I presume that it is something like this:

SQL> create table xxx ("!what ?" varchar2(10));

Table created.

Your code ends up with

DECLARE
*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at line 35

which isn't exactly what you got, but - you'll find it out yourself. I can't, I don't have your tables.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57