One of the topics I discussed in today’s Masterclass Advanced Database Development was inter-session communication – or how database sessions can exchange information. One of my favorite vehicles to accomplish that is the database pipe (leveraging the supplied package dbms_pipe) – a light weight, fast, in memory facility. Pipes can be used in several ways to exchange messages of type number, date, raw or varchar2 – up to 4000 bytes – between sessions.
A typical application of the pipe is to provide a real time logging facility that – unlike dbms_output.put_line – makes the logging from within a database session available while the PL/SQL code is running. So the requirement I will implement is: show in a browser window the logging messages that are written to the database pipe set up to collect the logging data so the progress of PL/SQL programs can be monitored in real-time. Also, have the browser monitor update/refresh automatically – without the user having to refresh all the time.
The idea would be something like the next figure:
The next picture demonstrates the end-result that the code in this article implements: a browser window that lists the logging messages with the latest ones on top. The messages have been collected from a piupe into which they were written by a PL/SQL program executing in a different session from the session handling the HTTP requests from the browser
To implement this log monitor, we have to go through a few steps:
create the database pipe and add the statements to the PL/SQL program that write the logging messages to the pipe
create a procedure in the PL/SQL package that is invoked in a background AJAX call to provide the most recent messages from the pipe
expose the package through a URL path associated with a DAD
Create the database pipe
On the top you see the code that creates the pipe – as a public pipe called MESSAGE_PIPE.
On the left, you see one session sending a message on the pipe MESSAGE_PIPE: string (‘A message on the pipe’). The session sending the message has no idea who if anyone is listening for the message. It just publishes the message, assuming someone will listen…
And indeed, a second session is listening to the pipe: it waits for a message on the pipe – with a long timeout of DBMS_PIPE.maxwait. When a message is received, it is unpacked and the contents is written to the server output. So the second session displays the output originating in the first session. The pipe works.
Add the statements to the PL/SQL program that write the logging messages to the pipe
This article uses a simple PL/SQL program that iterates through a cursor that fetches all employees in table EMP and processes each of them. It then waits for one second before starting work on the next employee. This procedure uses a nested procedure called log(msg in varchar2) that puts the message passed in as a message on the pipe MESSAGE_PIPE.
declare cursor c_emp is select * from emp; procedure log(msg in varchar2) is status NUMBER; pipe_name VARCHAR2(30) := 'logging_pipe'; BEGIN DBMS_PIPE.pack_message(msg); status := DBMS_PIPE.send_message(pipe_name); END; begin log('Anonymous block starts processing'); dbms_lock.sleep(2); for r_emp in c_emp loop log('Working on employee '||r_emp.ename); dbms_lock.sleep(1); end loop; dbms_lock.sleep(2); log('Done processing'); end;
Create a PL/SQL package that will render the HTML page for the logging monitor
create or replace package pipe_monitor as procedure monitor ( p_pipename in varchar2 ); procedure retrieve_pipe_contents ( p_pipename in varchar2 ); end pipe_monitor;
Create a procedure in the PL/SQL package that is invoked in a background AJAX call to provide the most recent messages from the pipe
Procedure retrieve_pipe_contents listens for one or more messages on the logging pipe whose name is passed as parameter in the AJAX call from the browser. The wait time is 0 – if there are no messages, an empty response is quickly returned to the browser. When one or more messages are retrieved from the pipe, they are concattenated together – with new line feeds between them – and returned as text in the HTTP response.
procedure retrieve_pipe_contents ( p_pipename in varchar2 ) as inbound_status NUMBER; message VARCHAR2(4000); l_pipe_contents varchar2(30000); l_first char(1):= 'Y'; begin -- Listen for incoming messages on the pipe loop inbound_status := DBMS_PIPE.receive_message ( pipename => p_pipename , timeout => 0 ); -- do not wait for message; if there is none, continue; -- Message received successfully. IF inbound_status = 0 THEN DBMS_PIPE.unpack_message(message); l_pipe_contents:= message ||case l_first when 'Y' then '' else chr(13)||chr(10) end ||l_pipe_contents ; -- most recent comes first l_first:='N'; else exit; END IF; end loop; htp.p(l_pipe_contents); end retrieve_pipe_contents;
Expose the package through a URL path associated with a DAD
The usual steps for making a package available through a an HTTP call handled by the database are:
- define Database Access Descriptor – with associated URL path
- authorize the DAD in/for the schema that contains the package
-- as SYS BEGIN DBMS_EPG.create_dad ( dad_name => 'monitor' , path => '/monitor/*' ); END; -- as SCOTT: begin DBMS_EPG.AUTHORIZE_DAD('monitor'); end;
Trying the whole thing out
Open a browser. Open the page http://127.0.0.1:8080/monitor/pipe_monitor.monitor?p_pipename=logging_pipe – using the hostname and port that apply to your database environment.
Download the sources for this article: EPGBasedPipeMonitor.