Why do I get no output from this query (searching database for string)?

Question

I'm an Oracle/PL/SQL Developer newbie, and I'm struggling to figure out how to see the output of this query:

DECLARE
  ncount NUMBER;
  vwhere VARCHAR2(1000) := '';
  vselect VARCHAR2(1000) := ' select count(1) from ';
  vsearchstr VARCHAR2(1000) := '1301 250 Sage Valley Road NW';
  vline VARCHAR2(1000) := '';
  istatus INTEGER;
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR k IN (SELECT a.table_name, a.column_name FROM user_tab_cols a WHERE a.data_type LIKE '%VARCHAR%')
  LOOP
    vwhere := ' where ' || k.column_name || ' = :vsearchstr ';
    EXECUTE IMMEDIATE vselect || k.table_name || vwhere
      INTO ncount
      USING vsearchstr;
    IF (ncount > 0)
    THEN
      dbms_output.put_line(k.column_name || ' ' || k.table_name);
    ELSE
      dbms_output.put_line('no output');
    END IF;
  END LOOP;
  dbms_output.get_line(vline, istatus);
END;

I got this script from https://community.oracle.com/tech/developers/discussion/2572717/how-to-search-a-particular-string-in-whole-schema. It's supposed to find a string (vsearchstr) in the entire database. When I run this in PL/SQL Developer 14.0.6, it spits out no errors, says it took 0.172 seconds, but I don't see any output. I'm expecting the output to show under the Output tab:

I know the string '1301 250 Sage Valley Road NW' exists in the database so it should be finding it. Even if it doesn't, the ELSE block should be outputting 'no output'.

From what I understand, dbms_output.put_line() adds the given string to a buffer, and dbms_output.get_line() prints it to the output target (whatever it's set to). I understand that dbms_output needs to be enabled (hence the line DBMS_OUTPUT.ENABLE) and dbms_output.get_line() will only run after the BEGIN/END block it's in completes (I don't know if this means it has to be put outside the BEGIN/END block, but I couldn't avoid certain errors every time I did).

I've read through various stackoverflow posts about this issue, as well as a few external site:

https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68001/dbms_out.htm#1000449 https://www.tutorialspoint.com/plsql/plsql_dbms_output.htm

...but nothing seems to be working.

How can I see the output, or if there's something wrong in the query above, can you tell what it is?

Thanks.

Answer 1

To enable output from DBMS_OUTPUT in PL/SQL Developer see this answer.

I'm looking for an alternative keyword to user_tab_cols for all schemas in the DB

Use ALL_TAB_COLS and catch the exceptions when you do not have enough privileges to read the table (and use quoted identifiers to match the case of user/table/column names):

DECLARE
  found_row  PLS_INTEGER;
  vsearchstr VARCHAR2(1000) := '1301 250 Sage Valley Road NW';
BEGIN
  FOR k IN (SELECT owner,
                   table_name,
                   column_name
            FROM   all_tab_cols t
            WHERE  data_type LIKE '%VARCHAR%'
            -- Ignore columns that are too small
            AND    data_length >= LENGTH(vsearchstr)
            -- Ignore all oracle maintained tables
            -- Not supported on earlier Oracle versions
            AND    NOT EXISTS (
                     SELECT 1
                     FROM   all_users u
                     WHERE  t.owner = u.username
                     AND    u.oracle_maintained = 'Y'
                   )
           )
  LOOP
    DECLARE
      invalid_privileges EXCEPTION;
      PRAGMA EXCEPTION_INIT(invalid_privileges, -1031);
    BEGIN
      EXECUTE IMMEDIATE 'SELECT 1 FROM "' || k.owner || '"."' || k.table_name || '" WHERE "' || k.column_name || '" = :1 AND ROWNUM = 1' 
        INTO  found_row
        USING vsearchstr;

      dbms_output.put_line('Found: ' || k.table_name || '.' || k.column_name);
    EXCEPTION
      WHEN invalid_privileges THEN
        NULL;
      WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('Not found: ' || k.table_name || '.' || k.column_name);
    END;
  END LOOP;
END;
/