Find eBS table based on value in column

Sometimes I know the value that is used somewhere in a screen, but I have no idea what tables are behind it. So I wrote a dynamic sql script to run through a number of tables to find the table and column that holds the value.

Run it using the value you are searching for and a restriction on the tables.
For example

@FINDVALUE 3004 AR

To find all AR tables that have any column with a value 3004. Currently I only look for CHARs and NUMBERs, but of course you can expand if desired.


SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF

DECLARE
  p_table_name   VARCHAR2(240) := '&&2';
  p_value        VARCHAR2(240) := '&&1';
  v_statement    VARCHAR2(8000);
  v_base_statement VARCHAR2(8000);
  v_found        VARCHAR2(240);
  TheResult      NUMBER;
 
  CURSOR C_Tables
  IS
  SELECT Table_Name
  FROM   ALL_TABLES
  WHERE  Table_Name like p_table_name || '%';
 
  CURSOR C_Table_Cols
  (
    cp_table_name VARCHAR2
  )
  IS
  SELECT Column_Name, Table_Name, Data_Type
  FROM   ALL_TAB_COLUMNS
  WHERE Table_Name = cp_table_name
  AND   Data_Type IN ('NUMBER', 'VARCHAR2')
  ;
 
 
BEGIN

  DBMS_OUTPUT.Put_Line ('List of tables with value '|| p_value);
  DBMS_OUTPUT.Put_Line ('---------------------------------------------------------------------');

  <<Table_Def>>
  FOR T IN C_Tables
  LOOP

      v_base_statement := 'SELECT 1 FROM ' || T.Table_Name || ' WHERE ';
     
      <<Table_Cols>>
      FOR C IN C_Table_Cols (cp_table_name => T.Table_Name)
      LOOP
        IF C.Data_Type = 'NUMBER'
        THEN
          v_statement := v_base_statement || C.Column_Name || ' = ' || p_value;
        ELSIF C.Data_Type = 'VARCHAR2'
        THEN
          v_statement := v_base_statement || C.Column_Name || ' = ''' || p_value || '''';
        END IF;
        TheResult := 0;
        BEGIN
          --dbms_output.put_line ('Testing ... ' || v_statement);
          execute immediate (v_statement) INTO TheResult;
          --dbms_output.put_line ('... '||  v_statement || ' returns TRUE');
          --dbms_output.put_line ('Result: ' || TheResult);
          EXCEPTION
            WHEN Others THEN
              --dbms_output.put_line ('Error: ' || SQLERRM);
              TheResult := 0;
        END;
       
        IF TheResult = 1
        THEN
          v_found := T.Table_Name || '.' || C.Column_Name || ' (' || C.Data_Type || ')';
          DBMS_OUTPUT.Put_Line (v_found);
        END IF;
      END LOOP Table_Cols;
     
  END LOOP Table_Def;
 
END;
/

Another sample for the same issue is as follows from Saubhik

SELECT   DISTINCT
                 SUBSTR(:val, 1, 11) "Searchword",
                  SUBSTR(table_name, 1, 14) "Table",
                 SUBSTR(t.COLUMN_VALUE.getstringval(), 1, 50) "Column/Value"
      FROM     cols,
                   table(XMLSEQUENCE(DBMS_XMLGEN.getxmltype(   'select '
                                                           || column_name
                                                           || ' from '
                                                           || table_name
                                                        || ' where (UPPER('''
                                                         || :val
                                                         || ''')=UPPER('
                                                        || '))').EXTRACT('ROWSET/ROW/*'))) t
    WHERE    table_name IN ('EMP', 'DEPT','EMPLOYEES') --limiting the table names, you can omit this.
     ORDER BY "Table"

  /