How do I get dbms_output to show when running a script in command window in PL/SQL Developer?

Question

This is a follow up question to this

I am trying to run the following script in PL/SQL Developer in a Command Window:

DECLARE
  ncount NUMBER;
  vwhere VARCHAR2(1000) := '';
  vselect VARCHAR2(1000) := ' select count(1) from ';
  vsearchstr VARCHAR2(1000) := 'put your string here';
  vline VARCHAR2(1000) := '';
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;
/

This script is supposed to search the entire database for the string given in vsearchstr and output the table and column it found it in. It outputs 'no output' if the string isn't found.

When I run it, it tells me PL/SQL procedure successfully completed but I do not see any output coming from dbms_output. I expect to see something regardless of the results of the script as it outputs something to dbms_output regardless of whether it finds the string or not. This is the case even when I turn on serveroutput with set serveroutput on before running the script.

Can anyone tell what's wrong? Is the output being printed somewhere else? Where would this be?

Answer 1

It turns out the problem wasn't with dbms_output, but two things: 1) dbms_output.get_line(vline, istatus) was clearing the output buffer, and 2) there were only a limit set of table the FOR loop was searching through, none of which had any VARCHAR columns. I am expanding my search to all_tab_columns.