Searching a particular value in all tables.

Sometimes, We need to search a particular data from all tables (We don't know the table name). There are some good examples in OTN provided by MichaelS.

I am consolidating those here.

SQL> /* My database version */
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> SET LINE 1000
SQL> VAR val VARCHAR2(30)
SQL> EXECUTE :val:='SMITH';

PL/SQL procedure successfully completed.

SQL> /* Searching the value */
SQL> SELECT DISTINCT SUBSTR(:val, 1, 11) "Searchword",
  2                   SUBSTR(table_name, 1, 14) "Table",
  3                   SUBSTR(t.COLUMN_VALUE.getstringval(), 1, 50) "Column/Value"
  4     FROM cols,
  5          table(XMLSEQUENCE(DBMS_XMLGEN.getxmltype('select ' || column_name ||
  6                                                   ' from ' || table_name ||
  7                                                   ' where (UPPER(''' || :val ||
  8                                                   ''')=UPPER(' ||
  9                                                   column_name || '))')
 10                            .EXTRACT('ROWSET/ROW/*'))) t
 11    WHERE table_name IN ('EMP', 'DEPT', 'EMPLOYEES') --limiting the table names, you can omit thi
s.
 12    ORDER BY "Table";

no rows selected

SQL> EXECUTE :val:='Sourav';

PL/SQL procedure successfully completed.

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

Searchword  Table          Column/Value
----------- -------------- --------------------------------------------------
Sourav      EMP            <ENAME>Sourav</ENAME>

SQL> EXECUTE :val:='NEW YORK'

PL/SQL procedure successfully completed.

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

Searchword  Table          Column/Value
----------- -------------- --------------------------------------------------
NEW YORK    DEPT           <LOC>NEW YORK</LOC>

SQL>