Search Oracle Database for data using PL/SQL

I know that a piece of data is present somewhere in the database and most often I don't know where is it exactly located in the jungle of tables. The following are such situations.

i) A string is appearing in an Oracle Forms screen, I want to know which table it is stored. And if it is a non-database item Oracle diagnostics does not provide much help.

ii)I have an interface concurrent program which takes a flat file data and loads in certain tables. I want to know which table a particular information is stored.

I used to read the code or simply guess the table name and search in ALL_TABLES and then query in the possible tables. Being too lazy to do that, I wrote the following PL/SQL procedure which will do the task for me. The inputs to the program are

i) Search_string :- The data you want to search for
ii) Data_type :- The type of the data. NUMBER,VARCHAR2 and DATE are the possible options

The output of the program will be the names of the tables and columns where the given data is present. Remember to turn on the output. (SET SERVEROUTPUT ON)

DECLARE

  CURSOR c_column IS

    SELECT table_name

          ,column_name

          ,data_type

    FROM   all_tab_columns atc

    WHERE  1 = 1

    AND    data_type = UPPER('&Data_type') --the possible options are NUMBER, VARCHAR2 and DATE. You won't get any result if some other value is entered

          --   and owner in ('')   -- Add the names of the possible schemas where you expect to find the data. If you comment this line, it may take a lot of time for the search

          -- and table_name like upper('%') --Use this condition if you have any clues on the table name. (Eg :- Table name contains word 'INVOICE'). This will improve the efficiency

          -- and column_name like upper('%')--Use this condition if you have any clues on the column name. (Eg :- Column name contains word 'DATE'). This will improve the efficiency

    AND    EXISTS (SELECT 1 FROM all_tables at WHERE at.table_name = atc.table_name

    and atc.TABLE_NAME like 'FND%'); -- This condition ensures that only tables are searched, not the views

  v_search_string VARCHAR2(100) := '&Search_string';

  v_count         NUMBER;

  v_sql_stmt      VARCHAR2(2000);

  e_table_col_not_found EXCEPTION;

  e_invalid_data_type   EXCEPTION;

  PRAGMA EXCEPTION_INIT(e_table_col_not_found

                       ,-942);

BEGIN

  dbms_output.put_line('Searching for ' || v_search_string);

  FOR r_column IN c_column

  LOOP

    BEGIN

      IF r_column.data_type = 'VARCHAR2'

      THEN

        v_sql_stmt := 'SELECT COUNT(ROWID) FROM ' || r_column.table_name || ' WHERE ' || r_column.column_name || ' like ''' || v_search_string || '''';

      ELSIF r_column.data_type = 'NUMBER'

      THEN

        v_sql_stmt := 'SELECT COUNT(ROWID) FROM ' || r_column.table_name || ' WHERE ' || r_column.column_name || '= to_number(''' || v_search_string || ''')';

      ELSIF r_column.data_type = 'DATE'

      THEN

        v_sql_stmt := 'SELECT COUNT(ROWID) FROM ' || r_column.table_name || ' WHERE ' || r_column.column_name || '= to_date(''' || v_search_string ||

                      ''',''DD-MON-YYYY'')';

      END IF;

      EXECUTE IMMEDIATE v_sql_stmt

        INTO v_count;

      IF (v_count > 0)

      THEN

        dbms_output.put_line('Found in Table ' || r_column.table_name || ' Column :-  ' || r_column.column_name);

      END IF;

    EXCEPTION

      WHEN e_table_col_not_found THEN

        dbms_output.put_line('Error. Skipping Column ' || r_column.column_name || ' of table ' || r_column.table_name);

    END;

  END LOOP;

  dbms_output.put_line('Search Over');

END;

The program contains a cursor which will loop through all the tables and columns present in the database. For each of the table and column, the program will form a dynamic SQL query to see whether the data is present in the particular column.

The program is awfully slow if it searches the entire Oracle Applications database, so it will be better to narrow the search by providing extra conditions in the main cursor. The possible conditions are

i) OWNER IN ('ABC', 'XYZ') :- If you have any clues on the module where you expect the data to be present use the schema(s) corresponding to the module. If you don't have any idea, at least you can avoid certain schema using something like OWNER NOT IN ('SYS')

ii) TABLE_NAME LIKE UPPER('%ABC%') :- If you can guess the probable strings in table name use this condition

iii)COLUMN_NAME LIKE UPPER('%ABC%') :- If you can guess the probable strings in column name use this condition

The procedure has been very much helpful for me when used appropriately.