1

I know this is a duplicate question, but I couldn't find a way to reopen the discussion. Im trying to create a stored proc that will search all columns in all tables for a value. This is what I created so far:

CLEAR SCREEN
SET VERIFY OFF
ACCEPT val CHAR PROMPT 'What value do you want to search for: '
CLEAR SCREEN;
DECLARE
 match_count integer;
 v_search_string varchar2(4000) := <<val>>;
 BEGIN  
 FOR t IN (SELECT owner,
 table_name, 
 column_name 
 FROM all_tab_columns
 WHERE data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2', 
 'CLOB', 'NCLOB') ) 
 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;
/

but I get errors. Any help would be highly appreciated!!

Mark Stewart
  • 2,046
  • 4
  • 22
  • 32
TrickyDBA
  • 37
  • 1
  • 10

1 Answers1

1

See the comments:

CLEAR SCREEN
SET VERIFY OFF
ACCEPT val CHAR PROMPT 'What value do you want to search for: '
CLEAR SCREEN;

DECLARE
    match_count                             INTEGER;
    v_search_string                         VARCHAR2(4000) := '&val'; /* this was <<val>> */
BEGIN
    FOR t IN (SELECT owner,
                     table_name,
                     column_name
                FROM all_tab_columns
               WHERE data_type IN ('CHAR',
                                   'VARCHAR2',
                                   'NCHAR',
                                   'NVARCHAR2',
                                   'CLOB',
                                   'NCLOB')) 
    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;
/
Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • The last two lines where there, they just weren't formatted properly. – Alex Poole Jun 23 '16 at 16:24
  • Great! I'm getting no errors but it taking a LOOOOOOOOONG time to give any results. Anyway to speed this up or maybe specify via requestor a db name? – TrickyDBA Jun 23 '16 at 17:02
  • @AlexPoole: you'reright, thanks. For TrickyDBA: If you use DBA or ALL views, you could probably add a filter by schema; however, this does a full scan of all the tables in the schemas you want, so you can not expect a good performance. Depending on your environment, you could edit the code to add a parallel hint – Aleksej Jun 24 '16 at 07:10
  • @TrickyDBA - searching every value in every column is always going to be slow. If you expect to see the value a lot - unlikely - you can add a rownum filter. You can easily restrict to tables owned by a particular user/schema (which might be what you mean by 'database'?), or if you only want your own tables, drive from`user_tab_columns` instead. – Alex Poole Jun 24 '16 at 07:10
  • I replaced "all_tab_columns" with my schema name but I get an error. I replaced it with FROM 'column_name'. I know its something simple – TrickyDBA Jun 27 '16 at 14:51
  • If you want to limit to your schema, use USER_TAB_COLUMNS instead of ALL_TAB_COLUMNS – Aleksej Jun 27 '16 at 14:55
  • I put in "user_tab_columns" but when I run this query, it shows no results instantly and loops – TrickyDBA Jun 27 '16 at 15:43
  • Error report - ORA-06550: line 5, column 20: PL/SQL: ORA-00904: "OWNER": invalid identifier ORA-06550: line 5, column 13: PL/SQL: SQL Statement ignored ORA-06550: line 14, column 34: PLS-00364: loop index variable 'T' use is invalid ORA-06550: line 13, column 7: PL/SQL: Statement ignored ORA-06550: line 19, column 31: PLS-00364: loop index variable 'T' use is invalid ORA-06550: line 19, column 9: PL/SQL: Statement ignored ORA-06550: line 24, column 31: PLS-00364: loop index variable 'T' use is invalid ORA-06550: line 23, column 9: PL/SQL: Statement ignored – TrickyDBA Jun 27 '16 at 15:55
  • You should read some [documentation](http://docs.oracle.com/database/121/REFRN/GUID-7DAA74E5-E165-49C8-9D4B-5701C876C28B.htm#REFRN26277) to better understand the different views; `user_tab_columns` only contains informations about the current user, so the column `owner` does not make sense (the owner always is the current user) – Aleksej Jun 28 '16 at 07:07