As Published In
Oracle Magazine
January/February 2007

TECHNOLOGY: PL/SQL Practices


The Right Place for PL/SQL

By Steven Feuerstein Oracle ACE Director

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.

Next Steps



READ more
PL/SQL Practices
Best Practice PL/SQL

LEARN more about using PL/SQL collections with SQL object types
Oracle Database PL/SQL User's Guide and Reference

 DOWNLOAD Oracle Database 10g

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 (steven.feuerstein@quest.com) 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.


Send us your comments