Re: Help with DBMS_OUTPUT

This is a multi-part message in MIME format.

--------------15A01C42FC0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Ed Jennings wrote:
>
> I'm using DBMS_OUTPUT in a stored procedure that print a key value
> after doing some computations and an insert. When I run this
> procedure from SQLPlus, I get no output until the stored proc is
> finished. I then get pummelled with a few hundred lines of output.
> The stored proc takes about 15 minutes to execute, therefore I can only
> assume that the output is being buffered somehow. Does anyone know if
> this is the case, and if there is a way to turn it off? I want
> to monitor the progress during runtime, and currently the output
> is of little use.
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> jennings_at_dca.net

OK, since I have seen this same problem come up again and again, I *will* post my debug package. So there. ;-)

By the way, I wrote this *years* ago (about 4) and was pleased that Steve Feuerstein(sp?) had something similar in his PL/SQL Programming book. It's really a simple solution.

The package is in debugpkg.sql. You need to have dbms_pipe and dbms_output installed. In your package or trigger code, put a line like:

        debug.put('This is my message here, variable = ' || :new.variable); or whatever you want to see output of. Then, you can enable debugging one of two ways:

1. If you want immediate output in your current session, use the enable_imm.sql file I'm sending. This uses dbms_output and will run into the buffering problem described in the newsgroup.
2. If you want to see the output in another session as the program is running, you will need to enable debugging in the application (Forms, another SQL*Plus session, the package itself, whatever) by using the debug.enable procedure, then start a new session and use the enable.sql file I'm sending. You will need to run the debug.get part of it repeatedly -- I usually just hit slash(/) and return.

Enjoy!

-- 
Diana Duncan	     | My opinions are my own.
Sr. Consultant	     | 
REALOGIC, Inc.	     | Excitement, Adventure and
dduncan_at_realogic.com | Really Wild Things - Z.B.

--------------15A01C42FC0
Content-Type: text/plain; charset=us-ascii; name="Debugpkg.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="Debugpkg.sql"

drop package debug;
create package debug is
  -- Place debug.put statements in PL/SQL.
  procedure put (msg in varchar2 default '');
  -- To see the debugging statements in another Oracle session as they are 
  -- executed, you must 
  --	1) enable debugging in the session that will be executing the code
  --	2) run the program/SQL statement
  --	3) in another SQL*Plus session, SET SERVEROUT ON
  --	4) enable debugging
  --	5) make repeated calls to debug.get
  --
  -- To see the debugging statements after execution in the same Oracle 
  -- session as they are executed, you must
  --	1) SET SERVEROUT ON
  --	2) enable debugging with the immediate flag on, debug.enable(TRUE);
  --	3) run the program/SQL statement
  --
  procedure get;
  procedure enable (imm in boolean default FALSE);
  procedure disable;
end debug;
/
create package body debug is
  enabled	boolean;
  immediate	boolean;
  s		integer;
  linelength	CONSTANT number := 255;
  procedure put (msg in varchar2 default '') is
  begin
    if enabled and immediate then
      if length(msg) <= linelength then
        dbms_output.put_line(msg);
      else
        put(substr(msg, 1, linelength));
        put(substr(msg, linelength+1));
      end if;
    elsif enabled then
      if length(msg) <= linelength then
        dbms_pipe.pack_message(msg);
        dbms_pipe.pack_message(user);
        dbms_pipe.pack_message(sysdate);
        s := dbms_pipe.send_message('debugmsg', 300, 32768);
      else
        put(substr(msg, 1, linelength));
        put(substr(msg, linelength+1));
      end if;
    end if;
  end put;
  procedure get is
    msg		varchar2(2000);
    username	varchar2(30);
    prev_username	varchar2(30);
    when_put	date;
    prev_when	date := to_date('01-JAN-1600', 'DD-MON-YYYY');
  begin
    if enabled then
      s := dbms_pipe.receive_message('debugmsg', 0);
      if s != 0 then
	dbms_output.put_line('No debugging messages.');
      end if;
      while s = 0 loop
	dbms_pipe.unpack_message(msg);
	dbms_pipe.unpack_message(username);
	dbms_pipe.unpack_message(when_put);
	if username != prev_username or
	   trunc(when_put,'MI') != prev_when then
	  dbms_output.put_line('Put by ' || username || ' on ' ||
			       to_char(when_put, 'MM/DD/YY HH24:MI:SS'));
	  prev_username := username;
	  prev_when := trunc(when_put,'MI');
	end if;
	dbms_output.put_line(msg);
        s := dbms_pipe.receive_message('debugmsg', 0);
      end loop;
    end if;
  end get;
  procedure enable (imm in boolean default FALSE) is
  begin
    enabled := TRUE;
    immediate := imm;
    dbms_output.enable(1000000);
  end enable;
  procedure disable is
  begin
    enabled := FALSE;
    dbms_output.disable;
  end disable;
begin
  enabled := FALSE;
  immediate := FALSE;
end debug;
/

--------------15A01C42FC0
Content-Type: text/plain; charset=us-ascii; name="Enable_imm.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="Enable_imm.sql"

set serverout on
begin
  debug.enable(TRUE);
end;
/

--------------15A01C42FC0
Content-Type: text/plain; charset=us-ascii; name="Enable.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="Enable.sql"

set serverout on
begin
  debug.enable;
  debug.get;
end;
/

--------------15A01C42FC0--