[Chapter 16] 16.3 The Package SpecificationChapter 16Packages 16.3 The Package Specification The specification of a package lists all the objects in that package that are available for use in applications, and provides all the information a developer needs in order to use objects in the package. A package specification may contain any of the following object specification statements: Variable declaration. Any kind of variable declaration statement, from a Boolean variable to a character string to a number. This variable is then available outside of the package (as well as within the body of the package). TYPE declaration (PL/SQL Version 2 only). Any kind of valid TYPE statement, such as those to create a programmer-defined record type or a PL/SQL table. These complex data structures are then available outside of the package (as well as within the body of the package). Exception declaration. Declare exceptions in a package that can then be raised and handled outside of the package. Cursor specification (PL/SQL Version 2 only). Specify a cursor's name and its RETURN clause. This cursor can then be opened, fetched, and closed outside of the package (as well as within the body of the package). This is available only in PL/SQL Version 2 because Version 1 does not support the required RETURN clause for a cursor. For more information on cursors, see Chapter 6, Database Interaction and Cursors. Module specification. Place the full specification for a module in the package specification. A module specification is the module type (PROCEDURE or FUNCTION), module name, parameter list, and RETURN clause (for a function). This module can then be called both from within and outside of the package. Of these object specification statements, only the cursor and the module need to be defined in the body of the package. The cursor and module, in other words, are not completely determined by the specification alone. The cursor needs its SELECT statement. The module needs its executable section. The variable, TYPE, and exception declarations, on the other hand, do not need any additional code for completion -- they need only their declaration statements. You can, therefore, write a package that consists solely of a specification and has no body at all, as you will see in the next section. The specification is the API (Application Programmatic Interface) into the package's contents. A developer should never have to look at the actual code in a package in order to use an object in the specification. The pets_inc package specification shown earlier contains a variable, cursor, and two modules. I do not need to know how the package function next_pet_shots determines the date when a pet will next need its shots. I need to know just the name of the function, its parameters, and the function's return datatype. Similarly, the package makes available a predefined cursor that gets me everything I need to know about a pet based on its primary key, pet_id. I, as a developer, do not have to understand the underlying data structures involved. The cursor might use a SELECT statement against a base table or against a three-way join with a sub-SELECT. None of that matters to me. In fact, my ignorance in these matters liberates my brain to work on how I can apply these package elements to good use in my application. 16.3.1 Packages Without BodiesA package only requires a body if one of the following is true:You want to define private package elementsYou have included a cursor or module in your specification A package may consist solely of declarations of public package elements. In this case, there is no need for a package body. This section offers two examples of scenarios where a bodiless package may be just the ticket. 16.3.1.1 A package of exceptions The exception handler package in the next example declares a set of programmer-defined exception numbers and exceptions to go with them. It also declares a PL/SQL table to hold the associated error messages (see Chapter 8, Exception Handlers, for more information about exceptions and the pragma statement, EXCEPTION_INIT). PACKAGE exchdlr IS en_general_error NUMBER := -20000; exc_general_error EXCEPTION; PRAGMA EXCEPTION_INIT (exc_general_error, -20000); en_must_be_eighteen NUMBER := -20001; exc_must_be_eighteen EXCEPTION; PRAGMA EXCEPTION_INIT (exc_must_be_eighteen, -20001); max_error_number_used NUMBER := -20001; TYPE error_msg_tabtype IS TABLE OF VARCHAR2 (240) INDEX BY BINARY_INTEGER; error_msg_table error_msg_tabtype; END exchdlr;Because this package does not specify any cursors or modules, I do not need to create a body for the exception handler package. In Chapter 8 I include a version of this package that does, in fact, specify two procedures in the package. That version does need a package body). 16.3.1.2 A package of magic values A magic value is a literal that has special significance in a system. These values might be type codes or validation limits. Your users will tell you that these magic values never change. "I will always have only 25 line items in my profit-and-loss," one will say. "The name of the parent company," swears another, "will always be ATLAS HQ." Don't take these promises at face value, and never code them into your programs. Instead of writing code like this: IF footing_difference BETWEEN 1 and 100 THEN adjust_line_item; END IF; IF cust_status = 'C' THEN reopen_customer; END IF;you should instead replace the magic values with named constants, as follows: IF footing_difference BETWEEN min_difference and max_difference THEN adjust_line_item; END IF; IF cust_status = closed_status THEN reopen_customer; END IF;The same magic values often appear in many different modules. Rather than declaring them repeatedly in each module, these magic values should be treated as global data in your application. And where can you create global data in PL/SQL? That's right, in a package! The bodiless package shown in the next example contains all the magic values in my application: PACKAGE config_pkg IS closed_status CONSTANT VARCHAR2(1) := 'C'; open_status CONSTANT VARCHAR2(1) := 'O'; active_status CONSTANT VARCHAR2(1) := 'A'; inactive_status CONSTANT VARCHAR2(1) := 'I'; min_difference CONSTANT NUMBER := 1; max_difference CONSTANT NUMBER := 100; earliest_date CONSTANT DATE := SYSDATE; latest_date CONSTANT DATE := ADD_MONTHS (SYSDATE, 120); END config_pkg;Using this package, my two IF statements above now become:IF footing_difference BETWEEN config_pkg.min_difference and config_pkg.max_difference THEN adjust_line_item; END IF; IF cust_status = config_pkg.closed_status THEN reopen_customer; END IF;Notice that when you reference a package element you must use dot notation in the format package_name.object_name so the compiler can resolve the reference to the object's name. This is similar to prefacing a reference to a GLOBAL variable in Oracle Forms with the word GLOBAL, as in :GLOBAL.system_name. If any of my magic values ever change, I need to modify only the assignment to the appropriate constant in the configuration package. I do not need to change a single program module. Just about every application I have reviewed (and many that I have written) mistakenly includes hardcoded magic values in the program. In every single case (especially those that I myself wrote), the developer had to make repeated changes to the programs, both during development and maintenance phases. It was often a headache, sometimes a nightmare; I cannot emphasize strongly enough the importance of consolidating all magic values into a single package, with or without a body. 16.3.1.3 Top-down design with bodiless packagesThere is another motivation for writing a package without a body: you don't yet know what the body is going to look like. If you follow a top-down design methodology, you start with a general description of an application and gradually decompose into separate functional areas, programs, or individual statements. With the package structure you can immediately translate high-level refinements into code, while at the same time postponing a resolution of the actual implementation of that code. You can even compile references to unimplemented package modules as long as that module specification is available in a package specification. Suppose I need to build an application to track calls about product quality. The functional areas of this application include call entry/maintenance, data validation, call analysis, and call assignment, and are represented in the packages shown below: Package to maintain support calls:PACKAGE support IS PROCEDURE add_call (call_id IN INTEGER); PROCEDURE remove_call (call_id IN INTEGER); END support;Package to perform analysis on call workload:PACKAGE workload IS FUNCTION average (dept_id IN INTEGER) RETURN NUMBER; FUNCTION operator (operator_id IN INTEGER) RETURN NUMBER; END workload;Notice that I have only created package specifications. There is no body yet for these modules. Still, I can now implement the assigncall procedure below, referencing my "stubs." This procedure will compile successfully. PROCEDURE assigncall (call_id IN INTEGER, operator_id IN INTEGER, dept_id IN INTEGER) IS BEGIN IF workload.operator (operator_id) < workload.average (dept_id) THEN support.add_call (call_id); END IF; END;How can PL/SQL do this? It all goes back to the public/private boundary of packages. The package provides all the information you need to call package modules: name, parameters, RETURN type if a function. You should not need to know how a module is implemented. Conversely, the implementation of a module can change, and it should not affect your code -- unless the module's header was affected (a parameter was removed, for example). When you write your own programs, the only thing PL/SQL needs to know at compile time is whether your call to a package module conforms to its specification. Of course, when you want to actually execute your application, you should throw some code into the package body. The separation of specification and body in packages offers you tremendous flexibility in performing high-level design and implementation of your complex applications. See how great packages are? And we haven't even gotten to the body of the package yet! 16.3.2 Declaring Package CursorsWhen you include a cursor in a package specification, you must use the RETURN clause of the cursor. It is an optional part of the cursor definition when that cursor is defined in the declaration section of a PL/SQL block. In a package specification, however, it is a necessary part of the structure. The RETURN clause of a cursor indicates the data elements that are returned by a fetch from the cursor. Of course, these data elements are actually determined by the SELECT statement for that cursor, but the SELECT statement appears only in the body, not in the specification. The cursor specification must contain all the information a program needs to use that cursor; hence the need for the RETURN clause. The RETURN clause may be made up of either of the following datatype structures: A record defined from a database table, using the %ROWTYPE attribute A record defined from a programmer-defined record (available in PL/SQL Version 2 only) These variations are shown below, first with the specification and then with the body: PACKAGE cursor_sampler IS CURSOR caller_tab (id_in NUMBER) RETURN caller%ROWTYPE; /* || By placing this TYPE declaration in the specification, I give || access to the record outside of the package, so that other || programs can create caller records with this structure as well. */ TYPE caller_rec IS RECORD (caller_id caller.caller_id%TYPE, company_id company.company_id%TYPE); CURSOR caller_cur RETURN caller_rec; END cursor_sampler; PACKAGE BODY cursor_sampler IS /* || Notice that in the body I do not repeat the declarations of || the date variable and the custom record structure. */ CURSOR hiredate_cur RETURN date_variable%TYPE IS SELECT hire_date FROM employee; CURSOR caller_key RETURN caller.caller_id%TYPE IS SELECT caller_id FROM caller; CURSOR caller_tab (id_in NUMBER) RETURN caller%ROWTYPE IS SELECT * FROM caller WHERE caller_id = id_in; CURSOR caller_cur RETURN caller_rec IS SELECT caller_id, company_id FROM caller; END cursor_sampler;You have a lot to gain by creating cursors in packages and making those cursors available to the developers on a project. Crafting precisely the data structures you need for your application is hard and careful work. These same structures -- and the data in them -- are used in your PL/SQL programs, almost always through the use of a cursor. If you do not package up your cursors and provide them "free of charge and effort" to all developers, they will each write their own variations of these cursors. And they will write many, many different cursors, with different sets of columns and expressions in the SELECT list, and different WHERE clauses. This decentralized effort will result in increased development and debugging time. And then what do you do when your data structures change? You have to hunt down every one of those cursors and make changes as required by the alteration. A much better solution is to think through in advance the common ways that developers will need to access data through cursors, and then place these in one or more packages. These cursors will not only provide the standard list of columns and expressions, but also perform the necessary joins, subselects, and so on required to retrieve that data. Publish the specification and perhaps even the body, so that everyone knows the SQL behind the cursors. Provide examples of how to reference and use the cursors. Build a mechanism for adding new cursors to the packages as developers discover new variations of data access. Then, developers do not necessarily have to understand how to join three or six different highly-normalized tables to get the right set of data. They can just pick a cursor and leave the data analysis to someone else. This process is very similar to the view-building effort required to support real ease of use in an ad hoc query/EIS tool. Perhaps more importantly, everyone will be "singing from the same songbook." If the data structure changes and a new table must now be joined into an existing cursor in order to accurately query the correct rows, you simply change the cursor in the package. If the cursor specification (name, parameters, and RETURN clause) itself does not change, then you don't even have to recompile all the programs using that cursor. See Chapter 6, for more information about the cursor RETURN clause. NOTE: The file named curs_ret.sp on the companion disk contains a package of cursors for the employee and department tables; they should give you an idea of what will be possible in your own, more complex environments. 16.2 Overview of Package Structure16.4 The Package Body Copyright (c) 2000 O'Reilly & Associates. All rights reserved.