oracle - SQL select for all records that may holds specific value

Question

How to select all records,that may contain specific value that is known, without referring to specific column in SQL expression?

For instance, i know,that some unknown column holds value 'xxx' and there are many columns and records in table.

Thank you.

Answer 1

---

So, you want to do a Google-like free text search over your database. This can be done but the performance will be Teh Suck! Google is fast because it has indexes on its indexes, duplicate data stores and generally optimizes everything for precisely this kind of search.

Anyway, here is a proof of concept using dynamic SQL and the Oracle data dictionary. Note that I restrict the columns to the type of data I want to search for i.e. strings.

SQL> set serveroutput on size unlimited
SQL> declare
  2      dummy varchar2(1);
  3  begin
  4      for r in ( select table_name, column_name from user_tab_cols
  5                 where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )
  6      loop
  7          begin
  8              execute immediate 'select null from '||r.table_name
  9                      ||' where '||r.column_name||' like ''%&search_value%'' '
 10                      ||' and rownum = 1'
 11                 into dummy;
 12              dbms_output.put_line('Found it in >>>'
 13                     ||r.table_name||'.'||r.column_name);
 14          exception
 15              when others then
 16                  -- bad practice ahoy!
 17                  null;
 18          end;
 19      end loop;
 20  end;
 21  /
Enter value for search_value: MAISIE
old   9:                ||' where '||r.column_name||' like ''%&search_value%'' '
new   9:                ||' where '||r.column_name||' like ''%MAISIE%'' '
Found it in >>>T23.NAME

PL/SQL procedure successfully completed.

SQL>

A more robust implementation might need to handle case, whole words, etc. If you're on 10g or higher then regular expressions could be useful, but combining regex and dynamic SQL is an, er, interesting prospect.

I repeat that performance is going to be Teh Suck! on a large data set. It is virtually impossible to tune, because we cannot index every column, and certainly not to support LIKE or similar fuzzy matches. An alternative approach would be to use XQuery to generate an XML representation of your data and then use Text to index it. Maintaining such a repository would be overhead, but the effort would be a sound investment if you need this functionality of a regular basis, especially in a production environment.

---

We can conduct a broader search across all the tables we have privileges on by using all_tab_cols instead.

for r in ( select owner, table_name, column_name from all_tab_cols
                   where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )

Obviously we need to prefix the owning schema in the generated statement.

execute immediate 'select null from '||r.owner||'.'||r.table_name
                       ||' where '||r.column_name||' like ''%