Advanced Oracle Pl/Sql: Programming With Packages (Nutshell Handbook)
ISBN: B00006AVR6
EAN: N/A
EAN: N/A
Year: 1995
Pages: 195
Pages: 195
Authors: Steven Feuerstein, Debby Russell
| Chapter 2 Best Practices for Packages |
One of the most powerful aspects of the package is the ability to overload program units. When you overload, you define more than one program with the same name. These programs will differ in other ways (usually the number and types of parameters) so that at runtime the PL/SQL engine can figure out which of the programs to execute. You can take advantage of the overloading feature of packages to make your package-based features as accessible as possible.
Does overloading sound unfamiliar or strange? Well, have you ever used the TO_CHAR function? If so, then you have already been enjoying the creature comforts of overloading. TO_CHAR converts both numbers and dates to strings. Have you ever wondered why you don't have to call functions with names like TO_CHAR_FROM_DATE or TO_CHAR_FROM_NUMBER? Probably not. You probably just took TO_CHAR for granted, and that is how it should be.
In reality, there are two different TO_CHAR functions (both defined in the STANDARD package): one to convert dates and another to convert numbers. The reason that you don't have to care about such details and can simply execute TO_CHAR is that the PL/SQL runtime engine examines the kind of data you pass to TO_CHAR and then automatically figures out which of the two functions (with the same name) to execute. It's like magic, only it's better than magic: it's intelligent software!
When you build overloaded modules, you spend more time in design and implementation than you might with separate, standalone modules. This additional up-front time will be repaid handsomely down the line in program productivity and ease of use.
You will not have to try to remember the different names of the modules and their specific arguments. Properly constructed, overloaded modules will have anticipated the different variations, hidden them behind a single name, and liberated your brain for other, more important matters.
See Chapter 16, of Oracle PL/SQL Programming for a more comprehensive coverage of overloading restrictions and examples.
When you overload, you take the first step towards providing a declarative interface to PL/SQL-based functionality. With a declarative approach, a developer does not write a program to obtain the necessary functionality. Instead, she describes what she wants and lets the underlying code handle the details (this follows the approach used by the SQL language). The process of overloading involves abstracting out from separate programs into a single action.
You want to display a date? You want to display a number? You want to display a string and a number? Hold on a minute. The common element is that you want to display something -- lots of somethings, in fact. So don't create display_date, display_string, etc. procedures. Instead, offer a single display procedure, which is in fact many overloaded display procedures.
With the overloading in place, your user must only remember this: when I want to display something, I simply ask the display program to take care of it for me. What do I pass to it? Whatever I want it to display. I will not (and do not have to) worry about the how of the display mechanism. Those details are hidden from me.
Here are some of the circumstances that cause the PL/SQL fairy to whisper in my ear "Overload, overload...":
Apply the same action to different kinds or combinations of data.
Allow developers to use a program in the most natural and intuitive fashion; you use overloading to fit your program to the needs of the user.
Make it easy for developers to specify, unambiguously and simply, the kind of action desired.
I explore these circumstances in the following sections.
This is probably the most common reason to employ overloading. The p package of PL/Vision (see the following sidebar) offers an excellent example of this kind of overloading opportunity. This package contains eight overloadings of the l procedure so that you can pass many different combinations of data and have the package interpret and display the information properly. The following headers show, for example, a simplified portion of the specification for the p package, which illustrates the overloading:
PROCEDURE l (date_in IN DATE, mask_in IN VARCHAR2 := PLV.datemask); PROCEDURE l (char_in IN VARCHAR2, number_in IN NUMBER); PROCEDURE l (boolean_in IN BOOLEAN);
Because of my extensive overloading, I can pass a complex date expression (taking me back 18 years) and see the date and time in a readable format with a minimum of effort:
SQL> exec p.l(ADD_MONTHS(SYSDATE,-316)); February 18, 1970 17:50:12
I can combine strings and numbers together easily, as shown in this exception section:
BEGIN p.l (1/0); EXCEPTION WHEN ZERO_DIVIDE THEN p.l (SQLERRM, SQLCODE); END; / SQL> @above_script ORA-01476: divisor is equal to zero: -1476
And, finally, I can pass a Boolean expression directly to the p.l procedure and have it display meaningful information:
SQL> exec p.l ('a' IN ('d', 'e', 'f')); FALSEJust to give you a sense of the benefit of overloading in this case, if I did not have access to the p package and instead relied on DBMS_OUTPUT.PUT_LINE to generate my output, I would have to write the following code to handle the last call to p.l:
IF bool_value IN ('d', 'e', 'f') THEN DBMS_OUTPUT.PUT_LINE ('TRUE'); ELSE DBMS_OUTPUT.PUT_LINE ('FALSE'); END IF;Why do I need to do this? The DBMS_OUTPUT package does overload its PUT_LINE procedure, but only for single string, date, and number values. It does not handle Booleans at all. It also does not allow me to pass combinations of data. And it does not show the time component of a date variable. What a hassle! For all these reasons, my extra layer of overloaded code in the p package liberates me from having to write extra code. I just tell p.l what I want to see and it figures out how to display that information.
I talk about coming up with names for your package that are clear, accurate, and easy to remember. Then I showcase the p.l procedure in my best practice on overloading. Surely I am not going to argue that p is a good name for a package -- and what about l as the name of a procedure? What justification could I possibly have for the names I chose for these elements?
My p.l procedure is a substitute for and rebellion against the DBMS_OUTPUT.PUT_LINE procedure. I hated the 20 characters I had to type to generate output from my PL/SQL programs (in uppercase no less, since that is my convention). I was frustrated by the limited overloading of the package itself. So when I set out to build my own layer of code around DBMS_OUTPUT, I was determined to use the fewest characters possible. The result is p.l.
I found it difficult to justify this obscure name, but John Beresniewicz, my able and deep-thinking reviewer, contributed this observation: "It's possible that the need for clearly descriptive (i.e., lengthy) names is directly proportional to the amount of work performed by the procedure and inversely proportional to the frequency of use. That is, procedures that implement a high level of functionality need clearly descriptive names and they will presumably be called less frequently (and these long names won't clog up the source code). Conversely, low-level routines called frequently need shorter names (to avoid clog) but nobody forgets their names (even if cryptic) since they are used all the time." I couldn't have stated it better myself!
The same technique is also readily visible in the PLVtab package. This PL/SQL table-oriented package offers nine overloadings of the display procedure, one for each kind of PL/SQL table predefined in the package. As far as a user of PLVtab.display is concerned, there is just one program to display a PL/SQL table. The only difference between each of the versions of PLVtab.display is the first argument, the table type, as shown in the following header for the display procedure:
PROCEDURE display (table_in IN number_table|boolean_table|date_table, end_row_in IN INTEGER, header_in IN VARCHAR2 := NULL, start_row_in IN INTEGER := 1, failure_threshold_in IN INTEGER := 0, increment_in IN INTEGER := +1);
When you see that vertical bar in documentation for program headers, by the way, that means you are dealing with an overloaded program. The more variations of data you provide in your overloadings, the more useful you make your package. There is, of course, a price to pay for your overloadings. While the user thinks there is just one program to call, you know that in reality there is a different program for each overloading.
A key challenge, therefore, that comes with successful overloading is to figure out how to implement all those programs without creating a total mess in your package body. This challenge is addressed in the section called "Modularizing for Maintainable Packages" later in this chapter.
Does the idea of fitting a program to your user sound odd or unnecessary? If so, change your attitude. We write our software to be used, to help others get their jobs done more easily or more efficiently. You should always be on the lookout for ways to improve your code so that it responds as closely as possible to the needs of your users. Overloading offers one way to achieve a very close fit.
You may sometimes end up with several overloadings of the same program because developers will be using the program in different ways. In this case, the overloading does not provide a single name for different activities, so much as providing different ways of requesting the same activity. Consider the overloading for the PLVlog.put_line (shown in simplified form below):
PROCEDURE put_line (context_in IN VARCHAR2, code_in IN INTEGER, string_in IN VARCHAR2 := NULL, create_by_in IN VARCHAR2 := USER); PROCEDURE put_line (string_in IN VARCHAR2);
The first header is the low-level version of put_line. It allows you to specify a full set of arguments to the program, including the context, the code, a string and the Oracle account providing the information. The second header asks only for the string, the text to be logged. What happened to all the other arguments? I suppressed them, because I found that in many situations a user of PLVlog simply doesn't care about all of those arguments. He simply wants to pass it a string to be saved. So rather than make him enter dummy values for all the unnecessary data, I provide a simpler interface, which in turn calls the low-level put_line with its own dummy values:
PROCEDURE put_line (string_in IN VARCHAR2) IS BEGIN put_line (NULL, 0, string_in, USER); END;
It wasn't necessary for me to take this step and provide this overloading. I could simply require that anyone who uses PLVlog.put_line provide values for all those non-defaulted parameters. If developers really had to use PLVlog, they would follow my bidding. And if I were on some kind of power trip, I would feel properly stroked. But if a developer could choose between PLVlog and another package or utility that didn't make him feel dumb, PLVlog would simply not be used. We almost always have choices. I would rather that my software be used because it was too useful and easy to use to reject.
A less common application of overloading offers a way for developers to specify very easily which of the overloaded programs should be executed. The best way to explain this technique is with an example. The PLVgen package allows you to generate PL/SQL source code, including procedures, functions, and packages. Let's consider how to request the generation of a function.
A function has a datatype: the type of data returned by the function. So when you generate a function, you want to be able to specify whether it is a number function, string function, date function, etc. If I ignored overloading, I might offer a package specification like this:
PACKAGE PLVgen IS PROCEDURE stg_func (name_in IN VARCHAR2); PROCEDURE num_func (name_in IN VARCHAR2); PROCEDURE date_func (name_in IN VARCHAR2); END;
to name just a few. Of course, this means that a user of PLVgen must remember all of these different program names. Is it num or nbr? Stg or strg or string? Why use the four-letter date when the others are just three letters? Wow! That is very confusing. Let's try overloading of the kind previously encountered in this chapter. I will declare a named constant for each kind of data and then, well, it would seem that I really only need one version of the func procedure:
PACKAGE PLVgen IS stg CONSTANT VARCHAR2(1) := 'S'; num CONSTANT VARCHAR2(1) := 'N'; dat CONSTANT VARCHAR2(1) := 'D'; PROCEDURE func (name_in IN VARCHAR2, type_in IN VARCHAR2); END;
I could then generate a numeric function as follows:
SQL> exec PLVgen.func ('booksales', PLVgen.num);Now, I still need to know the names of the constants, so it is pretty much the same situation as we encountered in my first func attempt. Furthermore, I would like to be able to pass a default value to be returned by the generated function, so I really would need to overload as shown in the next iteration:
PACKAGE PLVgen IS stg CONSTANT VARCHAR2(1) := 'S'; num CONSTANT VARCHAR2(1) := 'N'; dat CONSTANT VARCHAR2(1) := 'D'; PROCEDURE func (name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN VARCHAR2); PROCEDURE func (name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN NUMBER); PROCEDURE func (name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN DATE); END;
Might there not be a simpler way to handle this? Notice that the second parameter is a way for the user to specify the datatype of the function. You pass in a string constant, and PLVgen uses an IF statement to determine which constant you have provided. Why not skip the constant and simply pass in data itself of the right type? Then the PL/SQL runtime engine itself would automatically perform the conditional logic to determine which program to run, which code to execute. Consider this next version of the PLVgen package specification:
PACKAGE PLVgen IS PROCEDURE func (name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN VARCHAR2); PROCEDURE func (name_in IN VARCHAR2, type_in IN NUMBER, defval_in IN NUMBER); PROCEDURE func (name_in IN VARCHAR2, type_in IN DATE, defval_in IN DATE); END;
The named constants are gone, no longer needed. I can now generate a numeric function with a default value of 15,000 as follows:
SQL> exec PLVgen.func ('booksales', 1, 15000);It doesn't really matter what value I pass as the second argument; it doesn't matter if the argument is a literal or a variable or an expression. It just has to evaluate to a number, so that the PL/SQL runtime engine will know to execute the code associated with the second header in the specification. What could be simpler? You want a numeric function? Pass a number -- any number -- as the type argument. You want a date function? Pass a date -- be it SYSDATE or some locally declared variable.
I am sure that many readers are looking at that last specification and wondering why I just didn't use the defval_in argument to determine the datatype of the function and skip the type_in argument entirely. Take a look at the final PLVgen package specification. I provide a default value of NULL for all the defval_in arguments. I reasoned that you shouldn't have to provide a default value for the function. So I do need that separate, second argument (always required since it has no default value) to guarantee that you will unambiguously specify one of the function generators.
PLVgen uses this technique both for the func procedures and the gas (get-and-set) procedures. Oracle Corporation also uses this overloading approach in the DBMS_SQL built-in package (check out the DEFINE_COLUMN procedure). In fact, it was the DEFINE_COLUMN overloading that gave me the idea for the overloading you find in the PLVgen package. It took me a while to think through what PL/SQL was doing with DEFINE_COLUMN; I found the simplicity simultaneously clever, devilishly simple, and extremely elegant. It is a technique we should all put into use whenever appropriate.
You should now have a solid feeling for the technique of overloading. To build excellent packages, however, you will need to move beyond simply overloading occasionally to overloading at every possible opportunity and in every possible way. You need to develop a sensitivity to when you should overload and how you can overload most effectively.
The benefits and the beauty of overloading can be appreciated fully only by using overloaded programs -- and then in most cases, you won't even notice, because overloading hides the underlying complexity so you can concentrate on more important issues. You will, I hope, get a sense of the value of overloading from using -- and perhaps even extending -- PL/Vision. Do take some time to pursue the various spb files (the package bodies) and examine the many different examples of overloading you will find there.
When I've successfully overloaded an interesting set of programs and succeeded in hiding much of the underlying complexity of my package, I get an all-the-pieces-falling-into-place feeling and a this-is-as-it-should-be feeling and a sense of how-elegant! If you think I sound a bit strange, please withhold judgment until you do some really fancy and extensive overloading and then tell me how you feel.
The more you overload your packaged procedures and functions, the more functionality you offer to your users. Where overloading is appropriate, it is also impossible to overdo your overloading. If you see another interesting and useful combination, if you see a way to simplify the way a user passes information to your package, then overload for it! It will always be the right thing to do; your biggest challenge will be in figuring out how to implement all these overloadings in a modular and maintainable fashion. This issue is addressed in the next section.
| 2.7 Building Windows Into Your Packages | 2.9 Modularizing for Maintainable Packages |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.