Oracle PL/SQL: Searching All VARCHAR2 Fields In A Schema

How frustrating is that? I've entered the data into the Oracle Front-end, the workflow has kicked off, an email has been sent, and *somewhere* in all this mess is a record of an error e-mail being received.

After 4 hours of trying to track down the problem (we actually have 3 emails being received - all "address not found") every 3 minutes ... for the past month and a half. We've got 48,000 emails in the Inbox at the moment. It takes 15 minutes to open in Outlook.

Where do you start?

Now I know the email address that is being used. I also know that the problem persists despite the server being taken down (for backup) every week therefore it *must* be stored somewhere in the database.

The following script runs through every single table in the database that contains a VARCHAR2 column and tries to find a specific string. It takes a while against an Oracle 11i schema (best to leave overnight ... maybe over a weekend if you have that many modules installed!).

declare 
  c_SEARCHTEXT constant varchar2(255) := 'SEARCH TEXT GOES IN HERE';

  cursor c_Tables is 
    select distinct atc.owner, atc.table_name
    from all_tab_columns atc
    where data_type = 'VARCHAR2'
    and DATA_LENGTH >= length(c_SEARCHTEXT)
    and not exists (select 'X' from all_views av where av.owner = atc.owner and av.view_name = atc.table_name);
  
  cursor c_Columns (p_Owner varchar2, p_TableName varchar2) is
    select distinct column_name
    from all_tab_columns
    where owner = p_Owner
    and data_type = 'VARCHAR2'
    and DATA_LENGTH >= length(c_SEARCHTEXT)
    and table_name = p_TableName;
  
  TYPE cv_typ IS REF CURSOR;
  cv cv_typ;
  record_count integer;
  v_SQL varchar2(8124);
begin
  for v_Table in c_Tables loop
    for v_Columns in c_Columns(v_table.owner, v_table.table_name) loop
      v_SQL := 
        'select count(*) ' || chr(13) ||
        'from ' || v_table.owner || '.' || v_Table.table_name || chr(13) ||
        'where upper(' || v_Columns.column_name || ') like upper(''%' || c_SEARCHTEXT || '%'')' || chr(13);
      open cv for
        v_SQL;
      fetch cv into record_count;
      if record_count > 0 then
        dbms_output.put_Line(v_Table.table_name || '.' || v_Columns.column_name || '***** FOUND *****');
      end if;
      close cv;
    end loop;
  end loop;
end;

Now this is unoptimised so it will be sloooooooow. You can always change the initial select to prioritise schemas you are interested in, or add in a "length" check to make sure data of the correct length exists, but the biggest saving will be replacing the "dbms_output" call with something that will send you e-mail messages when it finds something (rather than waiting until the end when it's done!).