Having created a very flexible twice function and tested it successfully with my new UPPER-lower and lower-UPPER requirements, I can now step back into the stream of application development. (I consider work on twice as part of the process of building my generic PL/SQL toolset.) So I continue to code and, after a while, come back to one of my earlier uses of twice:
v_prodtype:= twice (UPPER (v_prodtype));
While I don't change this line of code, I do have to modify others in the same procedure. I then recompile that procedure and am shocked to get this error:
PLS-00306: wrong number or types of arguments in call to 'TWICE'
Suddenly code that was working earlier in the day is no longer even able to compile. What went wrong?
When I enhanced the twice function, I added a second parameter -- and I certainly needed to do that. I did not, unfortunately, take into account existing uses of twice. The way that I changed the parameter list actually invalidated those prior instances. Since I did not provide a default value for the action_in parameter, it became necessary for all executions of twice to include two values in the argument list. This is an unacceptable way to enhance existing code.
If I am going to make changes to programs currently in use across my production applications (or in any version of previously existing programs), I want to do so in a way that allows that code to continue to work as it did before without any changes. Otherwise I am facing a maintenance nightmare that would, in effect, stop me from enhancing code. It simply isn't possible (especially given the state of PL/SQL development and analysis tools) to search (and replace!) efficiently for all uses of a given program.
I must instead come up with a technique that will support backward compatibility with earlier uses of twice, while simultaneously allowing me to use that same program in new ways. Default values for my action_in parameter offer this possibility.
When an IN parameter has a default value, it is not necessary to include a value for that argument when the program is called. If a value is not specified, the program will use the default value in its execution. If these IN parameters are all trailing parameters (they come at the end of the parameter list), you can simply ignore them when calling the program. If the IN parameters are positioned before one or more IN or IN OUT parameters, you will have to use named notation to skip over that parameter. (See Oracle PL/SQL Programming for more details on named notation).
I can make a very simple change to the header of the twice function:
FUNCTION twice (string_in IN VARCHAR2, action_in IN VARCHAR2 DEFAULT 'N')
Then, if I call twice with just a single argument, it will assume that I do not want to perform any kind of case conversion. With this change in place, all previous occurrences of twice will work as they did before I even thought of a case-conversion action parameter. IN parameters with default values are a critical technique in ensuring backward compatibility for enhanced PL/SQL programs.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.