Browser-based log-monitor for database applications (alternative to dbms_output,leveraging pipe and Embedded PL/SQL Gateway with a touch of AJAX)

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 PL/SQL package that will render the HTML page for the logging monitor – including the JavaScript that does the periodic refresh
• 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

Package pipe_monitor contains a procedure called monitor. This procedure will render – through htp.p – the initial monitor page, that contains the text area to display the logging messages as a title. More interesting is the JavaScript that is also contained in the page:

create or replace
package pipe_monitor
as
procedure monitor
( p_pipename in varchar2
);

procedure retrieve_pipe_contents
( p_pipename in varchar2
);

end pipe_monitor;

The BODY tag for the page as an onLoad attribute that makes sure that when the page is loaded, the function refreshMonitor() is invoked. This function invokes function xmlhttpPost that does an AJAX call to the procedure pipe_monitor.retrieve_pipe_contents. When the response to this call is received, the callback handler function refreshMonitorContents is invoked. This function adds the contents from the response to the textarea – latest entries on top. Then this function updates the refresh time shown on the page and finally schedules the next refresh of the log monitor, by calling the function scheduleRefresh that uses a JavaScript time out schedule the next refresh.

create or replace
package body pipe_monitor
as

procedure write_background_refresh
( p_pipename in varchar2
) is
begin
htp.p('<script type="text/javascript">

monitorRefreshPeriod = 2000;
strURL ="/monitor/pipe_monitor.retrieve_pipe_contents";

function scheduleRefresh ( )
{
  setTimeout ( "refreshMonitor()", monitorRefreshPeriod );
}

function writeRefreshTime() {
  var d = new Date();
  var curr_hour = d.getHours();
  var curr_min = d.getMinutes();
  var curr_sec = d.getSeconds();
  var time = curr_hour + ":" + curr_min + ":" + curr_sec ;
  document.getElementById("mostRecentRefreshTime").innerText = time ;
  document.getElementById("mostRecentRefreshTime").textContent = time;
}

function refreshMonitor ( )
{
  xmlhttpPost(strURL);
}

function refreshMonitorContents (newContents ) {
  if (newContents.length > 2) {
	   document.monitorform.monitor.value = newContents + document.monitorform.monitor.value;
  }
  writeRefreshTime();
  scheduleRefresh();
}

function xmlhttpPost(strURL) {
    var xmlHttpReq = false;
    var self = this;
    // Mozilla/Safari
    if (window.XMLHttpRequest) {
        self.xmlHttpReq = new XMLHttpRequest();
    }
    // IE
    else if (window.ActiveXObject) {
        self.xmlHttpReq = new ActiveXObject("Microsoft.XMLHTTP");
    }
    self.xmlHttpReq.open("POST", strURL, true);
    self.xmlHttpReq.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
    self.xmlHttpReq.onreadystatechange = function() {
        if (self.xmlHttpReq.readyState == 4) {
            refreshMonitorContents(self.xmlHttpReq.responseText);
        }
    }
    self.xmlHttpReq.send(getquerystring());
}

function getquerystring() {
    qstr = "p_pipename='||p_pipename||'"
    return qstr;
}
</script>');
end;

procedure monitor
( p_pipename in varchar2
) as
begin
  htp.p('<HTML><HEAD><TITLE>Monitor for Database Pipe ('||p_pipename||'</TITLE></HEAD>');
  write_background_refresh(p_pipename);
  htp.p('<BODY onLoad="refreshMonitor();" >');
  htp.p('<H1>Monitoring Database Pipe '||p_pipename||'</H1>');
  htp.p('Most recent refresh time: <SPAN id="mostRecentRefreshTime"></SPAN><BR/><BR/>');
  htp.p('<form name="monitorform"><textarea name="monitor"></textarea></form>');
  htp.p('</BODY></HTML>');
end;

end;

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.

Resources

Download the sources for this article: EPGBasedPipeMonitor.