Searching for a particular value within an Oracle database

Question

I am trying to search for a particular value within an Oracle database and the script is not working. Can this be done with the following code?

DECLARE
  match_count integer;
  v_search_string varchar2(4000) := 'QS/Operation';

BEGIN  
  FOR t IN (SELECT owner,
                   table_name, 
                   column_name 
              FROM all_tab_columns
             WHERE data_type in ('VARCHAR2') ) 
  LOOP   
    BEGIN
      EXECUTE IMMEDIATE    
        'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
        ' WHERE '||t.column_name||' = :1'   
         INTO match_count  
        USING v_search_string; 
      IF match_count > 0 THEN 
        dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
      END IF; 
    EXCEPTION
      WHEN others THEN
        dbms_output.put_line( 'Error encountered trying to read ' ||
                              t.column_name || ' from ' || 
                              t.owner || '.' || t.table_name );
    END;
  END LOOP;
END;

The script is generating errors. I need to know how to get past the errors and find the value in the tables.

"The script is generating errors." - it is helpful to include the errors in your question. You seem to be modifying [this code](https://stackoverflow.com/a/208892/266304); why change it? — Alex Poole, Apr 02 '20 at 17:40

score 0 · Answer 1 · answered Apr 02 '20 at 17:59

First, catching when others without re-raising is usually a bug; here it looks like you're intending to report errors but carry on, which doesn't seem unreasonable. But you should include the error in the information you display, e.g.:

EXCEPTION
  WHEN others THEN
    dbms_output.put_line( 'Error encountered trying to read ' ||
                          t.column_name || ' from ' || 
                          t.owner || '.' || t.table_name );
    dbms_output.put_line(SQLERRM);
END;

Presumably that is telling that tables don't exist, or you have invalid identifiers, or you have privilege errors. You're searching all tables owned by everyone, and not even excluding those owned by SYS as the original code did - hopefully that isn't because you create your own tables under SYS - and it's possible somewhere in there you have tables with quoted identifiers. Actually, it's very likely; SYS has _default_auditing_options_, and system-generated nested table names can be mixed case, for instance.

To be on the safe side adding quotes won't hurt anyway:

BEGIN
  EXECUTE IMMEDIATE    
    'SELECT COUNT(*) FROM "' || t.owner || '"."' || t.table_name || '"' ||
    ' WHERE "' || t.column_name || '" = :1'   
     INTO match_count  
    USING v_search_string;

Searching for a particular value within an Oracle database

1 Answers1