Re: How do you search through multiple tables?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 19 Feb 2003 18:13:31 GMT
Message-ID: <b30hga$1h2fbv$1@ID-82536.news.dfncis.de>

Norman

If a table has multiple columns to be searched, it will entail multiple table scan. I'd suggest to do one table in one go like so:

set serveroutput on size 1000000 format wrapped

declare

  v_old_table user_tab_columns.table_name%type;
  v_where     Varchar2(4000);
  v_first_col Boolean := true;
  type rc     is ref cursor;
  c           rc;
  v_rowid     Varchar2(20);

begin
  for r in (
    select
      table_name, column_name, data_type     from
      user_tab_columns
    where
      data_type like '%CHAR%'
    order by table_name) loop

    if v_old_table is null then
      v_old_table := r.table_name;
    end if;        

    if v_old_table <> r.table_name then
      v_first_col := true;

      dbms_output.put_line('searching ' || v_old_table);

      open c for 'select rowid from ' || v_old_table || v_where;

      fetch c into v_rowid;
      loop
        exit when c%notfound;
        dbms_output.put_line('  rowid: ' || v_rowid);
        fetch c into v_rowid;
      end loop;
      


      v_old_table := r.table_name;

    end if;

    if v_first_col then
      v_where := ' where ' || r.column_name || ' = ''my value''';     else

      v_where := v_where || ' or ' || r.column_name || ' = ''my value''';
      v_first_col := false;

    end if;
  end loop;

end;
/

On the other hand, yours prints the column which mine doesn't, but that could be changed as well

Rene Nyffenegger

> Afternoon,
>
> the following (tested) procedure works for me. If you have any LONG,
> RAW, LONG RAW, CLOBs or BLOBs then they are not going to be searched -
> and probably can't anyway (without DBMS_LOB at least).
>
> Have fun.
>
> Regards,
> Norman.
>
> PS. You could replace DBMS_OUTPUT with UTL_FILE if you have a likelyhood
> of getting more than 1,000,000 characters out from the search, but this
> works for me.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
> CREATE OR REPLACE PROCEDURE FindText(TextToFind IN VARCHAR2) AS
>
> MyResult NUMBER := 0;
>
> BEGIN
> -- select a list of tables and columns to search. These will only be
> VARCHAR
> -- or CHAR columns which are defined as being GE the length of the
> test we are
> -- looking for.
> 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;
>
> -- Have to trap exceptions otherwise when we hit the first table
> -- which does not have the text in it, the procedure barfs !
> EXCEPTION
> WHEN NO_DATA_FOUND THEN NULL;
> END;
> END LOOP;
>
> -- Not strictly required as the user will only ever see the output
> when
> -- the procedure is finished - unless they are using TOAD and have
> the
> -- DBMS_OUTPUT polling turnbed on :o)
> DBMS_OUTPUT.PUT_LINE('Finished.');
> END;
> /
>
>
> -----Original Message-----
> From: navaed7024_at_hotmail.com (E. Navarro)
> [mailto:navaed7024_at_hotmail.com]
> Posted At: Tuesday, February 18, 2003 10:35 PM
> Posted To: server
> Conversation: How do you search through multiple tables?
> Subject: How do you search through multiple tables?
>
>
> Oracle 8.1.7.4 EE
> Solaris 7
>
> All,
>
> I am trying to figure out a way to search through multiple tables (over
> 50)for
> a particular piece of data. I had a user enter some data into a table a
> while
> back, and can't figure out which table he entered the data. Does anyone
> have a script that can traverse through table columns and locate data
> (in my
> case it was a string)?
>
>

-- 
  no sig today

Received on Wed Feb 19 2003 - 12:13:31 CST