Oracle database search all tables for string, return row data

Question

I need to search all tables in an oracle database for a specific string and return all the records where that string is found. I am working with SQL Developer. There are several really useful scripts and stored procedures that have been posted online that provide a way to search the entire database and they all seem to be able to return the table name and the column name where the string is found. I actually want to see the rows like a select * statement but I would like it to be for all the tables in my database. I want to note that I am very much a beginner and I'm not familiar with advanced PL/SQL scripting and database concepts so though I've tried and tried I can't seem to figure it out so any input would be appreciated.

Here is a script that I was trying to modify (someone else's solution)

declare
 l_count number := 0;
 l_str varchar2(20) := '%test%';
begin
 for rec in (select table_name, column_name
             from user_tab_columns
             where data_type in ( 'VARCHAR2' , 'VARCHAR', 'CHAR' ) ) loop
  execute immediate 'select count(*) from '||rec.table_name||
                ' where '||rec.column_name||' like '''||l_str||'''' into l_count;
  if l_count > 0 then
   dbms_output.put_line('Table : '||rec.table_name||'  '||rec.column_name);
  end if;
 end loop;
end;
/

Solution

Are you trying to build a full database search based on a key word? You can get table names , table column names and row counts by following code. But you cannot get row data within same code, you can get row data based on search results.

   --Set output size
   SET serveroutput ON size 1000000

   DECLARE
      -- var table Name for cursor loop.
      t_c1_tname      user_tab_columns.table_name%TYPE;
      -- var column name for dynamic sql statement.
      t_c1_cname      user_tab_columns.column_name%TYPE;
      -- var string for dynamic sql statement.
      t_command       VARCHAR2(200);
      -- var for your search key word.
      l_str varchar2(20) := '%test%';
      -- var for dynamic cursor.
      t_cid           INTEGER;
      -- var for total row counts.
      t_total_records NUMBER(10);
      -- var for stat of executing dynamic sql statement.
      stat            INTEGER;
      --var for each loop row counts.
      row_count       INTEGER;
      -- var for minimum search result, here I set value = 0;
      t_limit         INTEGER := 0;    -- Only show tables with more rows

      -- cursor gets all table name, column name.
      CURSOR c1 IS select table_name, column_name
        from user_tab_columns
        where data_type in ( 'VARCHAR2' , 'VARCHAR', 'CHAR' );
    BEGIN
      t_limit := 0;
      OPEN c1;
      LOOP
      FETCH c1 INTO t_c1_tname,t_c1_cname;
      EXIT WHEN c1%NOTFOUND;
      -- Here create dynamic sql statement. 
      t_command := 'SELECT COUNT(0) FROM '||t_c1_tname || ' where ' || t_c1_cname ||' like '''|| l_str||'''';
      t_cid := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(t_cid,t_command,DBMS_SQL.native);
      DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
      -- Here execute dynamic sql statement. 
      stat := DBMS_SQL.EXECUTE(t_cid);
      row_count := DBMS_SQL.FETCH_ROWS(t_cid);
      -- Here get total row counts for each loop.
      DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
      IF t_total_records > t_limit THEN
         --Here output results
         DBMS_OUTPUT.PUT_LINE(RPAD(t_c1_tname,55,' ')||RPAD(t_c1_cname,55,' ')||
                        TO_CHAR(t_total_records,'99999999')||' record(s)');
         -- here you can insert results into your table.
         --INSERT INTO search_db_results VALUES (t_c1_tname,t_c1_cname,t_total_records);
      END IF;
      DBMS_SQL.CLOSE_CURSOR(t_cid);
      END LOOP;
      CLOSE c1;

     -- COMMIT if you have any insert statement.
     -- COMMIT;
    END;
    /

Comments

[user1750939]

Thanks, I was referring to a full database search based on a key word. You seem to be correct that this cannot be done within the same code as I've come to realize. I am able to get the table name and row name. Is there a way to store the results in a table or an array then use it to retrieve those rows? I've tried creating an empty table and tried to insert my results into it using "execute immediate 'insert into search_db_results..." while within the loop but was not able to insert successfully. Again, my knowledge level is very basic so if you could be descriptive that would really help

[Luke Liu]

Change "DBMS_OUTPUT.PUT_LINE..." code to "INSERT INTO search_db_results VALUES (t_c1_tname,t_c1_cname,t_total_records); ".

[Luke Liu]

I have added some comments on code above, hope it helps.