Oracle tüm tablo kolonlarında text arama

 SQL> variable val varchar2(10)

  SQL> exec :val := 'Caddesi'

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "ArananKelime",
  2    SUBSTR (table_name, 1, 14) "Tablo",
  3    SUBSTR (column_name, 1, 14) "Kolon"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper(''%'
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /

ArananKelime Tablo          Kolon
-----------  -------------- --------------
CADDESI      CALISAN        ADRES