Using Standardized Error Handler Programs
Robust and consistent error handling is an absolutely crucial element of a properly constructed application. This consistency is important for two very different audiences: the user and the developer. If the user is presented with easy-to-understand, well-formatted information when an error occurs, she will be able to report that error more effectively to the support team and will feel more comfortable using the application. If the application handles and logs errors in the same way throughout the entire application, the support and maintenance programmers will be able to fix and enhance the code much more easily.
Sounds like a sensible approach, doesn't it? Unfortunately, and especially in development teams of more than a handful of people, the end result of exception handling is usually very different from what I just described. A more common practice is that each developer strikes out on his or her own path, following different principles, writing to different kinds of logs, and so on. Without standardization, debugging and maintenance becomes a nightmare. Here's an example of the kind of code that typically results:
At first glance, this code might seem quite sensible, and can in fact be explained as follows:
If I don't find a company for this ID, grab the SQLCODE value, set the program name and message, and write a row to the log table. Then allow the enclosing block to continue (it's not a very severe error in this case). If any other error occurs, grab the error code and message, set the program name, write a row to the log table, and then propagate out the same exception, causing the enclosing block to stop (I don't know how severe the error is).
So what's wrong with all that? The mere fact that I can actually explain everything that is going on is an indication of the problem. I have exposed and hardcoded all the steps I take to get the job done. The result is that (a) I write a lot of code, and (b) if anything changes, I have to change a lot of code. Just to give you one example, notice that I am writing to a database table for my log. This means that the log entry has become a part of my logical transaction. If I need to roll back that transaction, I lose my error log.
There are several ways to correct this problem (e.g., write to a file, or use autonomous transactions to save my error log without affecting my main transaction). The problem is that, with the way I have written my code above, I have to apply my correction in potentially hundreds of different programs.
Now consider a rewrite of this same exception section using a standardized package:
EXCEPTION WHEN NO_DATA_FOUND THEN errpkg.record_and_continue ( SQLCODE, 'No company for id ' || TO_CHAR (v_id)); WHEN OTHERS THEN errpkg.record_and_stop; END;My error-handling package hides all the implementation details; I simply decide which of the handler procedures I want to use by viewing the specification of the package. If I want to record the error and then continue, I call the record_and_continue program. If I want to record and then stop, clearly I want to use the record_and_stop program. How does it record the error? How does it stop the enclosing block (i.e., how does it propagate the exception)? I don't know and I don't care. Whatever it does, it does it according to the standards defined for my application.
All I know is that I can now spend more time building the interesting elements of my application, rather than worrying over the tedious, low-level administrivia.
The errpkg.pkg file available on the O'Reilly site contains a prototype of such a standardized error-handling package. You will need to complete its implementation before putting it to use in your application, but it will give you a very clear sense of how to construct such a utility. You might also check out the plvexc package of the PL/Vision library, a collection of 60+ packages that are a part of Quest Software's PL/SQL Knowledge Expert. The plvexc package provides a complete, working implementation of a generic, reusable error-handling infrastructure component.
Part III: PL/SQL Program Data
Just about every program you write will manipulate data—and much of that data is "local" (i.e., defined in) your PL/SQL procedure or function. This part of the book concentrates exhaustively on the various types of program data you can define in PL/SQL, such as numbers, strings, records, and collections. You will learn about the new datatypes introduced in Oracle9i, such as INTERVAL, TIMESTAMP, XMLType, and others. Chapters 7 through 12 also cover the various built-in functions provided by Oracle that allow you to manipulate and modify data.
Chapter 7
Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 7. Working with Program Data
Almost every PL/SQL block you write will define and manipulate program data. Program data consists of data structures that exist only within your PL/SQL session (physically, within the Program Global Area, or PGA, for your session); they are not stored in the database. Program data can be:
Variables or constants
The values of variables can change during a program's execution. The values of constants are static once they are set at the time of declaration.
Scalar or composite
Scalars are made up of a single value, such as a number or a string. Composite data consists of multiple values, such as a record, a collection, or an object type instance.
Containers
Containers may contain information obtained from the database, or data that was never in the database and might not ever end up there.
Before you can work with program data inside your PL/SQL code, you must declare those data structures, giving them names and datatypes.
This chapter describes how you declare program data. It covers the rules governing the format of the names you give them. It offers a quick reference to all the different types of data supported in PL/SQL and explores the concept of datatype conversion. The chapter finishes with some recommendations for working with program data. The remaining chapters in this part of the book describe specific types of program data.
7.1 Naming Your Program Data
To work with a variable or a constant you must first declare it, and when you declare it you give it a name. Here are the rules that PL/SQL insists you follow when naming your data structures (these are the same rules applied to names of database objects, such as tables and columns):
· Can be up to 30 characters in length
· Must start with a letter
· Can then be composed of any of the following: letters, numerals, $, #, and _
· All names are case-insensitive (unless those names are placed within double quotes).
Given these rules, the following names are valid:
l_total_countfirst_12_yearstotal_#_of_treessalary_in_$These next three names are not only valid, but considered identical by PL/SQL because it is not a case-sensitive language:
diapers_changed_week1DIAPERS_CHANGED_WEEK1Diapers_Changed_Week1The next three names are invalid, for the reasons indicated:
1st_account—Starts with a number instead of a letterfavorite_ice_cream_flavors_that_dont_contain_nuts—Too longemail_address@business_loc—Contains invalid character (@)There are some exceptions to these rules (why are we not surprised?). If you embed a name within double quotes when you declare it, you can bypass all the above rules except the maximum length of 30 characters. For example, all of the following declarations are valid:
DECLARE "truly_lower_case" INTEGER; " " DATE; -- Yes, a name consisting of five spaces! "123_go!" VARCHAR2(10); BEGIN "123_go!" := 'Steven';END;Note that when you reference these strange names in your execution section, you will need to do so within double quotes, as shown. Otherwise, your code will not compile.
Why would you use double quotes? There is little reason to do so in PL/SQL programs. It is a technique sometimes employed when creating database objects because it preserves case-sensitivity (in other words, if I CREATE TABLE "docs", then the name of the table is docs and not DOCS), but in general, you should avoid using double quotes in PL/SQL.
Another exception to these naming conventions has to do with the names of Java objects, which can be up to 4K in length. See Chapter 22 for more details about this variation and what it means for PL/SQL developers.
Here are two key recommendations for naming your variables, constants, and types:
Make sure the name accurately reflects its usage and is understandable "at a glance"
You might even take a moment to write down—in noncomputer terms—what the variable represents. You can then easily extract an appropriate name from this statement. For example, if a variable represents the "total number of calls made about lukewarm coffee," a good name for that variable might be total_calls_on_cold_coffee, or tot_cold_calls, if you are allergic to five-word variable names. A bad name for that variable would be totcoffee, or t_#_calls_lwcoff, both too cryptic to get the point across.
Establish consistent, sensible naming conventions
Such conventions usually involve the use of prefixes and/or suffixes to indicate type and usage. For example, all local variables should be prefixed with "l_" while global variables defined in packages have a "g_" prefix. All record types should have a suffix of "_rt", and so on. You can download a comprehensive set of naming conventions from O'Reilly's Oracle page at http://oracle.oreilly.com. Click on "Oracle PL/SQL Best Practices," then "Examples." The download contains a standards document for your use. (The direct URL is http://examples.oreilly.com/orbestprac/.)
7.2 Overview of PL/SQL Datatypes
Whenever you declare a variable or a constant, you must assign it a datatype. (PL/SQL is, with very few exceptions, a "strongly typed" language; see the sidebar for a definition.) PL/SQL offers a comprehensive set of predefined scalar and composite datatypes, and starting with Oracle8 you can create your ownuser-defined types (also known as abstract datatypes).
| What Does "Strongly Typed" Mean? "A strongly typed programming language is one in which each type of data (such as integer, character, hexadecimal, packed decimal, and so forth) is predefined as part of the programming language, and all constants or variables defined for a given program must be described with one of the data types. Certain operations may be allowable only with certain data types. The language compiler enforces the data typing and use compliance. An advantage of strong data typing is that it imposes a rigorous set of rules on a programmer and thus guarantees a certain consistency of results. A disadvantage is that it prevents the programmer from inventing a data type not anticipated by the developers of the programming language and it limits how "creative" one can be in using a given data type." (Definition courtesy of www.whatis.com) |
Virtually all of these predefined datatypes are defined in the PL/SQL STANDARD package. Here, for example, are the statements that define the Boolean datatype and two of the numeric datatypes:
create or replace package STANDARD is type BOOLEAN is (FALSE, TRUE); type NUMBER is NUMBER_BASE; subtype INTEGER is NUMBER(38,);When it comes to datatypes, PL/SQL supports the "usual suspects" and a whole lot more. This section provides a quick overview of the various predefined datatypes. They are covered in detail in Chapter 8 through Chapter 12, Chapter 14, and Chapter 21; you will find detailed references to specific chapters in the following sections.