ORACLE - The Right Place for PL/SQL

By Steven Feuerstein

Best practices for PL/SQL location, location, location

I write packages and procedures in both Oracle Database and Oracle Developer applications (Oracle Forms). How should I decide where to put my code?

Another way to frame this question is to ask what the scope of your program should be. That is, from which programs in your application can it be called? Within just a single form? All forms? From within a single program on the server? From any schema that connects to your instance?

I make my decision on these issues by following this principle: Implement the program as closely as possible to where it is used (called).

Location and Relocation Demonstration

For this answer, I use the following business requirement to demonstrate the variations possible and appropriate ways to define my code:

My team is building a call support application. Purchasers of my company's product call us when they have a problem, and we put their calls into a queue if they cannot be handled immediately. I must now write a program that distributes unhandled calls to members of the call support team. The package that will hold all of this logic is call_manager. The procedure for distributing unhandled calls is distribute_calls. Listing 1 shows the header and executable section of this program.

Code Listing 1: distribute_calls procedure

PROCEDURE distribute_calls (    department_id_in IN departments.department_id%TYPE) IS BEGIN    WHILE ( calls_are_unhandled ( ) )    LOOP        FOR emp_rec IN emps_in_dept_cur (department_id_in)        LOOP           IF current_caseload (emp_rec.employee_id) <                   avg_caseload_for_dept (department_id_in)           THEN               assign_next_open_call (emp_rec.employee_id);           END IF;        END LOOP;    END LOOP; END distribute_calls; 

As you can see, this executable section is quite readable: While there is still at least one unhandled call, then for each employee in the specified department, if the current caseload is less than the average for that department, assign the next open call to that employee. Then continue only if there are still unhandled calls.

The executable section calls many subprograms to get the work done:

• calls_are_unhandled function: takes no arguments; returns TRUE if there is still at least one unhandled call, FALSE otherwise
• current_caseload function: returns the number of calls (caseload) assigned to that employee
• avg_caseload_for_dept function: returns the average number of calls assigned to employees in that department
• assign_next_open_call procedure: assigns the employee to the call, making it handled (as opposed to unhandled)

One other thing to note: I haven't yet actually implemented any of these programs. I am using top-down design, also known as stepwise refinement, to stay focused on the overall, high-level logic of the program. That way I avoid getting consumed by all the little details.

I can now go down to the next level of detail and figure out where to put the implementation for these subprograms.

As I said, my rule is: Define the subprogram as closely as possible to its usage. Following that rule without any further analysis, I would define each of the programs as local subprograms within distribute_calls itself, as shown in Listing 2 (the ellipses [...] indicate the implementation of the subprograms).

Code Listing 2: Four local programs in distribute_calls

PROCEDURE distribute_calls (      department_id_in IN departments.department_id%TYPE) IS     FUNCTION calls_are_handled RETURN BOOLEAN            IS BEGIN ... END calls_are_handled;         FUNCTION current_caseload (              employee_id_in IN employees.employee_id%TYPE)         RETURN PLS_INTEGER           IS BEGIN ... END current_caseload;      FUNCTION avg_caseload_for_dept (              employee_id_in IN employees.employee_id%TYPE)         RETURN PLS_INTEGER           IS BEGIN ... END current_caseload;         PROCEDURE assign_next_open_call (              employee_id_in IN employees.employee_id%TYPE)                IS BEGIN ... END assign_next_open_call;    BEGIN 

Procedures and functions directly defined within the declaration section of any PL/SQL block are called local or nested subprograms. In this example, they can be called only within the distribute_calls procedure, and that certainly defines them as closely as possible to their usage.

Yet, as I do this, I find myself thinking about the other programs I have already written in this package and how I might want to use some of this new code in programs I'll write in the future.

I realize, for example, that last week I wrote another function that is very similar to current_caseload. It is now "buried" inside a procedure named show_caseload. Rather than implement the same logic twice (and thereby need to debug and maintain it in both places), it makes more sense for me to move the current_caseload function out of both distribute_calls and show_caseload.

So with a little reshuffling of code, I end up with the package body shown in Listing 3.

Code Listing 3: Relocating the current_caseload function

CREATE OR REPLACE PACKAGE BODY call_manager IS    FUNCTION current_caseload (          employee_id_in IN employees.employee_id%TYPE)        RETURN PLS_INTEGER    IS BEGIN ... END current_caseload;     PROCEDURE show_caseload (          department_id_in IN departments.department_id%TYPE)    IS BEGIN ... END show_caseload;     PROCEDURE distribute_calls (          department_id_in IN departments.department_id%TYPE    )    IS BEGIN ... END distribute_calls; END; / 

Now I have moved the current_caseload function farther away from distribute_calls, but that is because it is used by two subprograms in the package. So it is now as close as possible to both of its usages. Yet I don't expect or see any need for current_caseload to be used outside of the distribute_calls package, so I do not place the header of current_caseload in the package specification.

Now my attention turns to avg_caseload_for_dept. Something about this program seems so familiar. What is it, what is it? Oh, yeah! My coworker Sandra sent out an e-mail last week letting us all know that she had put together a package named call_util that contained several handy utility programs, including a function that returned the average caseload for an employee.

I slap my forehead, dig out the e-mail, and find that the function is named dept_avg_caseload. I check for the existence of call_util in my PL/SQL editing environment, and—lo and behold—the call_util.dept_avg_caseload function in all its already implemented beauty is there, waiting to be used.

I now go back to my distribute_calls procedure, delete the avg_caseload_for_dept function, and change my executable section as shown in Listing 4.

Code Listing 4: Revised distribute_calls executable section

BEGIN     WHILE ( calls_are_unhandled ( ) )     LOOP        FOR emp_rec IN emps_in_dept_cur (department_id_in)        LOOP            IF current_caseload (emp_rec.employee_id) <                      call_util.dept_avg_caseload (department_id_in)            THEN                 assign_next_open_call (emp_rec.employee_id);            END IF;        END LOOP;     END LOOP; END distribute_calls; 

Now one of the subprograms I am using in my procedure is declared so far away that I don't even have control over its implementation and may never even see that implementation. Is that a problem? No. I have more than enough to do and worry about!

The call_util.dept_avg_caseload function is implemented far from my usage, but it is as close as possible to all usages, which span various packages, and so must be declared in the package specification of call_utils.

Whew. I think I have now finished optimizing the location of the definitions of my subprograms. I am left with two local subprograms (calls_are_unhandled and assign_next_open_call), one program (current_caseload) defined privately at the package level (not appearing in the package specification), and another function (call_util.dept_avg_caseload) that someone else wrote and that is available to any schema with execute authority on the call_util package.

I hope the steps I went through to build distribute_calls will help you make your own decisions on where best to place the implementations of your own complex, multilayered programs.

Oracle Developer Code Location

This column has focused on where and how to define code in Oracle Database, but the same rules and logic apply to the Oracle Developer environment. I suggest these guidelines:

• If your program could be useful on both the server side and the client side, move it to the server, because it can be invoked from both sides there.
• If the program is used only in and relevant to client-side modules (it may, for example, manipulate the contents of a field in a form) and you think or know that it will be useful in more than one module, put it into a shared library.
• If your client program is very specific to a current form or report, define it within that module.

---

Steven Feuerstein is Quest Software's PL/SQL evangelist. He has published 10 books on Oracle's programming language, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media), and has created the free Quest Code Tester for Oracle.
More Oracle
Mas Oracle