Oracle PL/SQL Best Practices

Name

BIP-07: Encapsulate interaction with specific pipes.

Synopsis

A pipe is identified by its name: a string of up to 128 characters. Messages that are written to, and read from, a pipe can be composed of one or more “packets,” and each message can be made up of different numbers and types of packets (for example, two strings and a number or 12 dates). Working with a pipe can raise both pipe-specific and general errors.

For all these reasons, whenever you are working with a database pipe (or pipes), you should encapsulate access to that pipe behind a package interface. Make sure that no user of a pipe hard-codes the name of the pipe in his logic. Avoid the explicit packing and unpacking of message contents; instead, call procedures to do that work for you and for any other developer.

Example

Here’s an example of a pipe encapsulation package around the book table:

CREATE OR REPLACE PACKAGE pe_book -- Wrapper around pipe based on book -- NOTE: EXECUTE authority on DBMS_LOCK is required. -- Issue this command from SYS: -- GRANT EXECUTE ON DBMS_LOCK TO PUBLIC; IS c_name CONSTANT VARCHAR2 (200) := 'BOOK_pipe'; /* Overloadings of send */ PROCEDURE 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 ); PROCEDURE send (rec_in IN book%ROWTYPE, ...