Debugging via Dbms_Pipe

When running code you sometimes have the need to debug the progress of the statements. One way is to use a table and write your debug output into it by means of an autonomous transaction. But sometimes it can be easier to just hand the debug info to a different session. Say hi to Dbms_Pipe…

If you don’t want to have the “burden” of the context switching introduced by an autonomous transaction to save the debug output towards a table and can’t use Dbms_Output, maybe Dbms_Pipe is the solution to your problem. Among other things you can use it to transport log info towards a “listening” session.

This is quite easy to do, have the session you want to debug send it’s log info into a pipe and have a “listening” session retrieve it from the pipe. Below example code a an anonymous block which send data into a pipe.

DECLARE
  PIPENAME CONSTANT VARCHAR2(50) := 'LIJNTJE';

  l_status NUMBER;
BEGIN
  Dbms_Pipe.pack_message('Hello world');

  l_status := Dbms_Pipe.send_message(PIPENAME);

  IF (l_status != 0) THEN
    Raise_Application_Error('-20000', 'Sending through pipe failed');
  END IF;
END;
/

The code above will send “Hello world” via the pipe defined by the constant PIPENAME. The code to retrieve the data from the pipe is equally simple.

DECLARE
  PIPENAME CONSTANT VARCHAR2(50) := 'LIJNTJE';

  l_status NUMBER;
  l_data   VARCHAR2(4000);
BEGIN
  l_status := Dbms_Pipe.receive_message(PIPENAME);

  IF (l_status != 0) THEN
    Raise_Application_Error('-20000', 'Reading from pipe failed');
  END IF;

  Dbms_Pipe.unpack_message(l_data);
  Dbms_Output.put_line(l_data);
END;
/

As you can see it’s quite easy to utilise Dbms_Pipe to output log messages. The example code will read just one message, so if you want to keep on reading you need to build some sort of loop to retrieve the data.