Best Practices for PL/SQL Variables - Packages should have a strong sense of personal space
(Page 5 of 5 )
Control access to package data with “get and set” modules.
Problem: Data structures declared in a package specification may end up bypassing business rules.
Data structures (scalar variables, collections, cursors) declared in the package specification (not within any specific program) are able to be referenced directly from any program run from a session with EXECUTE authority on the package. This is almost always a bad idea and should be avoided.
Solution: Declare data in the package body, and hide the data structures via functions in the package specification.
Instead, declare all package-level data in the package body and provide “get and set” programs—a function to GET the value and a procedure to SET the value—in the package specification. Developers can then access the data through these programs, and automatically follow whatever rules you establish for manipulating that data.
Suppose that I’ve created a package to calculate fines for overdue books. The fine is, by default, $.10 per day, but it can be changed according to this rule: the fine can never be less than $.05 or more than $.25 per day. Here’s my first version:
PACKAGE overdue_pkg
IS
g_daily_fine NUMBER DEFAULT .10;
FUNCTION days_overdue (isbn_in IN book.isbn%TYPE)
RETURN INTEGER;
-- Relies on g_daily_fine for calculation
FUNCTION fine (isbn_in IN book.isbn%TYPE)
RETURN INTEGER;
END overdue_pkg;
You can easily see the problem with this package in the following block:
BEGIN
overdue_pkg.g_daily_fine := .50;
DBMS_OUTPUT.PUT_LINE ('Your overdue fine is ' ||
overdue_pkg.fine (' 1-56592-375-8'));
END;
Here I bypassed the business rule and applied a daily fine of $.50! By “publishing” the daily fine variable, I lost control of my data structure and the ability to enforce my business rules.
The following rewrite of overdue_pkg (available on the book’s web site) fixes the problem; for the sake of the trees, I show only the replacement of the g_daily_fine variable with its “get and set” programs:
PACKAGE overdue_pkg
IS
PROCEDURE set_daily_fine (fine_in IN NUMBER);
FUNCTION daily_fine RETURN NUMBER;
and the implementation:
PACKAGE BODY overdue_pkg
IS
g_daily_fine NUMBER DEFAULT .10;
PROCEDURE set_daily_fine (fine_in IN NUMBER)IS
BEGIN
g_daily_fine :=
GREATEST (LEAST (fine_in, .25), .05);
END;
FUNCTION daily_fine RETURN NUMBER IS
BEGIN
RETURN g_daily_fine;
END;
Now it’s impossible to bypass the business rule for the daily fine.
In this particular example, by the way, you will be even better off if you put your maximum and minimum fine information in a table. You could then use the package initialization section to load these limits into package data structures. This way, if (more likely when) the data points change, you won’t have to change the program itself, just some rows and columns in a table.
The only way to change a value is through the set procedure. The values of your data structures are protected; business rules can be enforced without exception.
By hiding the data structure, you also give yourself the freedom to change how that data is defined without affecting all accesses to the data.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter four of the book Oracle PL/SQL Best Practices, Second Edition, written by Steven Feuerstein (O'Reilly, 2007; ISBN: 0596514107). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 4 
