0

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?

Daniel Widdis
  • 8,424
  • 13
  • 41
  • 63
gib65
  • 1,709
  • 3
  • 24
  • 58
  • Google SET SERVER OUTPUT ON – EvilTeach Mar 30 '22 at 01:04
  • If the question is *How do I get dbms_output to show when running a script in a command window?* then it would be best not to complicate the test with all the loops and conditional logic in your question. Just `begin dbms_output.put_line('Hello'); end;` and lose the rest. – William Robertson Apr 02 '22 at 15:59
  • There is rarely any point in calling `dbms_output.enable`. If it's not already enabled in the calling application, it won't get the lines and print them after the database call. – William Robertson Apr 02 '22 at 16:10
  • Just to add, a Test window is generally best for running a single PL/SQL block like this. – William Robertson Apr 02 '22 at 16:11

1 Answers1

0

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.

gib65
  • 1,709
  • 3
  • 24
  • 58