Search database for specific string

As a general approach, assuming that you do not know the column and data, I would use the administration views to describe all tables that contain string columns and then dynamically create and run SQL statements that check each column for the data. You could exclude columns that are shorter than the data you are searching for. Someone may step in and give you an example and it is possible that there is a generic facility in Oracle that can achieve this for you. This is going to be very inefficient and slow for anything other than the smallest of databases.

What have you looked at already?  There are MANY examples out there on how to do this.

You do realize that running this type of query is EXTREMELY slow and problematic?

Here is a previously asked question on that:
 https://www.experts-exchange.com/questions/28069987/identifying-columns-in-all-the-tables-with-a-specific-value.html?anchorAnswerId=39001854#a39001854

If you can get by with a more generic answer:  IMG-IT-AS004 exists in this row but no idea what column, there might be a slightly better approach.  I remember some time ago I wrote something like that leveraging Oracle Text. It didn't give you the column but could tell you the row.

I don't have access to my SQL Bag-O-Tricks right now so I can't post it.  I also cannot find where I posted it here.

I tried the following I found online, and it seems to work, although I did see a problem with a temporary table.
Does anyone see any issue with this query?  I only need to run it on one occasion, now, to identify the string in any tables so it can then be updated.

    CREATE or REPLACE PROCEDURE SEARCH_DB(SEARCH_STR IN VARCHAR2, TAB_COL_RECS OUT VARCHAR2) IS
      match_count integer;
      qry_str varchar2(1000);
      CURSOR TAB_COL_CURSOR IS
          SELECT TABLE_NAME,COLUMN_NAME,OWNER,DATA_TYPE FROM ALL_TAB_COLUMNS WHERE DATA_TYPE in ('CHAR','VARCHAR2') AND OWNER='SCOTT';
          BEGIN  
            FOR TAB_COL_REC  IN TAB_COL_CURSOR
            LOOP
              qry_str := 'SELECT COUNT(*) FROM '||TAB_COL_REC.OWNER||'.'||TAB_COL_REC.TABLE_NAME||
              ' WHERE '||TAB_COL_REC.COLUMN_NAME;
                       qry_str := qry_str||' like '||SEARCH_STR;
                       --dbms_output.put_line( qry_str );
                EXECUTE IMMEDIATE  qry_str  INTO match_count;
                IF match_count > 0 THEN          
                   dbms_output.put_line( qry_str );
                  --dbms_output.put_line( TAB_COL_REC.TABLE_NAME ||' '||TAB_COL_REC.COLUMN_NAME ||' '||match_count);    
                    TAB_COL_RECS := TAB_COL_RECS||'@@'||TAB_COL_REC.TABLE_NAME||'##'||TAB_COL_REC.COLUMN_NAME;
                END IF;
          END LOOP;
     END SEARCH_DB;    

Execute Statement

  DECLARE
    SEARCH_STR VARCHAR2(200);
    TAB_COL_RECS VARCHAR2(200);
    BEGIN
      SEARCH_STR := 10;
      SEARCH_DB(
        SEARCH_STR => SEARCH_STR,
        TAB_COL_RECS => TAB_COL_RECS
      );
     DBMS_OUTPUT.PUT_LINE('TAB_COL_RECS = ' || TAB_COL_RECS);
     END;

No need to search a NUMBER field for a string.  I've seen similar code around the Internet and it should work for VARCHAR2 fields.

What about NVARCHAR2, NCHAR, CHAR and CLOBs?

If you are going to replace the string in the values why do you need to query it first?  Just perform the updates and report on where you updated them?

Do you know that the value you are searching for is stored in upper case?  Or, do you also want to find it if it may be stored in lower case?  If yes, that adds another wrinkle (and another performance penalty) to an already very-slow search.  Depending on the size of your database and on the size and speed of your server and storage system, this kind of search could run for hours or days.

I don't have a solution yet, I am able to query, and it turns out the database is not that large so I am expecting at most 60 tables that have the string in the column.  However, I need to be able to output as my results the table, column, and the value of the string.  I have the rest of the query except that part, and I would greatly appreciate help with this last part.
Thanks very much.

tHANK YOU. It worked