Comprehensive example v15
This example uses a pipe as a “mailbox." Three procedures are enclosed in a package named mailbox
. These procedures:
- Create the mailbox.
- Add a multi-item message to the mailbox (up to three items).
- Display the full contents of the mailbox.
CREATE OR REPLACE PACKAGE mailbox IS PROCEDURE create_mailbox; PROCEDURE add_message ( p_mailbox VARCHAR2, p_item_1 VARCHAR2, p_item_2 VARCHAR2 DEFAULT 'END', p_item_3 VARCHAR2 DEFAULT 'END' ); PROCEDURE empty_mailbox ( p_mailbox VARCHAR2, p_waittime INTEGER DEFAULT 10 ); END mailbox; CREATE OR REPLACE PACKAGE BODY mailbox IS PROCEDURE create_mailbox IS v_mailbox VARCHAR2(30); v_status INTEGER; BEGIN v_mailbox := DBMS_PIPE.UNIQUE_SESSION_NAME; v_status := DBMS_PIPE.CREATE_PIPE(v_mailbox,1000,FALSE); IF v_status = 0 THEN DBMS_OUTPUT.PUT_LINE('Created mailbox: ' || v_mailbox); ELSE DBMS_OUTPUT.PUT_LINE('CREATE_PIPE failed - status: ' || v_status); END IF; END create_mailbox; PROCEDURE add_message ( p_mailbox VARCHAR2, p_item_1 VARCHAR2, p_item_2 VARCHAR2 DEFAULT 'END', p_item_3 VARCHAR2 DEFAULT 'END' ) IS v_item_cnt INTEGER := 0; v_status INTEGER; BEGIN DBMS_PIPE.PACK_MESSAGE(p_item_1); v_item_cnt := 1; IF p_item_2 != 'END' THEN DBMS_PIPE.PACK_MESSAGE(p_item_2); v_item_cnt := v_item_cnt + 1; END IF; IF p_item_3 != 'END' THEN DBMS_PIPE.PACK_MESSAGE(p_item_3); v_item_cnt := v_item_cnt + 1; END IF; v_status := DBMS_PIPE.SEND_MESSAGE(p_mailbox); IF v_status = 0 THEN DBMS_OUTPUT.PUT_LINE('Added message with ' || v_item_cnt || ' item(s) to mailbox ' || p_mailbox); ELSE DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE in add_message failed - ' || 'status: ' || v_status); END IF; END add_message; PROCEDURE empty_mailbox ( p_mailbox VARCHAR2, p_waittime INTEGER DEFAULT 10 ) IS v_msgno INTEGER DEFAULT 0; v_itemno INTEGER DEFAULT 0; v_item VARCHAR2(100); v_status INTEGER; BEGIN v_status := DBMS_PIPE.RECEIVE_MESSAGE(p_mailbox,p_waittime); WHILE v_status = 0 LOOP v_msgno := v_msgno + 1; DBMS_OUTPUT.PUT_LINE('****** Start message #' || v_msgno || ' ******'); BEGIN LOOP v_status := DBMS_PIPE.NEXT_ITEM_TYPE; EXIT WHEN v_status = 0; DBMS_PIPE.UNPACK_MESSAGE(v_item); v_itemno := v_itemno + 1; DBMS_OUTPUT.PUT_LINE('Item #' || v_itemno || ': ' || v_item); END LOOP; DBMS_OUTPUT.PUT_LINE('******* End message #' || v_msgno || ' *******'); DBMS_OUTPUT.PUT_LINE('*'); v_itemno := 0; v_status := DBMS_PIPE.RECEIVE_MESSAGE(p_mailbox,1); END; END LOOP; DBMS_OUTPUT.PUT_LINE('Number of messages received: ' || v_msgno); v_status := DBMS_PIPE.REMOVE_PIPE(p_mailbox); IF v_status = 0 THEN DBMS_OUTPUT.PUT_LINE('Deleted mailbox ' || p_mailbox); ELSE DBMS_OUTPUT.PUT_LINE('Could not delete mailbox - status: ' || v_status); END IF; END empty_mailbox; END mailbox;
The following shows executing the procedures in mailbox
. The first procedure creates a public pipe using a name generated by the UNIQUE_SESSION_NAME
function.
EXEC mailbox.create_mailbox; Created mailbox: PG$PIPE$13$3940
Using the mailbox name, any user in the same database with access to the mailbox
package and DBMS_PIPE
package can add messages:
EXEC mailbox.add_message('PG$PIPE$13$3940','Hi, John','Can you attend a meeting at 3:00, today?','-- Mary'); Added message with 3 item(s) to mailbox PG$PIPE$13$3940 EXEC mailbox.add_message('PG$PIPE$13$3940','Don''t forget to submit your report','Thanks,','-- Joe'); Added message with 3 item(s) to mailbox PG$PIPE$13$3940
Finally, the contents of the mailbox can be emptied:
EXEC mailbox.empty_mailbox('PG$PIPE$13$3940'); ****** Start message #1 ****** Item #1: Hi, John Item #2: Can you attend a meeting at 3:00, today? Item #3: -- Mary ******* End message #1 ******* * ****** Start message #2 ****** Item #1: Don't forget to submit your report Item #2: Thanks, Item #3: Joe ******* End message #2 ******* * Number of messages received: 2 Deleted mailbox PG$PIPE$13$3940