LewisC's An Expert's Guide To Oracle Technology
The latest release of the EnterpriseDB database, Postgres Plus Advanced Server (AS) 8.3, includes additional Oracle compatibility features. Two of those new features are DBMS_PIPE and DBMS_ALERT. Old Oracle hands will recognize those two packages that are used for interprocess communication. This post is a quick tutorial on using these two features. If you've used them in Oracle, you would have no problem using them in AS.
DBMS_ALERT
DBMS_ALERT allows you to send an alert to any processes that are registered to hear (listen) that alert. The listening process can register to hear multiple alerts. When any alert comes that matches the listen list, the receiving process will take action. A process can block, i.e. freeze, while waiting for an alert or it can continue processing and periodically poll for alerts. This is accomplished via a timeout parameter.
The first step in alerting is to register to hear an alert. This is done via the register (DBMS_ALERT.REGISTER) process. Once the process is registered it can check for a specific alert (DBMS_ALERT.WAITONE) or it can check for any registered alert (DBMS_ALERT.WAITANY). When a process is no longer interested in the alerts, it can deregister from a single alert (DBMS_ALERT.REMOVE) or it can deregister from all alerts (DBMS_ALERT.REMOVEALL).
To send an alert, a process will call DBMS_ALERT.SIGNAL. This raises the alert and registered listeners are notified.
Here is a simple example using two EDB*Plus sessions.
SQL> set sqlprompt "Sess1> "
Sess1>
Sess1> exec dbms_alert.register('alertme');
Sess1> set serveroutput on
Sess1> declare
2 v_status integer;
3 v_message varchar2(200);
4 begin
5 -- wait for one second and return
6 dbms_alert.waitone('alertme', v_message, v_status, 1);
7 dbms_output.put_line('Message: ' || v_message || ', Status: ' || v_status );
8 end;
9 /
Message: , Status: 1
EDB-SPL Procedure successfully completed.
Sess1>
In this case, I had it wait for just a single second. Since I had not sent a message it timed out (status = 1).
Now I'll wait 240 seconds and send a message from a second session.
In session 1:
Sess1> declare
2 v_status integer;
3 v_message varchar2(200);
4 begin
5 -- wait for 240 seconds and return
6 dbms_alert.waitone('alertme', v_message, v_status, 240);
7 dbms_output.put_line('Message: ' || v_message || ', Status: ' || v_status );
8 end;
9 /
In session 2:
SQL> set sqlprompt "Sess2> "
Sess2> exec dbms_alert.signal('alertme', 'This is an alert!');
EDB-SPL Procedure successfully completed.
Sess2> commit;
COMMIT completed.
Sess2>
Back in session 1:
Message: This is an alert!, Status: 0
EDB-SPL Procedure successfully completed.
Sess1>
Note the commit. It is required.
DBMS_PIPE
DBMS_PIPE also allows you to send data to other sessions in the database. You pack a buffer with a message or messages and then send the message. Unlike an alert, the session listening for the message pulls it off and other sessions will not see it. Think of DBMS_ALERT as a multi consumer queue and DBMS_PIPE as a single consumer queue. That's not an entirely technically correct analogy but it's close.
Like an alert, a pipe has a name. With a pipe though, you may have public or private pipes. In Oracle, a public pipe may be read and written to by any session in the database while a private pipe can only be written to and read from a session connected as the same user as the creator. In AS, any session can read any pipe.
A pipe can be created with the DBMS_PIPE.CREATE_PIPE function. If you put a message on a pipe that has not been created using CREATE_PIPE, the database will automatically create an implicit public pipe. An implicit pipe will free up when there are no messages left in the buffer. An explicit pipe will free up only when DBMS_PIPE.REMOVE_PIPE is called.
Another difference from an alert is that a pipe can send data type specific messages. A pipe can send a varchar2, date, number or raw message. You put the message in the buffer via the DBMS_PIPE.PACK_MESSAGE procedure. This procedure also identifies the data type. You pull a message off using DBMS_PIPE.UNPACK_MESSAGE. Once you've packed a message on the buffer, you can call DBMS_PIPE.SEND_MESSAGE to send it. Before unpacking, you must call DBMS_PIPE.RECEIVE_MESSAGE. You can put multiple messages in the buffer before sending it.
To determine the data type of a message, you would call DBMS_PIPE.NEXT_ITEM_TYPE in between calls to DBMS_PIPE.UNPACK_MESSAGE. The return value from the NEXT_ITEM_TYPE call is an integer value: 0 = no more data, 9 = number, 11 = varchar2, 13 = date and 23 = raw.
Here is a very simple example to send a varchar2 and a date message from one session to another. I am not using DBMS_PIPE.CREATE_PIPE so I am creating an implicit public pipe. I am reusing Sess1 and Sess2 sessions from before.
Sess1> declare
2 v_status integer;
3 v_date date;
4 v_char varchar2(100);
5 begin
6 v_date := sysdate; -- sysdate = May 5, 2008
7 v_char := 'This is a char message.';
8 dbms_pipe.pack_message(v_char);
9 dbms_pipe.pack_message(v_date);
10 v_status := dbms_pipe.send_message('thepipe', 0);
11 dbms_output.put_line('Status: ' || v_status );
12 end;
13 /
Status: 0
EDB-SPL Procedure successfully completed.
Sess1>
Sess2> declare
2 v_status integer;
3 v_date date;
4 v_char varchar2(100);
5 v_data_type integer;
6 begin
7 v_status := dbms_pipe.receive_message('thepipe', 0);
8 v_data_type := dbms_pipe.next_item_type;
9 dbms_pipe.unpack_message(v_char);
10 dbms_output.put_line('Data Type: ' || v_data_type ||
11 ', Message: ' || v_char);
12 dbms_pipe.unpack_message(v_date);
13 dbms_output.put_line('Date: ' || to_char(v_date) );
14 end;
15 /
Data Type: 11, Message: This is a char message.
Date: 05-MAY-08
EDB-SPL Procedure successfully completed.
Sess2>
These functions are useful when you need them, especially for notifications to applications running outside the database. You can get additional information about these packages and others in the EnterpriseDB Oracle Compatibility Guide.
LewisC
|