Oracle PL/SQL Best Practices

Name

BIP-08: Provide explicit and appropriate timeout values when you send and receive messages.

Synopsis

When you send or receive a message via a database pipe, you can specify how long you are willing to wait for the operation to succeed. A pipe might be full, which means that you can’t immediately send to that pipe. A pipe might be empty, which means that you can’t immediately receive a message from that pipe.

The default wait time for DBMS_PIIPE is 86.4 million seconds, otherwise known as 1,000 days. This is an awfully long time wait for an operation to complete, and could cause problems in your application. You should never rely on the default timeout values in any DBMS_PIPE calls. Always provide an override.

Example

Here’s the implementation of the pe_book.send procedure:

PROCEDURE pe_book.receive (
   isbn_out             OUT      book.isbn%TYPE,
   title_out            OUT      book.title%TYPE,
   summary_out          OUT      book.summary%TYPE,
   author_out           OUT      book.author%TYPE,
   date_published_out   OUT      book.date_published%TYPE,
   page_count_out       OUT      book.page_count%TYPE,
   wait                         IN       INTEGER := 0
)
IS
BEGIN
   -- Receive next message and unpack for each column. 
   g_status := DBMS_PIPE.receive_message (defname, wait);

   IF g_status = 0
   THEN
      DBMS_PIPE.unpack_message (isbn_out);
      DBMS_PIPE.unpack_message (title_out);
      DBMS_PIPE.unpack_message (summary_out);
      DBMS_PIPE.unpack_message (author_out);
      DBMS_PIPE.unpack_message (date_published_out);
      DBMS_PIPE.unpack_message (page_count_out);
   END IF;

   g_action := 'RECEIVE_MESSAGE';
END;

In this case, I always override ...