Best Practices for PL/SQL Variables - Best Practices for Declaring and Using Package Variables
(Page 4 of 5 )
Use the best practices described in this section when you are declaring variables for use in packages.
Danger, Will Robinson! Globals in use!
Use package globals sparingly and only in package bodies.
Problem: Jasper needs Lizbeth’s program data. Delaware needs Jasper’s program data.
Everyone is working very hard in the final week or two of the coding cycle. Packages are constructed quickly, and everyone needs to reference “stuff” in everyone else’s packages. Here’s one example:
Lizbeth creates a package named mfe_reports to consolidate all reporting-related functionality, including a function that returns the standard header for a report. This header is composed of a top line, a bottom line, and a report-specific string sandwiched in between. Here is Lizbeth’s package specification:
PACKAGE mfe_reports
IS
topline VARCHAR2(100) := 'My Flimsy Excuse – We Report for You!!!!!!';
bottomline VARCHAR2(100) := '== Report Generation Engine Version 4.3 ==';
...
END mfe_reports;
Jasper builds a report in his mfe_acceptability package. He doesn’t like the exclamation marks at the end of the top line, but he can’t get Lizbeth to change it (“Sorry, Jasper, that’s the standard.”). He also thinks putting “=” at the beginning and end of the bottom line is dumb. So he writes the following code:
PACKAGE BODY mfe_acceptability
IS
PROCEDURE acceptance_report IS
c_carriage_return CONSTANT VARCHAR2(1) := CHR(10);
BEGIN
mfe_reports.topline := replace (mfe_reports.topline, '!', '');
mfe_reports.topline := replace (mfe_reports.bottomline, '=', '');
DBMS_OUTPUT.PUT_LINE (
mfe_reports.topline || c_carriage_return ||
'Acceptance Report' || c_carriage_return ||
mfe_reports.bottomline);
... rest of report logic ...
END acceptance_report;
And now Jasper can create the report just the way he likes it.
Unfortunately for Jasper, the users are dismayed. They like the exclamation marks and the equals signs. They asked for those characters. And so they complain to Sunita, and Jasper gets in trouble. Jasper’s response? “If you didn’t want me to change it, why didn’t you stop me from changing it?”
A very good question.
Solution: Don’t expose program data in package specifications, letting everyone see and change it.
Lizbeth realizes now that she should have ensured that neither Jasper nor any other developer could change the elements of the header. At first, she thinks to herself: “Well, fine. I will make the variables constants and then Jasper will not be able to change the values.”
PACKAGE mfe_reports
IS
topline CONSTANT VARCHAR2(100) :=
'My Flimsy Excuse – We Report for You!!!!!!';
bottomline CONSTANT VARCHAR2(100) :=
'== Report Generation Engine Version 4.3 ==';
END mfe_reports;
This is true. Now Jasper has no choice: he will have to accept the top and bottom lines and use them as is. Otherwise, he will get this error:
PLS-00363: expression 'MFE_REPORTS.TOPLINE' cannot be used as an assignment
target
Looking at Jasper’s code, though, Lizbeth realizes that she needs to do more. If she is truly supposed to help people produce standard headers, it doesn’t make any sense for them to be concatenating the various pieces together with line breaks.
So she decides to take things a step further and build a function that does all the work for the user of the mfe_reports package:
PACKAGE mfe_reports
IS
FUNCTION standard_header (text_in in VARCHAR2) RETURN VARCHAR2;
END mfe_reports;
PACKAGE BODY mfe_reports
IS
FUNCTION standard_header (text_in in VARCHAR2) RETURN VARCHAR2
IS
c_carriage_return CONSTANT VARCHAR2(1) := CHR(10);
c_topline CONSTANT VARCHAR2(100) :=
'My Flimsy Excuse – We Report for You!!!!!!';
c_bottomline CONSTANT VARCHAR2(100) :=
'== Report Generation Engine Version 4.3 ==';
BEGIN
RETURN c_topline
|| c_carriage_return
|| text_in
|| c_carriage_return
|| c_bottomline;
END standard_header;
END mfe_report
With this function in place, Jasper can add the header to his report with nothing more than this:
PACKAGE BODY mfe_acceptability
IS
PROCEDURE acceptance_report IS
BEGIN
DBMS_OUTPUT.PUT_LINE (
mfe_reports.standard_header ('Acceptance Report'));
... rest of report logic ...
END acceptance_report;
Sure, he might still gripe a bit about the lack of control over the report header, but at least now he doesn’t have to write nearly as much code. Instead, the central report package does most of the work for him.
Jasper’s direct references (and changes) to the package variables in mfe_reports demonstrated some of the problems associated with global variables. A global variable is a data structure that can be referenced outside the scope or block in which it is declared. A variable declared at the package level (outside any
individual procedure or function in that package) is global at one of two levels:
- If the variable is declared in the package body, then it is globally accessible to all programs defined within that package.
- If the variable is declared in the package specified, then it is accessible to (and directly referenceable by) any program executed from a schema that has EXECUTE authority on that package.
Globals can also be defined in any PL/SQL block. In the following block, for example, the l_publish_date is global to the local display_book_info procedure:
DECLARE
l_publish_date DATE;
...
PROCEDURE display_book_info IS
BEGIN
DBMS_OUTPUT.PUT_LINE (l_publish_date);
END;
Globals are dangerous and should be avoided, because they create hidden “dependencies” or side effects. A global doesn’t have to be passed through the parameter list, so it’s hard for you to even know that a global is referenced in a program without looking at the implementation.
Furthermore, if that global is a variable (not a constant) and is declared in the package specification, then you have in effect lost control of your data. You cannot guarantee the integrity of its value, since any program run from a schema that has EXECUTE authority on the package can change the package however the developer of that program desires.
You can avoid using globals, and uncontrolled modifications to globals, in a number of ways:
Pass the global as a parameter in your procedures and functions
Don’t reference it directly within the program
(circumventing the structure and visibility of the
parameter list).
Declare variables, cursors, functions, and other objects as “deeply” as possible
That would be in the block nearest to where, or
within which, that object will be used). Doing this will
reduce the chance of unintended use by other
sections of the code.
Hide your package data behind “gets and sets”
These are subprograms that control access to the
data. This approach is covered in the next best
practice.
Scope declarations as locally as possible
If your variable is used only in a single subprogram,
declare it there. If it needs to be shared among
multiple programs in a package body, declare it at the
package level (but never put the declaration in the
package specification).
Next: Packages should have a strong sense of personal space >>
More Database Articles Articles
More By O'Reilly Media
|
 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.
|
|
/ 2 
