Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How do you search through multiple tables?
On Wed, 19 Feb 2003, Norman.Dunbar_at_lfs.co.uk wrote:
> FOR x IN (SELECT table_name, column_name
> FROM user_tab_columns
> WHERE DATA_type IN ('VARCHAR2','CHAR')
> AND data_length >= LENGTH(TextToFind))
> LOOP
> BEGIN
> MyResult := 0;
> EXECUTE IMMEDIATE 'select 1 from dual where exists '||
> '(select 1 from '||x.table_name||
> ' where '||x.column_name||' like
> ''%'|| TextToFind||'%'')'
> INTO MyResult;
>
> -- If we got a hit, then list the table and column names.
> IF (MyResult = 1) THEN
> DBMS_OUTPUT.PUT_LINE(x.table_name || '.' ||
> x.column_name);
> END IF;
The thing I would add would be two loops. The outer loop is the list of
tables. The inner loop finds all the appropriate columns to query and
concatenates them together so the query would look sort of like,
SQL> select * from t3;
FLD1 FLD2
SQL> select * from t3 where fld1 || fld2 like '%A%C%';
FLD1 FLD2
Then, you only scan each table once.
-- Galen deForest Boyer Sweet dreams and flying machines in pieces on the ground.Received on Tue Mar 04 2003 - 21:01:12 CST