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 — MT0
– MT0, Commented Jan 7, 2022 at 0: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
– Hermann Baer, Commented Jan 7, 2022 at 0: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
– gib65, Commented Jan 7, 2022 at 5:13

MT0 · Accepted Answer · 2022-01-07 10:21:08Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related