Re: How do you search through multiple tables?

hi norman,

a small improvement suggestion

please change

         EXECUTE IMMEDIATE 'select 1 from dual where exists '||
                                   '(select 1 from '||x.table_name||
                                   ' where '||x.column_name||' like
''%'|| TextToFind||'%'')'
                            INTO MyResult;

into

         EXECUTE IMMEDIATE 'select 1 from dual where exists '||
                                   '(select 1 from '||x.table_name||
                                   ' where '||x.column_name||' like
''%'' || :TextToFind || ''%'')'  USING TextToFind
                            INTO MyResult;

thus reducing the number of hard parses when excessive using FindText

matthias

Norman Dunbar wrote:
> 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 - 09:16:20 CST