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.