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.
