Jan
14
Written by:
StevenFeuersteinTW
Monday, January 14, 2013 10:04 AM
| We at Toad World are very pleased to offer excerpts from Steven Feuerstein's Oracle PL/SQL Best Practices Second Edition in this blog. Many thanks to O'Reilly Media for giving permission to publish the excerpts. Here's a description of the Oracle PL/SQL Best Practices: "In this compact book, Steven Feuerstein, widely recognized as one of the world's leading experts on the Oracle PL/SQL language, distills his many years of programming, teaching, and writing about PL/SQL into a set of best practices-recommendations for developing successful applications. Covering the latest Oracle release, Oracle Database 11g Release 2, Feuerstein has rewritten this new edition in the style of his best-selling Oracle PL/SQL Programming. The text is organized in a problem/solution format, and chronicles the programming exploits of developers at a mythical company called My Flimsy Excuse, Inc., as they write code, make mistakes, and learn from those mistakes-and each other." |
In one important respect, writing software is no different from driving a car. You can be the best driver in the world, but if the other driver is awful, you can still have a big problem on your hands. So you drive defensively; you stay alert to the possibility that another driver might make a poor decision or be careless. You protect yourself as best you can.
It’s the same with software. You believe that you are a fine programmer (and I have no reason to doubt that!), and that your programs work well. But have you guarded your programs against people who might use them carelessly or in ignorance? Probably not.
This post shows how you can use assertion routines to make sure that a person running your program does not violate any assumptions of that program.
Problem: Delaware expects everyone to be a know-it-all like him.
Delaware attended a course on collections—array-like structures in PL/SQL. He is very excited about what he can do with collections—particularly using some of the most interesting features, like string indexing of collections.
Sunita asks him to build a utility to keep track of alternative names of flimsy excuses suggested by users. If a user tries one name, the myflimsyexcuse.com web site should be able to tell him: "No, you already tried that one. Think of another!" These tried-and-discarded alternatives are not stored in a database table; they are relevant only in this one session.
Those characteristics immediately get Delaware thinking about package-level variables, which persist for an entire session. He first constructs the specification of his excuse tracker package as follows:
PACKAGE excuse_tracker
IS
SUBTYPE excuse_excuse_t IS VARCHAR2 (32767);
PROCEDURE clear_used_list;
FUNCTION excuse_in_use (excuse_in IN excuse_excuse_t)
RETURN BOOLEAN;
PROCEDURE mark_as_used (excuse_in IN excuse_excuse_t);
END excuse_tracker;
Now that he has defined the public API (what the package needs to be able to do), he shifts focus to the package body. Relying on a string-indexed collection of Booleans, he can quickly construct his utility:
CREATE OR REPLACE PACKAGE BODY excuse_tracker
IS
TYPE used_aat IS TABLE OF BOOLEAN INDEX BY excuse_excuse_t;
g_excuses_used used_aat;
PROCEDURE clear_used_list
IS
BEGIN
g_excuses_used.DELETE;
END clear_used_list;
FUNCTION excuse_in_use (excuse_in IN excuse_excuse_t) RETURN BOOLEAN
IS
BEGIN
RETURN g_excuses_used.EXISTS (excuse_in);
EN Dexcuse_in_use;
PROCEDURE mark_as_used (excuse_in IN excuse_excuse_t)
IS
BEGIN
g_excuses_used (excuse_in) := TRUE;
END mark_as_used;
END excuse_tracker;
What could be simpler? What could be easier to use? To mark an alternative name for a flimsy excuse as used, the programmer needs do nothing more than this:
excuse_tracker.mark_as_used ('My tummy was making loud noises.');
and then later on in the program, one can check to see if that excuse has already been used:
IF excuse_tracker.string_is_used ('My tummy was making loud noises.') THEN ...
Delaware spreads the word of his utility to the team, and the very next week, Jasper realizes that this package will keep track of all kinds of strings, not just excuses. And that is just what he needs in his part of the application. So he writes the following program:
PROCEDURE mark_names_used (names_list_in in DBMS_SQL.VARCHAR2A)
/* Note: VARCHAR2A is a collection type of strings
defined in the DBMS_SQL package. */
IS
BEGIN
/* Mark all names in list as used. */
FOR l_index IN names_list_in.FIRST .. names_list_in.LAST
LOOP
excuse_tracker.mark_as_used (names_list_in (l_index));
END LOOP;
END mark_names_used;
He then writes a program that builds the list of names and passes it to mark_names_used; here is a very simplified version of that program:
DECLARE
l_names DBMS_SQL.varchar2a;
BEGIN
l_names (1) := 'abc;';
l_names (2) := my_package.name_from_somewhere_else();
mark_names_used (l_names);
... and then lots more code
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;
Unfortunately, when he runs this block, he sees the following error:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
Not having worked with collections much himself, Jasper is stumped and frustrated. What is an "index table key value"? Why is this happening? Is there something wrong with Jasper's code? Is it a bug in the excuse_tracker package? Delaware's a smart guy, so Jasper figures it must be in his code. He is embarrassed to ask Delaware about this, and he soon just gives up on using excuse_tracker.
Solution: Assume nothing! Make explicit and then validate all assumptions.
Jasper didn't do anything wrong...well, not directly, anyway. It turns out that the "name from somewhere else" function happened to return a NULL value. When he passed that NULL value to excuse_tracker, it tried to use it as an index value in the collection. NULLs can never be used in this way, however, so the Oracle database raised an exception.
So, yes, Jasper should know that. And he should make sure that all of his names are not NULL and...but wait a minute. Do these "shoulds" really fall on Jasper's shoulders? I think not. Instead, Delaware, the maker of the generic utility, should have stepped back from his code and asked himself:
· What am I assuming about the way this code will be used?
· What could go wrong when someone calls my programs?
· What errors can I anticipate and therefore build in some additional checking for?
Delaware should have realized that someone unfamiliar with collections might pass in a NULL value and think that was perfectly fine. In fact, it could be a perfectly fine value that they want to track. Well, with Delaware's implementation, that just isn't allowed.
Here's what Delaware should have done:
· Add an assertion program to his package as follows:
PACKAGE BODY excuse_tracker
IS
PROCEDURE assert (expr_in IN BOOLEAN, text_in IN VARCHAR2)
IS
BEGIN
IF NOT expr_in OR expr_in IS NULL
THEN
RAISE_APPLICATION_ERROR (-20000, 'ASSERTION FAILURE! ' || text_in);
END IF;
END assert;
It is a very simple program; you pass it a Boolean condition that you want to assert it true. If it evaluates to FALSE or to NULL, then the assertion routine raises an exception, stopping the program, and passes back as the error message the text provided in the call to the assertion program.
· Call the assertion routine in each of the two main programs:
FUNCTION excuse_in_use (excuse_in IN excuse_excuse_t) RETURN BOOLEAN
IS
BEGIN
assert (excuse_in IS NOT NULL
, 'You must provide a non-NULL string to check in use!' );
RETURN g_excuses_used.EXISTS (excuse_in);
END excuse_in_use;
PROCEDURE mark_as_used (excuse_in IN excuse_excuse_t)
IS
BEGIN
assert (excuse_in IS NOT NULL
, 'You must provide a non-NULL string to mark as used!' );
g_excuses_used (excuse_in) := TRUE;
END mark_as_used;
If Delaware had done this, then when Jasper ran his program, he would have seen this error message:
ORA-20000: ASSERTION FAILURE! You must provide a non-NULL string to mark as used!
Now the mystery is gone; the user (Jasper) is told explicitly the cause of the problem and implicitly how to fix it. Jasper can go back through his code and figure out where the NULL is coming from.
To summarize:
-
Every time you write a program, you make certain assumptions: Some of these assumptions can be embedded so deeply in the architecture of the application that you don't even realize you have made the assumption.
-
A user of your program doesn’t necessarily know about those assumptions: If you don’t “code defensively” and make sure that your assumptions aren’t violated, your programs can break down in unpredictable ways.
-
Use assertion routines to make it as easy as possible to validate assumptions in a declarative fashion: These routines, standardized for an entire application, take care of all the housekeeping: what to do when a condition fails, how to report the problem, and whether and how to stop the program from continuing.
Resources
The assert.pkg package contains a generic assertion package that you can use to test any of the following conditions:
-
Is it NULL or is it NOT NULL?
-
Is the expression TRUE or FALSE?
-
Does the date fall within a specified range?