-- DBMS_EPG monitor
rem create and authorize dad as SCOTT
-- as SYS:
BEGIN
DBMS_EPG.create_dad
( dad_name => 'monitor'
, path => '/monitor/*'
);
END;
/
-- as SCOTT:
begin
DBMS_EPG.AUTHORIZE_DAD('monitor');
end;
/
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 or replace
package body pipe_monitor
as
procedure write_background_refresh
( p_pipename in varchar2
) is
begin
htp.p('');
end;
procedure monitor
( p_pipename in varchar2
) as
begin
htp.p('Monitor for Database Pipe ('||p_pipename||'');
write_background_refresh(p_pipename);
htp.p('');
htp.p('Monitoring Database Pipe '||p_pipename||'
');
htp.p('Most recent refresh time:
');
htp.p('');
-- htp.p('---');
htp.p('');
end;
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;
end pipe_monitor;
-- url on local XE:
http://127.0.0.1:8080/monitor/pipe_monitor.monitor?p_pipename=logging_pipe
-- other ports can be configured with
EXECUTE dbms_xdb.SETHTTPPORT(2100);
DECLARE
status NUMBER;
message VARCHAR2(80) := 'A message on the pipe';
pipe_name VARCHAR2(30) := 'logging_pipe';
BEGIN
DBMS_PIPE.pack_message(message);
status := DBMS_PIPE.send_message(pipe_name);
END;
-- run piece of PL/SQL that sends output to 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;