Searching multiple columns query does not appear to function

Question

I found this query on an old post but there is no activity.(I am not strong in SQL)

I am trying to search all column for a specific value.

I am using the latest Oracle SQL Developer and executing the SQL using a SQL Worksheet.

Here is the SQL:

DECLARE
  match_count integer;
  v_search_string varchar2(4000) := 'Male';
BEGIN 
  FOR t IN (SELECT owner,
                   table_name,
                   column_name
              FROM all_tab_columns
             WHERE data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2',
                                 'CLOB', 'NCLOB') AND table_name = 'PersonG')
           
  LOOP  
    BEGIN
      EXECUTE IMMEDIATE   
        'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
        ' WHERE '||t.column_name||' = :1'  
         INTO match_count 
        USING v_search_string;
      IF match_count > 0 THEN
        dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
      END IF;
    EXCEPTION
      WHEN others THEN
        dbms_output.put_line( 'Error encountered trying to read ' ||
                              t.column_name || ' from ' ||
                              t.owner || '.' || t.table_name );
    END;
  END LOOP;
END;
/

When I execute it, I immediatly get the Script Output window with just PL/SQL procedure successfully completed. I know the string is in the table.

If I remove AND table_name = 'PersonG' the script takes so long to run the connection times out(probably our VPN that I have no control over) so I tried to restrict to a table name. I also tried AND owner = 'user' instead of table but it was the same.

If I execute:

                SELECT owner,
                   table_name,
                   column_name
              FROM all_tab_columns
             WHERE data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2',
                                 'CLOB', 'NCLOB') AND table_name = 'PersonG'

it gives me the columns from that table.

Am I doing something wrong?

Also, is there a way to change it to LIKE instead of matching?

Thanks.

Answer 1

the output of dbms_output does not go to the script window. It just goes to a buffer, for the client to deal with as it pleases. In SQL Dev, it's a separate window, 'DBMS Output'. And it has to be enabled.