Oracle PL/SQL Best Practices

Name

BIP-09: Use RESET_BUFFER in exception handlers and before you pack data into the message buffer.

Synopsis

Each session connected to Oracle has a message buffer that can contain up to 4096 bytes of information. You can place data into the buffer with calls to DBMS_PIPE.PACK_MESSAGE and DBMS_PIPE.RECEIVE_MESSAGE.

Prior to packing data into the buffer, you should not assume it’s empty. Your last unpack operation might have left some data in the buffer, or a previous pack-and-send operation could have failed with an exception. For these reasons, you should call DBMS_PIPE.RESET_BUFFER both before you pack data into the message buffer and in exception handlers in blocks where the buffer may have been partially filled.

Example

The pipe encapsulation package for the book table, pe_book, offers this implementation of the send operation:

PROCEDURE pe_book.send ( isbn_in IN book.isbn%TYPE DEFAULT NULL, title_in IN book.title%TYPE DEFAULT NULL, summary_in IN book.summary%TYPE DEFAULT NULL, author_in IN book.author%TYPE DEFAULT NULL, date_published_in IN book.date_published%TYPE DEFAULT NULL, page_count_in IN book.page_count%TYPE DEFAULT NULL, wait IN INTEGER := 0 ) IS BEGIN -- Clear the buffer before writing. DBMS_PIPE.reset_buffer; -- For each column, pack item into buffer. DBMS_PIPE.pack_message (isbn_in); DBMS_PIPE.pack_message (title_in); DBMS_PIPE.pack_message (summary_in); DBMS_PIPE.pack_message (author_in); DBMS_PIPE.pack_message (date_published_in); DBMS_PIPE.pack_message (page_count_in); ...