How do you search through multiple tables?

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 19 Feb 2003 12:40:20 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703537129@lnewton.leeds.lfs.co.uk>

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)? Received on Wed Feb 19 2003 - 06:40:20 CST