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.

Did you use `SET SERVEROUTPUT ON` to enable the server to return the buffer to the client? Run that once in the session first and then run your script. Apart from that, your code appears to work [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=95b8c3aff63370d60e3c8e0d82cc7d14) — MT0, Jan 07 '22 at 00:14
Also, you talk about "the database". Your code is going after USER_TAB_COLS, which only loops over all the columns (and therefore tables) in the schema you are connected to. Not all schemas in the database. — Hermann Baer, Jan 07 '22 at 00:23
And you do not need to use `DBMS_OUTPUT.ENABLE`. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=e561e4daa463db15ecb2d64c53d11025) — MT0, Jan 07 '22 at 00:23
@MT0 yes I tried that but apparently that's not the way to do it in PL/SQL Developer. Instead `DBMS_OUTPUT.ENABLE` is the way to do it (right after `BEGIN`). You're probably right that I don't need it as the Output tab shows it to be enabled by default. @Hermann I'm looking for an alternative keyword to `user_tab_cols` for all schemas in the DB but I'm not finding anything. Also, how do I tell which schema I'm connected to? How to change it? How to see all schemas? Thanks both for your help. — gib65, Jan 07 '22 at 05:13

score 1 · Accepted Answer · answered Jan 07 '22 at 10:21

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;
/

Thanks MT0, that script did the trick. – gib65 Jan 08 '22 at 02:40 — gib65, Jan 08 '22 at 02:40

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

1 Answers1