Oracle PL/SQL Best Practices

Name

PKG-04: Implement flexible, user-adjustable functionality using package state toggles and related techniques.

Synopsis

As you rely more and more on packages to offer functionality to programmers on your team or in your company, you want to design those packages to be flexible and responsive to varying user needs.

You certainly don’t want programmers going into the package bodies and changing implementations. You also don’t want them making copies of your code and then producing their own variations.

Instead, add programs to the package specification that allow developers to modify (within certain accepted pathways) the behavior of your package to fit their varying requirements. These programs might turn on/off certain features (“toggles”) or might set internal package values.

The most important feature of these programs is that they allow the package to change its behavior without having to change the source code.

Example

I have decided to build a check-out package that allows librarians to check books out of their collection. The default rule is that a person can have a maximum of 10 books checked out at any time. I can write my package to hard-code that rule as follows:

CREATE OR REPLACE PACKAGE BODY checkout_pkg IS c_max_allowed CONSTANT PLS_INTEGER := 10; FUNCTION can_check_out ( borrower_id IN borrower.id%TYPE, isbn_in IN book.isn%TYPE) RETURN BOOLEAN IS l_checked_out PLS_INTEGER; l_book_is_available BOOLEAN; BEGIN l_checked_out := checked_out_count (borrower_id); l_book_is_available ...