PDA

View Full Version : Self-Managing PL/SQL



nightstars
30-11-2004, 10:44
By Steven Feuerstein

Follow self-managing databases with self-managing PL/SQL.

Oracle has introduced significant management automation features in its flagship database product and has described the new Oracle Database 10g release as "the self-managing database." Given that achievement and direction, I thought it would make sense to talk about how to write one's PL/SQL application so that it is also self-managing.

What, you might ask, would it mean for an application to be self-managing? Let's dream for a moment: I have built an application composed of 50 tables and 200 programs. It is running in production, but the users have a request for an enhancement. This enhancement requires a new column in table A and changes in 6 of the 200 programs. Yet 55 of the 200 programs reference table A.

When I add the column to table A, all 55 programs are marked INVALID, marking, in turn, another 72 programs INVALID. I make the necessary changes to those 6 programs, testing them thoroughly. I then recompile any invalid programs, at which time they automatically adapt to the changes in my data structures and compile cleanly. My application is ready once again for production.

Doesn't that sound very pleasant? Unfortunately, most of us would not be able to enjoy such an experience. Rather, we write our code so that the slightest change in data structures wreaks havoc in our source, not only marking excessive numbers of programs INVALID but also requiring extensive and ultimately unnecessary modifications before the application is running again.

This article offers several suggestions for ways to write your code so that your application is, as much as possible, self-managing, requiring the absolute minimum amount of attention and adjustment to keep it running, even in the face of data structure modifications.

Use Anchored Declarations and SUBTYPEs

Consider the following code:


1 CREATE OR REPLACE PROCEDURE process_employee (
2 employee_id_in IN number)
3 IS
4 l_fullname VARCHAR2(200);
5 BEGIN
6 SELECT last_name || ',' ||
7 first_name
8 INTO l_fullname
9 FROM employee
10 WHERE employee_id = employee_id_in;
11
12 ... /*other processing*/ ...
13 END;


I pass in the primary key value (employee_id) and use it to retrieve the full name of an employee in the format "last comma first." The way I have written this code makes it very vulnerable, unfortunately, to relatively minor changes in the underlying employee table. Specifically, I have hard-coded the datatypes of my parameter (line 2) and variable (line 4). Let's explore the impact of this rather lazy approach to writing code.

Suppose the DBA changes the datatype of the primary key from NUMBER to VARCHAR2 and starts using alphanumeric values. The process_employee program will be marked INVALID, because it queries the employee table. It will recompile cleanly, without a programmer having to make any modifications. Yet I will not be able to call this procedure with an employee ID that contains a letter, because the datatype has been hard-coded to NUMBER.

A much better way to write this procedure header is


1 CREATE OR REPLACE PROCEDURE process_employee (
2 employee_id_in IN employee.employee_id%TYPE)


I have now "anchored" the datatype of the parameter directly to that of the table's column. Every time my procedure is compiled, PL/SQL will look up the datatype of the column from the data dictionary and use that in the compiled code. So if the DBA does make the column alphanumeric, no harm will befall this code.

The declaration on line 4, unfortunately, cannot be resolved so easily—and it is much more likely to cause problems in my application. Here is the thought process that results in such a declaration: I know that I should anchor my declaration of l_fullname so that as the lengths of my names grow, my variable will automatically adjust. Yet what can I use as an anchor? The following declaration avoids the hard-coding but does not avoid the problem of a VALUE_ERROR exception if the combination of first and last names exceeds the length of a last name:


l_fullname employee.last_name%TYPE;


The problem here is that the full name is a derived value. There simply isn't a column I can use in an anchored declaration. For such situations, however, Oracle offers a fine solution: create and use a SUBTYPE.

A SUBTYPE is an alias for another already defined datatype. In the following "employee rules" (employee_rp) package specification, for example, I define a new datatype called fullname_t, which is really nothing more than another name for VARCHAR2(1000):


1 CREATE OR REPLACE PACKAGE employee_rp
2 AS
3 SUBTYPE fullname_t IS VARCHAR2 (1000);
4 END employee_rp;


With this SUBTYPE in place, I can adjust my declaration to


l_fullname employee_rp.fullname_t;


I have now removed the hard-coding from my process_employee procedure's declaration. Does this allow my code base to manage itself? Not completely, but it's much better than before. If all developers take advantage of predefined, application-specific types such as fullname_t whenever they declare a derived value, they will reduce the points of failure to one place in their code—in this case, the employee_rp package.

If there comes a time when 1,000 characters isn't enough to hold last and first names, I change just this single line of code in employee_rp and recompile my code base, and everything is up and running with the current, valid definition.

Always Fetch into Records

We PL/SQL developers generally live in the moment. We write code today to satisfy today's requirements. We don't give much thought to what our code might be doing or changing to do in the future. We also write an awful lot of queries in our code. The intersection of "coding for the present" and querying data can lead to significant problems in our code in the future. Let's look at an example.

I am building an application to keep track of employment levels throughout the Americas, so that a government agency can analyze the impact of the North American Free Trade Agreement (NAFTA). In order to make it easier to optimize and maintain the code, I have hidden all SELECT statements against the employee_level table in the employee_ level_qp package ("QP" for Query Package) and made the result sets available through REF CURSORs.

Here is a demonstration of this technique. The employee_level table is defined as follows:


1 CREATE TABLE employee_level (
2 employer_id INTEGER
3 ,employee_count INTEGER
4 ,measurement_date DATE
5 );


First, I define the package specification, which includes a REF CURSOR type declaration and the header of a function that returns all columns for all the rows in the table, as shown in Listing 1.

Code Listing 1: Defining the employee_level_qp specification


1 CREATE OR REPLACE PACKAGE employee_level_qp
2 IS
3 TYPE employee_level_rc IS
4 REF CURSOR RETURN employee_level%ROWTYPE;
5
6 FUNCTION allrows_CV RETURN employee_level_rc;
7 END employee_level_qp;
8 /

I then implement the function, using the OPEN FOR statement, as shown in Listing 2.

Code Listing 2: Implementing the allrows_cv function


1 CREATE OR REPLACE PACKAGE BODY employee_level_qp
2 IS
3 FUNCTION allrows_cv
4 RETURN employee_level_rc
5 IS
6 retval employee_level_rc;
7 BEGIN
8 OPEN retval
9 FOR
10 SELECT employer_id, employee_count, measurement_date
11 FROM employee_level
12 ORDER BY measurement_date;
13
14 RETURN retval;
15 END allrows_cv;
16 END employee_level_qp;
17 /

With this package in place, I can query data from the employee_level table. The analyze_data procedure in Listing 3 illustrates the kind of code developers often write.

Code Listing 3: Creating a typical analyze_data procedure


1 CREATE OR REPLACE PROCEDURE analyze_data
2 IS
3 l_employee_levels employee_level_qp.employee_level_rc;
4 l_empid integer;
5 l_count integer;
6 l_date date;
7 BEGIN
8 l_employee_levels := employee_level_qp.allrows_CV;
9 LOOP
10 FETCH l_employee_levels INTO l_empid, l_count, l_date;
11 EXIT WHEN l_employee_levels%NOTFOUND;
12 ... process data ...
13 END LOOP;
14 CLOSE l_employee_levels;
15 END;

I have already shown the problems with hard-coded declarations like those found in lines 3 through 6. Line 10, however, presents new challenges. When I first wrote this code, the employee_level table had just three columns, and this code worked just fine. Three months after the application went into production, however, the DBA added a fourth column: examiner_id, a foreign key to the examiner table, so that she could keep track of who examined the employer to determine the employment level.

With the addition of the new column, the analyze_data program would be marked INVALID—and any attempts to recompile this program would fail. The INTO clause of the FETCH on line 10 must now contain four variables, not three. Unfortunately, the number of elements is hard-coded in the very structure of the statement; it will not adjust to match the new structure of the table.

There is, fortunately, a very simple solution to this problem: Never fetch into a list of individual variables. Instead, always fetch into a record structure, as you see in the new implementation of analyze_data shown in Listing 4.

Code Listing 4: Creating a self-managing analyze_data procedure


1 CREATE OR REPLACE PROCEDURE analyze_data
2 IS
3 l_employee_levels employee_level_qp.employee_level_rc;
4 l_one_level employee_level%ROWTYPE;
5 BEGIN
6 l_employee_levels := employee_level_qp.allrows_CV;
7 LOOP
8 FETCH l_employee_levels INTO l_one_level;
9 EXIT WHEN l_employee_levels%NOTFOUND;
10 ... /*process data*/ ...
11 END LOOP;
12 CLOSE l_employee_levels;
13 END;

Now, whenever the table is changed and analyze_data is recompiled, the l_one_level record will have a field for each column in the table and the FETCH statement will be valid. No programmer intervention needed.
:lick: