Disciplined PL/SQL

Four simple guidelines for improving the quantity and quality of PL/SQL code you write

November/December 2003

I have been writing PL/SQL code since 1990. This means I have churned out tens of thousands of lines of software, and most of it, I am certain, was poorly written and difficult to maintain.

Fortunately, I have discovered that it is never too late to find—and follow—new paths to writing better code. In just the past year, the quality of my code has improved significantly; this improvement has come mostly from setting some simple rules and then following them in a disciplined way.

This article offers four pieces of advice for novice and experienced PL/SQL developers; follow any of these suggestions and your code will improve. Follow all of them, and you likely will be struck by the sudden realization that you are a much better programmer than you ever thought possible.

Few of us work in isolation; most PL/SQL development takes place in relatively large organizations. Yet we are still, by and large, left to our own devices, working alone in our own cubicles. Hardly any PL/SQL development teams engage in formal code review or systematic testing.

I cannot, through this magazine article, change the fundamental dynamics in your development team. I have, therefore, chosen the following points of advice carefully. You will not need to obtain management approval to implement any of them. You won't have to get everyone on your team, whether it's large or small, to agree to the same coding guidelines. Instead, you can concentrate on doing nothing more than transforming your personal coding experience by following these suggestions:

1. Follow naming conventions as though your life depended on it.
2. Break your SQL addiction: The less SQL you write, the better.
3. Keep your executable sections small: Say goodbye to "spaghetti code."
4. Find a buddy: Come to love the idea of having someone looking over your shoulder.

1. Follow Naming Conventions

If you establish and follow rigorously a set of naming conventions, particularly for your application components, you will be able to save lots of time.

Of course, the idea of following naming conventions is nothing new, and it probably sounds boring to you. So rather than propose any grand naming schemes, I will be very concrete and specific, and then I will demonstrate to you how useful such conventions can be.

I have spent the last few months designing and constructing a new tool for PL/SQL developers. It is called Swyg, and it helps programmers through code generation, testing, and reuse. It therefore has several distinct components. I decided to assign a two-letter abbreviation to each component as follows:

SF —Swyg foundation elements
SM —Swyg metadata
SG —Swyg generator
SL —Swyg code library
ST —Swyg unit testing

I then followed the naming conventions in Table 1, using these abbreviations. What advantages accrue to following such conventions? Very generally, if I am consistent about how I name things, I can write my code more smoothly and productively.

Specifically, the predictability of the conventions means I can write SQL programs that generate useful scripts. For example, using the conventions in Listing 1. This sort of script is handy, because it means I do not have to maintain the installation script manually. When I add another table to the Swyg schema and generate the related set of packages, I simply run my script, and out pops the updated installation script.

Code Listing 1: Package installation script generator

 1  SELECT    1 ord, 'PROMPT Compiling types package specification for "'
 2         || object_name
 3         || '"...'
 4         || CHR (10)
 5         || '@@'
 6         || object_name || '_tp.pks'
 7      || CHR(10)
 8      || 'SHOW ERRORS' line
 9    FROM all_objects
10   WHERE owner = '&install_owner'
11     AND object_name LIKE '&obj_wildcard' || '%'
12     AND object_type IN ('TABLE')
13  UNION
14  SELECT    2 ord, 'PROMPT Compiling query package specification for "'
15         || object_name
16         || '"...'
17         || CHR (10)
18         || '@@'
19         || object_name || '_qp.pks'
20      || CHR(10)
21      || 'SHOW ERRORS' line
22    FROM all_objects
23   WHERE owner = '&install_owner'
24     AND object_name LIKE '&obj_wildcard' || '%'
25     AND object_type IN ('TABLE')
26  UNION
27  SELECT    3 ord, 'PROMPT Compiling change package specification for "'
28         || object_name
29         || '"...'
30         || CHR (10)
31         || '@@'
32         || object_name || '_cp.pks'
33      || CHR(10)
34      || 'SHOW ERRORS' line
35    FROM all_objects
36   WHERE owner = '&install_owner'
37     AND object_name LIKE '&obj_wildcard' || '%'
38     AND object_type IN ('TABLE')
39  UNION
40  SELECT    4 ord, 'PROMPT Compiling "extra" package specification for "'
41         || object_name
42         || '"...'
43         || CHR (10)
44         || '@@'
45         || object_name || '_cp.pks'
46      || CHR(10)
47      || 'SHOW ERRORS' line
48    FROM all_objects
49   WHERE owner = '&install_owner'
50     AND object_name LIKE '&obj_wildcard' || '%'
51     AND object_type IN ('TABLE')
52  UNION
53  SELECT    5 ord, 'PROMPT Compiling query package body for "'
54         || object_name
55         || '"...'
56         || CHR (10)
57         || '@@'
58         || object_name || '_qp.pkb'
59      || CHR(10)
60      || 'SHOW ERRORS' line
61    FROM all_objects
62   WHERE owner = '&install_owner'
63     AND object_name LIKE '&obj_wildcard' || '%'
64     AND object_type IN ('TABLE')
65  UNION
66  SELECT    6 ord, 'PROMPT Compiling change package body for "'
67         || object_name
68         || '"...'
69         || CHR (10)
70         || '@@'
71         || object_name || '_cp.pkb'
72      || CHR(10)
73      || 'SHOW ERRORS' line
74    FROM all_objects
75   WHERE owner = '&install_owner'
76     AND object_name LIKE '&obj_wildcard' || '%'
77     AND object_type IN ('TABLE')
78  UNION
79  SELECT    7 ord, 'PROMPT Compiling "extra" package body for "'
80         || object_name
81         || '"...'
82         || CHR (10)
83         || '@@'
84         || object_name || '_xp.pkb'
85      || CHR(10)
86      || 'SHOW ERRORS' line
87    FROM all_objects
88   WHERE owner = '&install_owner'
89     AND object_name LIKE '&obj_wildcard' || '%'
90*    AND object_type IN ('TABLE');

2. Break Your SQL Addiction

As counterintuitive as this might seem, the less SQL you write, the better. This is strange advice for a PL/SQL developer, because one of the key advantages of PL/SQL is the ease of writing SQL statements in your code. This ease, however, is also an Achilles' heel of the language.

You can place native SQL statements directly inside PL/SQL code; no intermediate layers such as JDBC or ODBC are necessary. Consequently, PL/SQL developers generally embed SQL statements whenever and wherever they need them in their application code. So what's wrong with that?

Dispersion of SQL statements in PL/SQL code leads inevitably to the following:

• Repetition of the same logical statement, although with different physical representations, resulting in excessive parsing and difficulty in optimizing the performance of your application.

• Exposure of business rules and formulas. There is a good chance that you will include right in the SQL statement logic that implements business rules. Such rules, of course, are always changing, so the maintenance costs of your application rise rapidly.

Of course, just about every PL/SQL application you will write is built on underlying tables and views. You need to execute SQL statements. The question is not if, but when and how.

Your application will be much more robust, and you will find it much easier to build and maintain, if you encapsulate your data structures, or hide them behind a layer of PL/SQL code, usually a package.

Let's look at a simple example. Suppose I need to write a program that processes an employee. The first thing I need to do is get the full name of that employee, defined as "last comma (,) first"; I can then perform the detailed analysis. Listing 2 offers an example of the sort of code I am likely to write in this situation.

Code Listing 2: Hard-coding a SQL query (and business rules)

 1  CREATE OR REPLACE PROCEDURE process_employee (
 2     employee_id_in IN NUMBER)
 3  IS
 4     l_name VARCHAR2(100);
 5  BEGIN
 6     SELECT last_name || ',' ||
 7            first_name
 8       INTO l_name
 9       FROM employee
10      WHERE employee_id = employee_id_in;
11
12      perform_detailed_analysis (
13         l_name);
14  END;

It all seems so simple and direct; what could possibly be wrong with this code? An awful lot, actually. Most important, I have exposed a business rule: the structure of a full name. I can test this code, and the application it is built on, for hours. Right after it goes into production, however, I just know that I will be getting calls from users telling me that, in actuality, they need their full names to be presented as "last space first."

What now? Search for all occurrences of a single comma inside quotation marks?

The real solution is to use packages that hide all these details and simply provide a set of predefined, pretested, and preoptimized programs that do all the work for me. Listing 3 shows a rewriting of the process_employee procedure based on encapsulated code. The hr_employee_tp package provides the type I use to define the local variable holding the name; the hr_employee_rp package contains the function that returns the full name, based on a business rule.

Code Listing 3: Encapsulating in a SQL query

 1  CREATE OR REPLACE PROCEDURE process_employee (
  2    employee_id_in IN employee.EMPLOYEE_ID%TYPE)
  3  IS
  4    l_name hr_employee_tp.fullname_t;
  5  BEGIN
  6    l_name :=
  7       hr_employee_rp.fullname (
  8          employee_id_in);
  9
 10    perform_detailed_analysis (
 11        l_name);
 12* END;

Just as it is easy to dump explicit SQL statements into PL/SQL code, it is also easy to talk about how important it is to encapsulate those statements. It is challenging, on the other hand, to write the code that performs the encapsulation; it is likely not even practical to do so. Perhaps generating these packages makes more sense.

I helped build such a generator several years ago. It is called PL/Generator, and in a special arrangement with Quest Software (the current owner of PL/Generator), it is now available at no charge to the PL/SQL development community. You can download it from my Web site by visiting www.StevenFeuerstein.com. Be aware that its encapsulation architecture is different from the convention I outlined earlier in this article. PL/Generator creates a single package that contains all of the type, query, and change logic for a table.

Whether you generate or write your own custom encapsulations, your applications will benefit greatly when you stop writing so much SQL and instead call the programs that execute the SQL for you.

3. Keep Your Executable Sections Small

Let's face it: All too often, against our better judgment and our latest round of New Year's resolutions, we end up writing spaghetti code: large, endless blobs of meandering code that are virtually impossible to understand, much less maintain or enhance. What can you do to avoid spaghetti?

Actually, the solution is simple: Never allow an executable section to exceed more than 50 or 60 lines. This size allows you to view the entire logical structure of the block on a single page of paper or eyeful of screen, which also means that you can actually grasp the intention of the program and understand it at a fairly intuitive level.

You may well agree with all of this yet scoff at my suggestion that your world could ever be contained in 50 lines of code. You should scoff, because that is, of course, not possible. You will no doubt need more than 50 lines of executable code; the question is where you will put that code and how you will organize it.

You will indeed be able to implement requirements of arbitrary complexity and hew to the 50-line limit, if you

• Take care to reuse code whenever possible by placing all business rules and discrete chunks of logic in their own programs (usually functions)

• Make extensive use of local modules, procedures and functions that are defined right inside the declaration section of your program

Suppose I am working on a call center application. I have been told to write a program whose documentation contains this overview:

"For every employee in the specified department, compare that person's workload (the number of calls assigned to the employee) with the average load for an employee in that department. If the employee is underutilized, assign the next open call to that person and schedule an appointment to work on that case."

From previous work on this system, I know that my friend Claudia has written an analysis package that returns information on workload. But assigning open calls and scheduling appointments are both new tasks, defined in detail in the rest of the requirements document.

My first inclination is to read through the entire 15 pages of information, but I resist. Instead, I use a "stepwise refinement" or "top-down design" technique and take my first stab at implementing the program by writing the code in Listing 4.

Code Listing 4: Logic in stepwise refinement

 1  CREATE OR REPLACE PROCEDURE assign_workload (department_in IN emp.deptno%TYPE)
 2  IS
 3    case# INTEGER;
 4
 5    CURSOR emps_in_dept_cur
 6    IS
 7       SELECT * FROM emp
 8        WHERE deptno = department_in;
 9
10    PROCEDURE assign_next_open_case (emp_id_in IN emp.empno%TYPE, case_out OUT NUMBER)
11    IS BEGIN NULL; END assign_next_open_case;
12
13    FUNCTION next_appointment (case_id_in IN NUMBER)
14        RETURN DATE
15    IS BEGIN RETURN SYSDATE; END next_appointment;
16
17    PROCEDURE schedule_case (case_in IN NUMBER, date_in IN DATE)
18    IS BEGIN NULL; END schedule_case;
19     END;
20  /* MAIN ASSIGN_WORKLOAD */
21  BEGIN
22    FOR emp_rec IN emps_in_dept_cur
23    LOOP
24       IF analysis.caseload (emp_rec.empno) <
25           analysis.avg_cases (department_in)
26       THEN
27          assign_next_open_case (emp_rec.empno, case#);
28          schedule_case (case#, next_appointment (case#));
29       END IF;
30    END LOOP;
31* END assign_workload;

The following offers an explanation of the most pertinent lines in Listing 4; I'm starting from the bottom of the program (the compact executable section) and working my way up. This may sound counterintuitive, but it is actually the best way to read through a program that was written with stepwise refinement.

Lines 22-30. I use a cursor FOR loop to iterate through all the employees in the specified department. In lines 24-25, I take advantage of the analysis package's programs to determine if the current employee is underutilized. In lines 27-28, I type in calls to three programs: assign_next_open_case, schedule_case , and next_appointment . I don't know yet what I will have to do to implement these programs, but I know they express through their names and the parameter list what I need to have done.

Lines 10-19. I create "stubs," or placeholder programs, for the three programs in lines 27-28. Note that these are local modules, defined within assign_workload and not callable from any other program.

Lines 5-8. I define a cursor to get all the employees in the specified department. I can now try to compile this code.

Getting a clean compile on such a small program may seem like a small victory, and it is. But small victories such as these—a clean compile, then a simple test, then the addition of a bit more code, another clean compile, and so on—lead to well-constructed programs and a big dose of satisfaction.

I can also verify that the analysis programs work and identify the appropriate employees for assignment. Once all this is done, I will pick one of my three programs, say assign_next_open_case , for my next step or level of refinement. I read the documentation for that task and write a simple, small executable section within assign_next_open_case that reflects the overview of that task.

Soon, my local procedure has its own local procedures and functions, but at every step of the way, my code is short, readable, easily tested, and fixable as needed.

4. Find a Buddy

Computers do not write programs; humans write them.

How many times have you sat hunched over your computer, deeply frustrated by your inability to find a bug in your code? First minutes and then hours pass. Finally, disgusted with yourself and feeling like a failure, you poke your head over the top of your cubicle and ask a friend to come over and take a look.

One of three things usually happens:

• As the friend gets up from her chair, all becomes instantly clear.

• Your friend takes one peek at the screen and immediately puts her finger on the problem.

• Your friend doesn't work on your area of the system, so you have to explain what your program is doing. As you step through the logic, the flaw that is causing the bug pops out at you.

The reality is that it's hard to debug your own code, because you are too attached to and too involved in it.

The best solution to this problem is for the development manager to create a culture in which ideas are shared, ignorance is admitted without penalty, and constructive code reviews take place regularly. Such cultural changes are, unfortunately, difficult to achieve.

In the meantime, I suggest that you take the first steps in helping transform the culture in your group. Seek out another developer, preferably one with more experience than you, and establish a "buddy" relationship: Offer to be a sounding board for that person in case of problems if your buddy will do the same for you. Agree in advance that there isn't anything wrong with not knowing the answer to everything.

Then set a simple rule for yourself: Never struggle for more than half an hour on a bug. When 30 minutes have passed, call your buddy over and put human psychology to work for you, not against you.

This article offered four steps you can take, without investing in new tools or changing overall group process, to transform your coding experience. You don't even have to follow all four steps; the benefits accrue independently.

 READ more

on Oracle Database 10g

articles by Feuerstein