Press "Enter" to skip to content

Search any text string in all tables of current schema in Oracle

A procedure is given below to search any text string in all tables of current schema in Oracle.

Create the following procedure in your schema:

CREATE OR REPLACE PROCEDURE search_text (i_search_string IN VARCHAR2)
IS
   CURSOR c_search
   IS
      SELECT    ‘Select ”’
             || table_name
             || ”’, ‘
             || SUBSTR (REPLACE (fields, ‘,’, ‘||” ”||’), 1, 4000)
             || ‘ From ‘
             || table_name
             || ‘ Where instr(lower(‘
             || REPLACE (fields, ‘,’, ‘||’)
             || ‘), ‘
             || ””
             || LOWER (i_search_string)
             || ””
             || ‘) > 0 ‘
                sqlcmd
        FROM (  SELECT wm_concat (column_name) fields, table_name
                  FROM (  SELECT table_name, column_name
                            FROM user_tab_cols
                           WHERE data_type = ‘VARCHAR2’
                        ORDER BY table_name, column_id)
              GROUP BY table_name);

   TYPE t_rec_data IS RECORD (
      table_name   VARCHAR2 (100),
      v_data       VARCHAR2 (4000)
   );

   TYPE rec_data IS TABLE OF t_rec_data
                       INDEX BY BINARY_INTEGER;

   v_rec_data     rec_data;
   i              NUMBER := 0;
BEGIN
   DBMS_OUTPUT.enable (200000);

   FOR c IN c_search
   LOOP
      BEGIN
         EXECUTE IMMEDIATE c.sqlcmd
            INTO v_rec_data (i).table_name, v_rec_data (i).v_data;

         i := i + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;

 IF v_rec_data.COUNT > 0
   THEN
      FOR j IN v_rec_data.FIRST .. v_rec_data.LAST
      LOOP
         DBMS_OUTPUT.put_line (
            v_rec_data (j).table_name || ‘ | ‘ || v_rec_data (j).v_data);
      END LOOP;
   END IF;
END;

And then call it using the following:

set serveroutput on;

BEGIN
   search_text (‘marketing’); — where marketing is the string being searched
END;

It will list out the table names and the row data containing that string.

Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

2 Comments

  1. Thanks for the information. But I'm not clear what is "wm_concat "?

  2. wm_concat function available in wmsys schema, btw you can use listagg function instead, here is the modified cursor query:

    SELECT 'Select ''' || table_name || ''', ' || SUBSTR (REPLACE (fields, ',', '||'' ''||'), 1, 4000) || ' From ' || table_name || ' Where instr(lower(' || REPLACE (fields, ',', '||') || '), ' || '''' || LOWER (i_search_string) || '''' || ') > 0 ' sqlcmd FROM ( SELECT listagg(column_name,',') within group (order by column_name) fields, table_name FROM ( SELECT table_name, column_name FROM user_tab_cols WHERE data_type = 'VARCHAR2' ORDER BY table_name, column_id) GROUP BY table_name); – See more at: https://www.foxinfotech.in/2015/08/search-any-text-string-in-all-tables-oracle.html#sthash.o06C6S6g.dpuf

Leave a Reply

Your email address will not be published. Required fields are marked *