3.1.3.1.1 Return values

The CREATE_PIPE procedure has a single return value of 0, indicating success. This value is returned even if the pipe already existed and can be used by the user.

3.1.3.1.2 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the user attempts to create a pipe that already exists and is private to another user or uses a NULL pipename:

3.1.3.1.3 Restrictions

Note the following restrictions on calling CREATE_PIPE:

• Pipenames are limited to 128 byes in length, are case-insensitive, and cannot contain NLS characters.

• Pipenames must not begin with "ORA$", as these names are reserved for use by Oracle Corporation.

3.1.3.1.4 Example

This example is a function that encapsulates CREATE_PIPE and returns either the Boolean value TRUE, indicating that the pipe can be used by the caller, or FALSE otherwise. The function traps the ORA-23322 exception using PRAGMA EXCEPTION_INIT and returns FALSE if this exception is raised.

The makepipe function can be found in the dbpipe package discussed in the " Section 3.1.7, "DBMS_PIPE Examples" " section. It is created by the dbpipe.sql script.

/* Filename on companion disk:  dbpipe.sql */* PACKAGE BODY dbpipe IS    cannot_use_pipe   EXCEPTION;    PRAGMA EXCEPTION_INIT(cannot_use_pipe,-23322);    null_pipename   EXCEPTION;    PRAGMA EXCEPTION_INIT(null_pipename,-23321);     /*    || encapsulates DBMS_PIPE.CREATE_PIPE and returns     || FALSE if ORA-23322 is raised indicating    || the pipename is already used and not accessible    || to the caller    */    FUNCTION 

makepipe       (pipename_IN IN VARCHAR2       ,maxsize_bytes_IN IN INTEGER DEFAULT 8192       ,private_IN IN BOOLEAN DEFAULT TRUE)    RETURN BOOLEAN    IS       call_status  INTEGER;      BEGIN       call_status := DBMS_PIPE.CREATE_PIPE                         (pipename_IN                         ,maxsize_bytes_IN                         ,private_IN);       RETURN call_status = 0;    EXCEPTION       WHEN cannot_use_pipe OR null_pipename       THEN          RETURN FALSE;    END makepipe;  END dbpipe;

The CREATE_PIPE function creates a private pipe by default. Private pipes may be used only by sessions connected to the same username (schema) as the pipe's creator or executing stored PL/SQL programs owned by that schema. Public pipes may be accessed by all sessions with execute privileges on DBMS_PIPE. Note that CREATE_PIPE is the only way to create a private database pipe. Pipes created implicitly by the SEND_MESSAGE function are always public.

Pipes created using CREATE_PIPE should be explicitly removed using the REMOVE_PIPE function.

Database pipes are empty upon creation. However, if the named database pipe already exists and is available to the user calling CREATE_PIPE, the function will return 0, but the pipe is not emptied. Avoid writing code that assumes that a successful call to CREATE_PIPE results in an empty pipe.

The maxpipesize parameter of CREATE_PIPE determines the maximum size in memory of the database pipe. This places a limit both on the amount of Oracle shared pool memory and on the maximum size of all messages the pipe can hold at any time. When designing applications that use database pipes, it is important to estimate the number and the size of the messages that the pipe will need to contain, so that maxpipesize can be determined. Basically, the objective is to size the pipe as small as possible while making sure that there is plenty of room to handle anticipated message traffic. Note that after creation, a pipe's maximum size can be increased using the SEND_MESSAGE function or by destroying and recreating the pipe.

3.1.3.2.1 Return values

The REMOVE_PIPE procedure has a single return value of 0, indicating success. This value is returned even if the pipe did not exist.

3.1.3.2.2 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the user attempts to remove a pipe belonging to another user or passes a NULL pipename:

3.1.3.2.3 Restrictions

Pipenames must not begin with "ORA$" as these names are reserved for use by Oracle Corporation.

3.1.3.2.4 Example

This example is a function that encapsulates REMOVE_PIPE and returns the Boolean value TRUE indicating that the pipe was successfully removed (or did not exist) or FALSE indicating that the pipe exists but cannot be removed by the caller. The function traps the ORA-23322 error using PRAGMA EXCEPTION_INIT and returns FALSE if this exception is raised.

The closepipe function can be found in the dbpipe package discussed in the " Section 3.1.7 " section and defined in the dbpipe.sql script.

/* Filename on companion disk:  dbpipe.sql */* PACKAGE BODY dbpipe IS    cannot_use_pipe   EXCEPTION;    PRAGMA EXCEPTION_INIT(cannot_use_pipe,-23322);    null_pipename   EXCEPTION;    PRAGMA EXCEPTION_INIT(null_pipename,-23321);    /*     || encapsulates DBMS_PIPE.REMOVE_PIPE and returns     || FALSE if ORA-23322 is raised indicating    || the pipename exists and is not removable    || by the caller    */    FUNCTION 

closepipe       (pipename_IN IN VARCHAR2)    RETURN BOOLEAN    IS       call_status  INTEGER;     BEGIN       call_status := DBMS_PIPE.REMOVE_PIPE(pipename_IN);       RETURN (call_status = 0);     EXCEPTION       WHEN cannot_use_pipe OR null_pipename       THEN          RETURN FALSE;    END closepipe;  END dbpipe;

NOTE: It is good practice to remove pipes explicitly when they are no longer needed. This can sometimes be difficult, since database pipes are typically shared by multiple sessions, so it is hard to know when they can be removed. Empty pipes that have not been removed will eventually be aged out of the shared pool by Oracle.

3.1.3.3.1 Example

This example shows the use of RESET_BUFFER at the beginning of a program that packs a PL/SQL record into a message.

The pack_send_request procedure can be found in the pipesvr package discussed in the " Section 3.1.7 " section. The following code has been excerpted from that example package:

/* Filename on companion disk: 

pipesvr.sql */* PROCEDURE pack_send_request    (request_rec_IN IN request_rectype    ,return_code_OUT OUT NUMBER) IS BEGIN    /* discard any previous unsent message items */    DBMS_PIPE.RESET_BUFFER;     /* pack message in standard order */    DBMS_PIPE.PACK_MESSAGE(request_protocol);    DBMS_PIPE.PACK_MESSAGE(request_rec_IN.response_pipe);    DBMS_PIPE.PACK_MESSAGE(request_rec_IN.service);     /*    || send message to request pipe nowait    */    return_code_OUT := DBMS_PIPE.SEND_MESSAGE                         (pipename => request_pipe                         ,timeout  => 0); END pack_send_request;

Oracle advises that the RESET_BUFFER procedure should not generally be needed. I make sure, however, to use it in the following places:

• Exception handlers of programs using UNPACK_MESSAGE procedures

• At the beginning of programs designed to pack specific messages into the buffer using PACK_MESSAGE procedures

In handling unpack exceptions, it is safe practice to initialize the message buffer after an unexpected item type is encountered. When packing messages, it is important to be sure that only the intended message items are packed into the buffer. By resetting the message buffer, programs can protect themselves from sending any previously packed but unsent message items.

3.1.3.4.1 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the user attempts to purge a pipe belonging to another user or passes a NULL pipename:

3.1.3.4.2 Restrictions

Note the following restrictions on calling PURGE:

• Pipenames are limited to 128 bytes in length, are case-insensitive, and cannot contain NLS characters.

• Pipenames must not begin with "ORA$", as these names are reserved for use by Oracle Corporation.

3.1.3.4.3 Example

This example shows a procedure that will purge all pipes to which the calling user has access. The purge_all_pipes procedure can be found in the dbpipe package discussed in " Section 3.1.7 ." You will need SELECT privilege on SYS.V_$DB_PIPES to create the package.

/* Filename on companion disk: 

dbpipe.sql */* PACKAGE BODY dbpipe IS    cannot_use_pipe   EXCEPTION;    PRAGMA EXCEPTION_INIT(cannot_use_pipe,-23322);     PROCEDURE 

purge_all_pipes    IS       /* gets names of all pipes */       CURSOR all_pipes_cur       IS       SELECT name         FROM sys.v_$db_pipes;     BEGIN       FOR all_pipes_rec IN all_pipes_cur       LOOP          BEGIN             DBMS_PIPE.PURGE(all_pipes_rec.name);           /* ignore cannot_use_pipe exception */          EXCEPTION             WHEN cannot_use_pipe             THEN                null;             WHEN OTHERS             THEN                RAISE;          END;       END LOOP;    END purge_all_pipes;  END dbpipe;

Emptying a pipe using PURGE releases the SGA memory associated with the pipe. Oracle can then reclaim this memory for other uses based on normal least-recently-used (LRU) aging of the shared pool.

WARNING: Pipes that are not in use, but still have unpurged messages in them, waste SGA memory because they cannot be aged out of the shared pool. Very large pipes in this condition can lead to serious database performance problems.

Note also that calling PURGE may cause the user session's message buffer to be overwritten by messages discarded from the pipe. Be sure to send any message packed in the session buffer prior to calling PURGE. Expert technical reviewer Dan Clamage points out that this is because the implementation of PURGE is "to simply do RECEIVE_MESSAGE with timeout = 0 until the pipe is empty."

The PURGE procedure will implicitly create a public pipe of the given name if one does not already exist. This is somewhat nonintuitive, in that emptying a nonexistent pipe causes a new empty pipe to exist.

3.1.3.5.1 Example

This example creates a pipe with a name unique to the session for receiving messages intended specifically for this session.

DECLARE    /*     || declare and initialize my_pipename variable with     || string unique to session     */    my_pipename VARCHAR2(128) := DBMS_PIPE.UNIQUE_SESSION_NAME;     call_status INTEGER;  BEGIN    /*     || create pipe as public so anyone can send message to it     */    call_status := DBMS_PIPE.CREATE_PIPE                      (pipename => my_pipename                      ,private => FALSE); END;

Applications in which user sessions receive messages on database pipes usually create a unique pipe for each session to use. This helps ensure that sessions get only messages intended for them. As illustrated in the previous example, UNIQUE_SESSION_NAME is often used to generate pipe names that are unique to the session.

3.1.4.1.1 Exceptions

The program does not raise any package exceptions. The Oracle.06558 Oracle exception is raised if the message buffer becomes full and no more items can be packed.

3.1.4.1.2 Restrictions

Note that the user session message buffer is limited to 4096 bytes.

3.1.4.1.3 Example

The following example creates a procedure to pack and send a PL/SQL record to a database pipe. Notice the overloading of PACK_MESSAGE, which packs items of different datatypes into the local message buffer using the same procedure call.

/* Filename on companion disk: 

pipex1.sql */* DECLARE    /*     || PL/SQL block illustrating use of     || DBMS_PIPE.PACK_MESSAGE to pack and send    || a PL/SQL record to a pipe    */    TYPE friend_rectype IS RECORD       (name       VARCHAR2(60)       ,birthdate  DATE       ,weight_lbs NUMBER       );     friend_rec  friend_rectype;     PROCEDURE 

pack_send_friend       (friend_rec_IN IN friend_rectype       ,pipename_IN IN VARCHAR2)    IS       call_status   INTEGER;    BEGIN       /*        ||notice the PACK_MESSAGE overloading        */       DBMS_PIPE.PACK_MESSAGE(friend_rec_IN.name);       DBMS_PIPE.PACK_MESSAGE(friend_rec_IN.birthdate);       DBMS_PIPE.PACK_MESSAGE(friend_rec_IN.weight_lbs);              call_status := DBMS_PIPE.SEND_MESSAGE                         (pipename=>pipename_IN,timeout=>0);        IF call_status != 0       THEN          DBMS_OUTPUT.PUT_LINE('Send message failed');       END IF;     END pack_send_friend;  BEGIN     /*     || OK, now use the procedure to send a friend_rec     */    friend_rec.name := 'John Smith';    friend_rec.birthdate := TO_DATE('01/14/55','MM/DD/YY');    friend_rec.weight_lbs := 175;     pack_send_friend(friend_rec,'OPBIP_TEST_PIPE'); END;

As in the example, it is good practice to encapsulate the packing and sending of an entire message as part of a logical program unit. Otherwise, additional calls to PACK_MESSAGE could add unexpected items to a message before sending, perhaps causing confusion on the receiving end.

3.1.4.2.1 Exceptions

The program does not raise any package exceptions. The ORA-06558 Oracle exception is raised if the message buffer becomes full and no more items can be packed.

3.1.4.2.2 Restrictions

Note that the user session's local message buffer is limited to 4096 bytes.

3.1.4.2.3 Example

In this example, a hex string is converted to RAW, packed into the session buffer using the PACK_MESSAGE_RAW procedure, and sent to a pipe.

/* Filename on companion disk: pipex1.sql */* DECLARE    hex_data    VARCHAR2(12):='FFEEDDCCBBAA';    raw_data    RAW(6);    call_status INTEGER; BEGIN    /* create some raw data */    raw_data := HEXTORAW(hex_data);     /*    || pack and send raw data on pipe    */    DBMS_PIPE.PACK_MESSAGE_RAW(raw_data);    call_status := DBMS_PIPE.SEND_MESSAGE('OPBIP_TEST_PIPE');     IF call_status != 0    THEN       DBMS_OUTPUT.PUT_LINE('Send message failed');    END IF; END;

Applications that need to send large or sensitive data items through database pipes may benefit from using compression and/or encryption on the data and packing the results using PACK_MESSAGE_RAW.

3.1.4.3.1 Exceptions

The program does not raise any package exceptions. The ORA-06558 Oracle exception is raised if the message buffer becomes full and no more items can be packed.

3.1.4.3.2 Restrictions

Note that the user session's local message buffer is limited to 4096 bytes.

3.1.4.3.3 Example

This (somewhat contrived) example shows a trigger that places the ROWIDs of new or modified rows of the emp table into a database pipe.

/* Filename on companion disk: pipex1.sql */* CREATE OR REPLACE TRIGGER emp_AIU    AFTER INSERT OR UPDATE ON emp    FOR EACH ROW DECLARE    rowid_pipename  VARCHAR2(20) := 'ROWID_PIPE';    call_status   INTEGER; BEGIN    /*     || pack and send the rowid to a pipe     */    DBMS_PIPE.PACK_MESSAGE_ROWID(:NEW.rowid);    call_status := DBMS_PIPE.SEND_MESSAGE(rowid_pipename);     IF call_status != 0    THEN       RAISE_APPLICATION_ERROR(-20001, 'Trigger emp_AIU failed');   END IF; 

END; 

3.1.4.4.1 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the message buffer contains no more items, or if the item parameter does not match the datatype of the next item in the buffer:

3.1.4.4.2 Example

This example shows a procedure using UNPACK_MESSAGE to receive and unpack the PL/SQL record type used in the example for PACK_MESSAGE. Note that even though UNPACK_MESSAGE is overloaded on datatype, the correct version needs to be called for the next item in the buffer, or an ORA-06559 error will be raised. This example is a companion to the PACK_MESSAGE example. Together these two examples illustrate a complete pack, send, receive, and unpack DBMS_PIPE communications cycle.

/* Filename on companion disk: pipex1.sql */* set serveroutput on size 100000  DECLARE    /*     || PL/SQL block illustrating use of     || DBMS_PIPE.UNPACK_MESSAGE to receive and    || unpack a PL/SQL record from a pipe    */    TYPE friend_rectype IS RECORD       (name       VARCHAR2(60)       ,birthdate  DATE       ,weight_lbs NUMBER       );     friend_rec  friend_rectype;     PROCEDURE

 receive_unpack_friend       (friend_rec_OUT OUT friend_rectype       ,pipename_IN IN VARCHAR2)    IS       call_status   INTEGER;    BEGIN        call_status := DBMS_PIPE.RECEIVE_MESSAGE                         (pipename=>pipename_IN,timeout=>0);       /*        ||NOTE: UNPACK_MESSAGE overloaded but we must       ||      call the correct version        */       DBMS_PIPE.UNPACK_MESSAGE(friend_rec_OUT.name);       DBMS_PIPE.UNPACK_MESSAGE(friend_rec_OUT.birthdate);       DBMS_PIPE.UNPACK_MESSAGE(friend_rec_OUT.weight_lbs);           END receive_unpack_friend;  BEGIN    /*     || OK test the procedure, get rec from other example     */    receive_unpack_friend(friend_rec,'OPBIP_TEST_PIPE');     /* display results */    DBMS_OUTPUT.PUT_LINE('Friend name: '||friend_rec.name);    DBMS_OUTPUT.PUT_LINE('Friend birthdate: '||                      TO_CHAR(friend_rec.birthdate));    DBMS_OUTPUT.PUT_LINE('Friend weight: '||                      TO_CHAR(friend_rec.weight_lbs));    END;

Here is output from running the previous script after running the PACK_MESSAGE example script:

Friend name: John Smith Friend birthdate: 14-JAN-55 Friend weight: 175                                                                                    PL/SQL procedure successfully completed. 

As illustrated in the example, it is good practice to encapsulate the receipt and unpacking of messages into a single logical program unit. Message items must be unpacked in the same order in which they were packed.

NOTE: The numeric datatype identifiers in the ORA-06559 message do not match the item type values returned by the NEXT_ITEM_TYPE function.

3.1.4.5.1 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the message buffer contains no more items or the item parameter does not match the datatype of the next item in the buffer:

3.1.4.5.2 Example

In this example, a message with a raw data item is received, unpacked using the UNPACK_MESSAGE_RAW procedure, and displayed.

/* Filename on companion disk: pipex1.sql */* set serveroutput on size 100000  DECLARE    hex_data    VARCHAR2(12);    raw_data    RAW(6);    call_status INTEGER; BEGIN    /*     || receive and unpack the raw message     */    call_status := DBMS_PIPE.RECEIVE_MESSAGE('OPBIP_TEST_PIPE');    DBMS_PIPE.UNPACK_MESSAGE_RAW(raw_data);     /* display results */    hex_data := RAWTOHEX(raw_data);    DBMS_OUTPUT.PUT_LINE('hex of raw: '||hex_data); END;

Output from running this script immediately after the PACK_MESSAGE_RAW example script:

hex of raw: FFEEDDCCBBAA  PL/SQL procedure successfully completed.

Note that the numeric datatype identifiers in the ORA-06559 message do not match the item type values returned by the NEXT_ITEM_TYPE function.

3.1.4.6.1 Exceptions

The program does not raise any declared exceptions. The following Oracle errors are raised if the message buffer contains no more items or the item parameter does not match the datatype of the next item in the buffer:

3.1.4.6.2 Example

In this example, the database pipe that is filled by the EMP_AIU trigger from the example for PACK_MESSAGE_ROWID is emptied using UNPACK_MESSAGE_ROWID.

/* Filename on companion disk: 

pipex1.sql */* set serveroutput on size 100000  DECLARE    rowid_pipename  VARCHAR2(20) := 'ROWID_PIPE';    temp_rowid   ROWID;    call_status  INTEGER:=0; BEGIN    /*     || receive and unpack all rowids from pipe     */    WHILE call_status = 0    LOOP       call_status := DBMS_PIPE.RECEIVE_MESSAGE          (pipename=>rowid_pipename, timeout=>0);        IF call_status = 0       THEN          DBMS_PIPE.UNPACK_MESSAGE_ROWID(temp_rowid);           /* display rowid results */          DBMS_OUTPUT.PUT_LINE(ROWIDTOCHAR(temp_rowid));       END IF;    END LOOP; END; /

The script output looks like this:

SQL>  14 rows updated.  Commit complete.  AAAA6OAAGAAAA0FAAA AAAA6OAAGAAAA0FAAB AAAA6OAAGAAAA0FAAC AAAA6OAAGAAAA0FAAD AAAA6OAAGAAAA0FAAE AAAA6OAAGAAAA0FAAF AAAA6OAAGAAAA0FAAG AAAA6OAAGAAAA0FAAH AAAA6OAAGAAAA0FAAI AAAA6OAAGAAAA0FAAJ AAAA6OAAGAAAA0FAAK AAAA6OAAGAAAA0FAAL AAAA6OAAGAAAA0FAAM AAAA6OAAGAAAA0FAAN AAAA6OAAGAAAA0FAAN  PL/SQL procedure successfully completed.

In the previous example, the unusual looking ROWIDs are the result of running the test on an Oracle8 database. (Remember that ROWID formats have changed in Oracle8.)

NOTE: The numeric datatype identifiers in the ORA-06559 message do not match the item type values returned by the NEXT_ITEM_TYPE function.

3.1.4.7.1 Example

The following PL/SQL block contains an inline procedure called unpack_all_items, which can unpack any message and display its contents using DBMS_OUTPUT. The unpack_all_items procedure uses NEXT_ITEM_TYPE to determine which version of UNPACK_MESSAGE to call for each item.

/* Filename on companion disk: 

pipex2.sql */* set serveroutput on size 100000  DECLARE    call_stat   INTEGER;        PROCEDURE 

unpack_all_items    IS       /*        || declare temp variables of all message item types       */       temp_varchar2  VARCHAR2(2000);       temp_date      DATE;       temp_number    NUMBER;       temp_rowid     ROWID;       temp_raw       RAW(2000);        next_item      INTEGER:=0;     BEGIN       next_item := DBMS_PIPE.NEXT_ITEM_TYPE;        /*       || unpack by item type and convert to varchar2       */       WHILE next_item > 0       LOOP          IF next_item = 9          THEN             DBMS_PIPE.UNPACK_MESSAGE(temp_varchar2);           ELSIF next_item = 6          THEN             DBMS_PIPE.UNPACK_MESSAGE(temp_number);             temp_varchar2 := 'NUMBER: '||TO_CHAR(temp_number);           ELSIF next_item = 11          THEN             DBMS_PIPE.UNPACK_MESSAGE_ROWID(temp_rowid);             temp_varchar2 := 'ROWID: '||ROWIDTOCHAR(temp_rowid);           ELSIF next_item = 12          THEN             DBMS_PIPE.UNPACK_MESSAGE(temp_date);             temp_varchar2 := 'DATE: '||                         TO_CHAR(temp_date,'YYYY:MM:DD:HH24:MI:SS');            ELSIF next_item = 23          THEN             DBMS_PIPE.UNPACK_MESSAGE_RAW(temp_raw);              temp_varchar2 := 'RAW: '||RAWTOHEX(temp_raw);           ELSE             temp_varchar2 := 'Invalid item type: '||TO_CHAR(next_item);            END IF;           /*           || display item and determine next item           */          DBMS_OUTPUT.PUT_LINE(temp_varchar2);          next_item := DBMS_PIPE.NEXT_ITEM_TYPE;        END LOOP;    END unpack_all_items;  BEGIN    /* empty pipe */    DBMS_PIPE.PURGE('OPBIP_TEST_PIPE');     /* initialize buffer  */    DBMS_PIPE.RESET_BUFFER;        /* pack in some data of different types */    DBMS_PIPE.PACK_MESSAGE('HELLO THERE');    DBMS_PIPE.PACK_MESSAGE(123456789);    DBMS_PIPE.PACK_MESSAGE(SYSDATE);    DBMS_PIPE.PACK_MESSAGE_RAW(HEXTORAW('FFDDEE2344AA'));     /* send and receive the message */    call_stat := DBMS_PIPE.SEND_MESSAGE('OPBIP_TEST_PIPE');    call_stat := DBMS_PIPE.RECEIVE_MESSAGE('OPBIP_TEST_PIPE');     /* call the generic unpack procedure */    unpack_all_items;     

END;

Here is output from running the example script:

SQL> @pipex2.sql  HELLO THERE NUMBER: 123456789 DATE: 1998:02:01:12:01:19 RAW: FFDDEE2344AA  PL/SQL procedure successfully completed.

The unpack_all_items inline procedure is a prototype for the procedure of the same name found in the dbpipe package, discussed in the " Section 3.1.7 " section.

NOTE: The item type values returned by the NEXT_ITEM_TYPE function do not match the numeric datatype identifiers in the ORA-06559 message.

3.1.5.1.1 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the user attempts to receive a message on a pipe belonging to another user or on a NULL pipename:

3.1.5.1.2 Restrictions

Note the following restrictions on calling SEND_MESSAGE:

• Pipenames are limited to 128 bytes in length, are case-insensitive, and cannot contain NLS characters.

• Pipenames must not begin with "ORA$", as these names are reserved for use by Oracle Corporation.

3.1.5.1.3 Example

This example shows the use of SEND_MESSAGE to send a message based on a PL/SQL record out on a database pipe.

The pack_send_request procedure can be found in the pipesvr package discussed in the " Section 3.1.7 " section.

 /* Filename on companion disk: pipesvr.sql */*  PROCEDURE pack_send_request 	(request_rec_IN IN request_rectype 	,return_code_OUT OUT NUMBER) IS BEGIN 	/* discard any previous unsent message items */ 	DBMS_PIPE.RESET_BUFFER;  	/* pack message in standard order */ 	DBMS_PIPE.PACK_MESSAGE(request_protocol); 	DBMS_PIPE.PACK_MESSAGE(request_rec_IN.response_pipe);      DBMS_PIPE.PACK_MESSAGE(request_rec_IN.service);       /*      || send message to request pipe nowait      */      return_code_OUT := DBMS_PIPE.SEND_MESSAGE                            (pipename => request_pipe                            ,timeout  => 0);  END pack_send_request;

The SEND_MESSAGE function will implicitly create a public pipe if the pipe specified by the pipename parameter does not already exist. Be careful not to assume that the call to SEND_MESSAGE has been successful. Note that in this example, the value returned by the call to SEND_MESSAGE is passed out of the pack_send_request procedure to its caller, so it will be the caller's responsibility to handle a nonzero return value.

Calls to SEND_MESSAGE will wait for up to the value of the timeout parameter in seconds for the call to complete. Applications using database pipes that stay full of messages may incur lengthy wait times or timeouts. When using SEND_MESSAGE under these circumstances, be careful to specify a timeout that users can tolerate.

Applications experiencing frequent timeouts or long waits when calling SEND_MESSAGE may benefit by increasing the size of the database pipe. This can be done by specifying a value for the maxpipesize parameter that is greater than the current maximum size of the pipe.

3.1.5.2.1 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the user attempts to receive a message on a pipe belonging to another user or on a NULL pipename:

3.1.5.2.2 Restrictions

Note the following restrictions on calling RECEIVE_MESSAGE:

• Pipenames are limited to 128 bytes in length, are case-insensitive, and cannot contain NLS characters.

• Pipenames must not begin with "ORA$", as these names are reserved for use by Oracle Corporation.

3.1.5.2.3 Example

This example shows the use of the RECEIVE_MESSAGE function to receive a message based on a PL/SQL record from a database pipe.

The receive_unpack_request procedure can be found in the pipesvr package discussed in the " Section 3.1.7 " section.

 /* Filename on companion disk: 

pipesvr.sql */*  PROCEDURE receive_unpack_request 	(timeout_IN IN INTEGER 	,request_rec_OUT OUT request_rectype 	 ,return_code_OUT OUT NUMBER) IS 	/* temp variables */ 	temp_protocol  request_protocol%TYPE; 	temp_return_code  NUMBER;  BEGIN  	temp_return_code := DBMS_PIPE.RECEIVE_MESSAGE 			(pipename => request_pipe 			 ,timeout  => timeout_IN);  	IF temp_return_code = 0 	THEN 		/* check if expected protocol */ 		DBMS_PIPE.UNPACK_MESSAGE(temp_protocol);        		IF temp_protocol = request_protocol 		THEN 			DBMS_PIPE.UNPACK_MESSAGE(request_rec_OUT.response_pipe); 			DBMS_PIPE.UNPACK_MESSAGE(request_rec_OUT.service); 		ELSE 			/* pipe message has unexpected protocol */ 			temp_return_code := -1; 			debug('UNKNOWN PROTOCOL: '||temp_protocol); 			DBMS_PIPE.RESET_BUFFER; 		END IF; 	END IF;  	return_code_OUT := temp_return_code;  EXCEPTION 	WHEN OTHERS THEN 		return_code_OUT := SQLCODE; 		debug('RECEIVE REQUEST EXCP: '||SQLERRM               ,force_TF_IN=>TRUE);          DBMS_PIPE.RESET_BUFFER;     END receive_unpack_request;

Calls to RECEIVE_MESSAGE will wait for up to the value of the timeout parameter in seconds for the call to complete. Applications accessing database pipes that are usually empty of messages can incur lengthy wait times or timeouts. When using RECEIVE_MESSAGE under these circumstances, be careful to specify a timeout that users can tolerate.

The RECEIVE_MESSAGE function will implicitly create a public pipe of the given name if one does not already exist.

3.1.7.3.1 Unpack_to_tbl and pack_from_tbl procedures

The two procedures unpack_to_tbl and pack_from_tbl implement the generic unpack and pack functionality. They use PL/SQL tables of records based on message_tbltype, which is designed to hold an ordered list of items of any datatype. Each row in a table of type message_tbltype contains two data values: an entry in the item_type field indicating the type of this message item (as returned by DBMS_PIPE.NEXT_ITEM_TYPE) and an entry in the field of the corresponding datatype with the value of this message item. The unpack_to_tbl procedure unpacks all items in a newly received message into a message table, indexing them in the table by their unpack order. The pack_from_tbl procedure takes a message table loaded in this fashion and repacks the original message into the message buffer in index order. The unpack_to_tbl procedure can also optionally use the DBMS_OUTPUT built-in package (described in Chapter 6, Generating Output from PL/SQL Programs ) to display the message unpacked.

Here are the full package bodies for unpack_to_tbl and pack_from_tbl. Note how unpack_to_tbl grew out of the example code for the DBMS.PIPE.NEXT_ITEM_TYPE function.

/* Filename on companion disk: dbpipe.sql */* PROCEDURE unpack_to_tbl       (message_tbl_OUT OUT message_tbltype       ,display_TF IN BOOLEAN := FALSE)    IS       /*       || NOTE: this procedure should only be called after       || a successful call to DBMS_PIPE.RECEIVE_MESSAGE       */        /* empty table to flush output table on exception */       null_message_tbl message_tbltype;              /*       || temp display variable extra long to account       || for RAWTOHEX conversion that can double size       */       temp_varchar2  VARCHAR2(8186);           next_item      INTEGER;       item_count     INTEGER := 0;        BEGIN        next_item := DBMS_PIPE.NEXT_ITEM_TYPE;              /*       || loop through all items, unpacking each by item       || type and convert to varchar2 for display       */       WHILE next_item > 0       LOOP          /*          || increment item count and store item type          */          item_count := item_count + 1;          message_tbl_OUT(item_count).item_type := next_item;           /*          || now use next_item to call correct unpack procedure,          || saving item to message_tbl          ||          || also stuff temp_varchar2 with string conversion          || of the item          */          IF next_item = 9          THEN             DBMS_PIPE.UNPACK_MESSAGE                (message_tbl_OUT(item_count).Mvarchar2);                          temp_varchar2 := 'VARCHAR2: '||                      message_tbl_OUT(item_count).Mvarchar2;          ELSIF next_item = 6          THEN             DBMS_PIPE.UNPACK_MESSAGE                (message_tbl_OUT(item_count).Mnumber);                          temp_varchar2 := 'NUMBER: '||                   TO_CHAR(message_tbl_OUT(item_count).Mnumber);           ELSIF next_item = 11             THEN             DBMS_PIPE.UNPACK_MESSAGE_ROWID                (message_tbl_OUT(item_count).Mrowid);                             temp_varchar2 := 'ROWID: '||                   ROWIDTOCHAR(message_tbl_OUT(item_count).Mrowid);           ELSIF next_item = 12          THEN             DBMS_PIPE.UNPACK_MESSAGE                (message_tbl_OUT(item_count).Mdate);                       temp_varchar2 := 'DATE: '||                TO_CHAR(message_tbl_OUT(item_count).Mdate,                                  'YYYY:MM:DD:HH24:MI:SS');            ELSIF next_item = 23          THEN             DBMS_PIPE.UNPACK_MESSAGE_RAW                (message_tbl_OUT(item_count).Mraw);                           temp_varchar2 := 'RAW: '||                RAWTOHEX(message_tbl_OUT(item_count).Mraw);           ELSE             temp_varchar2 := 'Invalid item type: '||                                  TO_CHAR(next_item);              RAISE invalid_item_type;          END IF;           /*           || display results and get next item type          */          IF display_TF          THEN             DBMS_OUTPUT.PUT_LINE(temp_varchar2);          END IF;                    next_item := DBMS_PIPE.NEXT_ITEM_TYPE;        END LOOP;     EXCEPTION       WHEN invalid_item_type       THEN          message_tbl_OUT := null_message_tbl;           END unpack_to_tbl;      PROCEDURE pack_from_tbl        (message_tbl_IN IN message_tbltype)    IS       /*       || packs the session message buffer from a generic       || message table       */    BEGIN       FOR i IN message_tbl_IN.FIRST..message_tbl_IN.LAST       LOOP          IF message_tbl_IN(i).item_type = 9          THEN             DBMS_PIPE.PACK_MESSAGE(message_tbl_IN(i).Mvarchar2);                       ELSIF message_tbl_IN(i).item_type = 6          THEN             DBMS_PIPE.PACK_MESSAGE(message_tbl_IN(i).Mnumber);                       ELSIF message_tbl_IN(i).item_type = 12          THEN             DBMS_PIPE.PACK_MESSAGE(message_tbl_IN(i).Mdate);                       ELSIF message_tbl_IN(i).item_type = 11          THEN             DBMS_PIPE.PACK_MESSAGE_ROWID(message_tbl_IN(i).Mrowid);                      ELSIF message_tbl_IN(i).item_type = 23          THEN             DBMS_PIPE.PACK_MESSAGE_RAW(message_tbl_IN(i).Mraw);           END IF;                       END LOOP;     END

pack_from_tbl;

I really like these utilities, but they suffer from a potentially serious limitation inherited from Oracle's rather poor memory management for PL/SQL tables of records. Basically, each row of a PL/SQL table of type message_tbltype consumes at least enough memory to fill out the variable-length columns, which is greater than eight kilobytes. Thus, unpacking a message with more than a few items in it can result in a very large PL/SQL table. This is demonstrated by the following test results, which use the my_session.memory procedure (see Chapter 11, Managing Session Information ) to display user session memory before and after unpacking a message.

/* Filename on companion disk: pipemem.sql. */* set serveroutput on size 100000  DECLARE    null_msg_tbl dbpipe.message_tbltype;    msg_tbl dbpipe.message_tbltype;    call_stat  INTEGER;  BEGIN     /* pack a message with a number of items */    FOR i in 1..50     LOOP       DBMS_PIPE.PACK_MESSAGE('message number: '||TO_CHAR(i));    END LOOP;        /* send and receive the message */    call_stat :=DBMS_PIPE.SEND_MESSAGE('PIPEX');    call_stat :=DBMS_PIPE.RECEIVE_MESSAGE('PIPEX');     /* use the generic unpack and show memory */    dbpipe.unpack_to_tbl(msg_tbl,FALSE);    my_session.memory;     /* now free, release and show memory */    msg_tbl := null_msg_tbl;    DBMS_SESSION.FREE_UNUSED_USER_MEMORY;    my_session.memory;  END; / session UGA: 41160 session PGA: 987576 session UGA: 41160 session PGA: 137760  PL/SQL procedure successfully completed.

The test shows that using unpack_to_tbl on a message with 50 items results in session PGA memory exceeding 900 kilobytes in size, most of which is wasted. Clearly, this is not a good scenario for a real application with many users, so the general usefulness of unpack_to_tbl and pack_from_tbl will have to wait until Oracle fixes these PL/SQL memory management problems.

NOTE: The problem caused by unpacking messages with more than a few items in them can result in a very large PL/SQL table that has been fixed in Oracle PL/SQL8.

3.1.7.3.2 The peek procedure

Developers or DBAs working with and testing DBMS_PIPE applications may really like the peek procedure built on top of the generic pack and unpack procedures. The peek procedure lets you pull a message off any pipe (which you have permission to use), look at its content, and place it back into the pipe, if you desire. Note that using peek will change the message order in the pipe, since database pipes are FIFO queues.

 /* Filename on companion disk: 

dbpipe.sql */* PROCEDURE peek 	(pipename_IN IN VARCHAR2 	,timeout_secs_IN IN INTEGER := 60 	,replace_message_TF IN BOOLEAN := TRUE)  IS 	/* || Takes a sample message from a pipe, unpacks and displays 	|| contents using unpack_to_tbl procedure. 	|| 	|| If replace_message_TF parameter is TRUE (the default), 	|| then the message is replaced into the pipe.NOTE: this  	|| will change message order in the pipe. 	*/  	message_tblmessage_tbltype; 	call_statusINTEGER;   	/* empty table used to free and release memory */ 	null_message_tblmessage_tbltype;   BEGIN  	call_status := DBMS_PIPE.RECEIVE_MESSAGE 		(pipename=>pipename_IN, timeout=>timeout_secs_IN);  	IF call_status = 0 	THEN 		unpack_to_tbl(message_tbl, display_TF=>TRUE);   		IF replace_message_TF  		THEN 			/* 			|| repack message into initialized buffer 			*/ 			DBMS_PIPE.RESET_BUFFER; 			pack_from_tbl(message_tbl);  			/* 			|| replace message on the pipe 			*/ 			call_status := DBMS_PIPE.SEND_MESSAGE 				(pipename=>pipename_IN, timeout=>0); 		END IF;  	 	/* 		|| empty message_tbl and free memory 		*/ 		message_tbl := null_message_tbl; 		DBMS_SESSION.FREE_UNUSED_USER_MEMORY;  	END IF;   END peek;

The peek procedure takes the memory management limitations into account. It returns memory consumed by the unpack_to_tbl procedure to the operating system by initializing message_tbl and calling DBMS_SESSION.FREE_UNUSED_USER_MEMORY.

3.1.7.3.3 The forward procedure

The final fun utility from dbpipe to be discussed here is the forward procedure, which lets you forward a message from one pipe to another. The procedure has four IN parameters:

from_pipename_IN and to_pipename_IN

Receiving and sending pipes for the message forwarding.

timeout_secs_IN

Determines the number of seconds to wait for a message to forward (on the pipe from_pipename_IN).

safe_mode_IN

A Boolean that determines which of two message forwarding techniques to use (which I call "safe" and "cool"). Safe mode forwarding uses unpack_to_tbl and pack_from_tbl to physically unbundle and recreate the message before sending it on to_pipename_IN. Cool mode forwarding is based on the idea that the best way to forward a message should be to execute DBMS_PIPE.RECEIVE_MESSAGE followed immediately by DBMS_PIPE.SEND_MESSAGE. After all, forwarding should be fast -- so why bother with the overhead of unpacking and repacking?

Well, it turns out that you cannot just receive and immediately send a message using DBMS_PIPE unless you have previously called the DBMS_PIPE. PACK_MESSAGE procedure. Why? I have no idea; it just seems to be another one of those mysteries of DBMS_PIPE that I happened to discover during my experimentation. I don't like the fact that it's mysterious, but I do like the fact that it works, so I used this "feature" to implement the cool forwarding mode.

 /* Filename on companion disk: 

dbpipe.sql. */* PROCEDURE forward 	(from_pipename_IN IN VARCHAR2 	,to_pipename_IN IN VARCHAR2 	,timeout_secs_IN IN INTEGER := 10 	,safe_mode_IN IN BOOLEAN := FALSE) IS 	call_status INTEGER; 	message_tbl message_tbltype;  BEGIN 	/* initialize buffer */ 	DBMS_PIPE.RESET_BUFFER;  	IF NOT safe_mode_IN 	THEN 		/* 		|| do an initial pack so COOL mode forwarding will work, 		|| why this is necessary is unknown 		*/ 		DBMS_PIPE.PACK_MESSAGE('bogus message'); 	END IF;  	/* 	|| receive the message on from_pipename, if success 	|| then forward on to_pipename 	*/ 	call_status := DBMS_PIPE.RECEIVE_MESSAGE 				(pipename=>from_pipename_IN 				,timeout=>timeout_secs_IN);  	IF call_status = 0 	THEN 		/* 		|| safe mode does full unpack and repack 		*/ 		IF safe_mode_IN 		THEN 		unpack_to_tbl(message_tbl); 		pack_from_tbl(message_tbl); 		END IF;  		/* 		|| OK, now send on to_pipename 		*/ 		call_status := DBMS_PIPE.SEND_MESSAGE 				(pipename=>to_pipename_IN 				,



timeout=>timeout_secs_IN); 	END IF;  

END 

forward;

3.1.7.5.1 Message types

Two record types have been declared for the two kinds of messages that will be handled: service request messages (sent from client to server) and server status messages (sent from server to client). Corresponding to each record (message) type is a protocol identifier to use when unpacking messages.

3.1.7.5.2 Pipenames

The following pipenames are established for proper message separation:

• request_pipe, into which all client requests are placed for receipt by the server

• my_response_pipe, from which each session receives its response from the server

3.1.7.5.3 Pack/send, receive/unpack encapsulation

In keeping with the best practices for safe pipe communications, the following four (package private) procedures are implemented in the body of pipesvr (only the specifications are shown below):

  /* Filename on companion disk: pipesvr.sql */*   /*   || private program to put service request on pipe,   || called by client programs   */   PROCEDURE 

pack_send_request 	(request_rec_IN IN request_rectype     	 ,return_code_OUT OUT NUMBER);    /*   || private program to receive request on the    || request pipe   */   PROCEDURE 

receive_unpack_request 	(timeout_IN IN INTEGER      	,request_rec_OUT OUT request_rectype      	,return_code_OUT OUT NUMBER);    /*   || private program to put request on pipe,   || called by client programs   */   PROCEDURE 

pack_send_status      	(status_rec_IN IN status_rectype      	,response_pipe_IN IN my_response_pipe%TYPE      	,return_code_OUT OUT NUMBER);    /*   || private program to receive status on unique   || session pipe   */   PROCEDURE receive_unpack_status 	(timeout_IN IN INTEGER 	,status_rec_OUT OUT status_rectype 	,return_code_OUT OUT NUMBER);

3.1.7.5.4 The server procedure

The server procedure itself is quite straightforward. It begins by creating the request pipe and initializing its private status record. Then it loops forever (or until the terminate_TF boolean is TRUE) on request_pipe for client requests using receive_unpack_request. Valid requests are passed on to the process_request procedure, which encapsulates the inelegant IF...THEN logic required to handle various types of requests. Finally, when the loop terminates, due to setting terminate_TF to TRUE, the pipe is removed and the program ends.

The code for the server is surprisingly simple.

/* Filename on companion disk: pipesvr.sql */* PROCEDURE 

server  IS 	request_rec  request_rectype; 	temp_return_code  NUMBER;  BEGIN 	/* create pipe */ 	temp_return_code := DBMS_PIPE.CREATE_PIPE(request_pipe);  	/* initialize status rec */ 	status_rec.start_date := SYSDATE; 	status_rec.total_requests := 0; 	status_rec.debug_status := 'OFF';  	/*  	|| loop forever and process requests  	*/ 	WHILE NOT terminate_TF  	LOOP 		receive_unpack_request 			(timeout_IN => DBMS_PIPE.maxwait 			,request_rec_OUT=> request_rec 			,return_code_OUT => temp_return_code);  		IF temp_return_code != 0 		THEN 			DBMS_PIPE.PURGE(request_pipe); 			debug('REQUEST PIPE STAT: '||temp_return_code); 		ELSE 			process_request(request_rec); 			debug('REQUEST PROCESSED'); 		END IF; 	END LOOP;  	/*  	|| terminating: remove pipe and exit  	*/ 	temp_return_code := DBMS_PIPE.REMOVE_PIPE(request_pipe);  EXCEPTION 	WHEN OTHERS THEN 		debug('SERVER EXCP: '||SQLERRM, force_TF_IN=>TRUE); 		temp_return_code := DBMS_PIPE.REMOVE_PIPE(request_pipe);  END server;

3.1.7.5.5 The process_request procedure

When the server procedure receives a valid service request, it calls the process_request procedure. This procedure has the responsibility of interpreting the service request and performing the requested action. Note that this procedure sets the terminate_TF Boolean, which stops the server. You must always code a stop routine into this type of service program, or you will have to kill the process running the procedure. Other services performed by process_request include setting debugging to on or off, and sending the server's current status_rec back to the requesting session on a database pipe using pack_send_status.

/* Filename on companion disk: pipesvr.sql */* /* || private program to encapsulate request processing || logic (lots of IF...THEN stuff) of server */ PROCEDURE 

process_request 	(request_rec_IN IN request_rectype) IS 	temp_return_code NUMBER; BEGIN 	/* increment total */ 	status_rec.total_requests := status_rec.total_requests +1;  	/* stop the server, this is a MUST have */ 	IF request_rec_IN.service = stop_req 	THEN  		terminate_TF := TRUE;  	ELSIF request_rec_IN.service = debugon_req 	THEN 		debug_TF := TRUE; 		status_rec.debug_status := 'ON';  	ELSIF request_rec_IN.service = debugoff_req 	THEN 		debug_TF := FALSE; 		status_rec.debug_status := 'OFF';  	ELSIF request_rec_IN.service = status_req 	THEN 		pack_send_status 			(status_rec_IN=>status_rec 			,response_pipe_IN=> request_rec_IN.response_pipe 			,return_code_OUT=> temp_return_code); 		debug('SEND STATUS: '||temp_return_code); 	/* unrecognized request */  	ELSE 		DBMS_PIPE.RESET_BUFFER; 		debug('UNKNOWN REQUEST: '||request_rec_IN.service); 	END IF;  END process_request;

3.1.7.5.6 Debug procedure

Notice that the server procedure makes several calls to a procedure called debug. The debug procedure dumps informational messages into a simple table to allow tracking of server-side events. This procedure usually inserts data to the table only if the server is in debug mode, as determined by the debug_TF global variable. This prevents too many debugging rows from being inserted when the server is operating normally. The debug procedure can be forced to write to the table by setting a parameter called force_TF_IN to TRUE. This is used to guarantee that certain debugging information gets into the table regardless of the server's current debug mode. Debugging messages are usually forced in exception handlers, as in the server procedure. Here is the source code for debug:

/* Filename on companion disk: 

pipesvr.sql */* /*  || private program to put debug messages into table || if boolean is TRUE, or if force_IN is TRUE || NOTE: commits after inserting row */ PROCEDURE 

debug 	(message_IN IN VARCHAR2 	,force_TF_IN IN BOOLEAN := FALSE) IS BEGIN 	IF debug_TF OR force_TF_IN 	THEN 		INSERT INTO pipesvr_debug 		VALUES (SYSDATE, message_IN); 		COMMIT; 	END IF; END debug;

3.1.7.5.7 Client side procedures

The four client-side procedures all have to send specific request records to the server, and the request records they use vary only in the contents of the service field. I reduced code redundancy by creating the client_request procedure as follows:

/* Filename on companion disk: 

pipesvr.sql. */* /* || private program to make simple service requests, || if request_rectype gets more complex or need more || flexibility use pack_send_request instead   */ PROCEDURE client_request(request_IN IN stop_req%TYPE) IS  	request_rec request_rectype; 	temp_return_code  NUMBER;  BEGIN 	request_rec.service := request_IN;  	pack_send_request 		(request_rec_IN => request_rec 		,return_code_OUT => temp_return_code);  END client_request;

The client_request procedure loads the service field of a request record and then calls pack_send_request. This procedure helps simplify client programs, as seen in server_stop:

PROCEDURE server_stop IS BEGIN 	client_request(stop_req); END server_stop;

The server_status procedure calls client_request and then waits for up to a minute on the receive_unpack_status procedure. If a status record is successfully received from the server, it is displayed using the DBMS_OUTPUT package.

I hope the pipesvr package will serve as a useful template to those seeking to implement PL/SQL service programs. It works and it incorporates the best practices for using DBMS_PIPE, so it should be a good starting point.