How do I limit my PL/SQL query to search only relevant tables?

Question

I'm trying to write a PL/SQL script that searches the entire database for a string and report the tables and columns it finds it in. It looks like this:

DECLARE
  ncount NUMBER;
  vwhere VARCHAR2(1000) := '';
  vsearchstr VARCHAR2(1000) := 'search string here';
  vresult VARCHAR2(10000) := 'result: ';
  vtab VARCHAR2(1000) := '';
  vcol VARCHAR2(1000) := '';
BEGIN
  FOR k IN (SELECT a.table_name, a.column_name FROM all_tab_columns a WHERE a.data_type LIKE '%VARCHAR%')
  LOOP
    vtab := k.table_name;
    vcol := k.column_name;
    vwhere := ' where ' || vcolumnname || ' = :vsearchstr ';
    EXECUTE IMMEDIATE 'select count(1) from ' || vtab || vwhere INTO ncount USING vsearctstr;
    IF (ncount > 0)
    THEN
      vresult := CONCAT(vresult, vcol || ' ' || vtab || ', ');
    END IF;
  END LOOP;
  
  IF (LENGTH(vresult) > 1)
  THEN
    dbms_output.put_line(vresult);
  ELSE
    dbms_output.put_line('not found');
  END IF;
END;

When I run it, I get the following error:

It is essentially saying it doesn't recognize the table vtab in the line EXECUTE IMMEDIATE 'select count(1) from ' || vtab || vwhere INTO ncount USING vsearctstr.

So in order to see which table it's complaining about, I added the following exception block to the end of the script:

  EXCEPTION
    WHEN OTHERS
    THEN
    BEGIN
      dbms_output.put_line('exception: ' || vtab);
    END;

It tells me the table name is IND$.

I'm not sure what this table (or view) is and it doesn't look relevant anyway.

So my question is two fold: 1) If it is fetching IND$ in the FOR loop from k.table_name (which in turn is from all_tab_columns), why does it say it doesn't exist in the select query? 2) I'm not sure what IND$ is but I'm pretty sure I don't need to search it; So is there a way to limit my search to only relevant tables (not views)? By 'relevant', I mean tables that we created to store data for our application (as opposed to system tables or user tables, etc.).

Thanks very much.

Answer 1

all_tab_columns lists columns in all tables that your current schema has access to. In general, if you're going to use ALL_... views to build dynamic SQL, you should incorporate the OWNER column as well since it may return rows for tables in other schemas.

If you really only want to consider tables in your current schema, use user_tab_columns instead.

If you want to get more, or less, particular than that about which tables are 'relevant', then you'll probably need to hardcode specific rules into your query.

FYI: IND$ is part of the Oracle data dictionary and is in the SYS schema. (Although, it's always possible someone has created a table with that name in some other schema.) It's unusual that your application schema would have direct access to this.

Answer 2

If you know how to skip tables you don't want, do it - you might filter by OWNER, maybe table name, etc.

If you don't want to bother, include inner begin-exception-end block into the loop so that it skips errors (either silently, or display errors, or store them into some table). Here's one option:

DECLARE
  ncount NUMBER;
  vwhere VARCHAR2(1000) := '';
  vsearchstr VARCHAR2(1000) := 'search string here';
  vresult VARCHAR2(10000) := 'result: ';
  vtab VARCHAR2(1000) := '';
  vcol VARCHAR2(1000) := '';
BEGIN
  FOR k IN (SELECT a.table_name, a.column_name FROM all_tab_columns a WHERE a.data_type LIKE '%VARCHAR%')
  LOOP
  BEGIN
    vtab := k.table_name;
    vcol := k.column_name;
    vwhere := ' where ' || vcolumnname || ' = :vsearchstr ';
    EXECUTE IMMEDIATE 'select count(1) from ' || vtab || vwhere INTO ncount USING vsearctstr;
    IF (ncount > 0)
    THEN
      vresult := CONCAT(vresult, vcol || ' ' || vtab || ', ');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(vtab ||': '|| sqlerrm);
  END;
  END LOOP;
  
  IF (LENGTH(vresult) > 1)
  THEN
    dbms_output.put_line(vresult);
  ELSE
    dbms_output.put_line('not found');
  END IF;
END;

Answer 3

You need to:

• Also get the owner from all_tab_columns
• Use quoted identifiers around the owner, table_name and column_name so that the query does not fail for identifiers in different cases or with special characters.
• Handle errors (i.e. when you do not have the permissions to SELECT from a table).

To make it faster, you can:

• Filter the columns to only those that are long enough to contain the search string.

You can do that using:

DECLARE
  ncount NUMBER;
  vsearchstr VARCHAR2(1000) := 'search string here';
  vresult VARCHAR2(10000);
BEGIN
  FOR k IN (SELECT owner,
                   table_name,
                   column_name
            FROM   all_tab_columns
            WHERE  data_type LIKE '%VARCHAR%'
            AND    data_length >= LENGTH(vsearchstr))
  LOOP
    BEGIN
      EXECUTE IMMEDIATE
         'select count(1)
          from   "' || k.owner || '"."' || k.table_name || '"
          where  "' || k.column_name || '" = :1'
        INTO  ncount
        USING vsearchstr;

      IF (ncount > 0)
      THEN
        vresult := vresult || ','
                   || k.owner || '.' || k.table_name || '.' || k.column_name;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
  
  IF (LENGTH(vresult) > 1)
  THEN
    dbms_output.put_line(vresult);
  ELSE
    dbms_output.put_line('not found');
  END IF;
END;
/

db<>fiddle here

Answer 4

Here is the final query (which works):

DECLARE
  ncount NUMBER;
  vwhere VARCHAR2(1000) := '';
  vsearchstr VARCHAR2(1000) := '%your string here%';
  vresult VARCHAR2(10000) := '';
  vtab VARCHAR2(1000) := '';
  vcol VARCHAR2(1000) := '';
BEGIN
  FOR k IN (SELECT table_name, column_name
              FROM all_tab_columns
             WHERE data_type LIKE '%VARCHAR%'
               AND data_length >= LENGTH(vsearchstr)
               AND table_name NOT IN (SELECT view_name FROM all_views))
  LOOP
  BEGIN
    vtab := k.table_name;
    vcol := k.column_name;
    vwhere := ' where ' || vcol || ' like :vsearchstr ';
    EXECUTE IMMEDIATE 'select count(1) from OWNER.' || vtab || vwhere INTO ncount USING vsearchstr;
    IF (ncount > 0)
    THEN
      vresult := CONCAT(vresult, 'table: ' || vtab || ', column: ' || vcol || chr(13) || chr(10));
    END IF;
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
  END LOOP;

  IF (LENGTH(vresult) > 0)
  THEN
    dbms_output.put_line(vresult);
  ELSE
    dbms_output.put_line('string not found');
  END IF;
END;

I took Littlefoot's advice and added an exception block to catch the exception when the table is not found and just continue with the loop. I also took MT0's advice and checked the length of the column in the select query. I also included the owner in the select query.

This works. Thanks to everyone who gave their advice.