16.4.5.2 Impact of nocopy

Depending on your application, NOCOPY can improve the performance of programs with IN OUT or OUT parameters. As you might expect, these potential gains come with a tradeoff: if a program terminates with an unhandled exception, you cannot trust the values in a NOCOPY actual parameter.

What do I mean by "trust"? Let's review how PL/SQL behaves concerning its parameters when an unhandled exception terminates a program. Suppose that I pass an IN OUT record to my calculate_totals procedure. The PL/SQL runtime engine first makes a copy of that record and then, during program execution, makes any changes to that copy. The actual parameter itself is not modified until calculate_totals ends successfully (without propagating back an exception). At that point, the local copy is copied back to the actual parameter, and the program that called calculate_totals can access that changed data. If calculate_totals terminates with an unhandled exception, however, the calling program can be certain that the actual parameter's value has not been changed.

That certainty disappears with the NOCOPY hint. When a parameter is passed by reference (the effect of NOCOPY), any changes made to the formal parameter are also made immediately to the actual parameter. Suppose that my calculate_totals program reads through a 10,000-row collection and makes changes to each row. If an error is raised at row 5000 and propagated out of calculate_totals unhandled, my actual parameter collection will be only half-changed.

As a result, you should be very judicious in your use of the NOCOPY hint. Use it only when you know that you have a performance problem relating to your parameter passing, and be prepared for the potential consequences when exceptions are raised.

16.4.6 Default Values

As you have seen from previous examples, you can provide default values for IN parameters. If an IN parameter has a default value, you do not need to include that parameter in the call to the program. Likewise, a parameter's default value is used by the program only if the call to that program does not include that parameter in the list. You must, of course, include an actual parameter for any IN OUT parameters.

The parameter default value works the same way as a specification of a default value for a declared variable. There are two ways to specify a default value: either with the keyword DEFAULT or with the assignment operator (:=), as the following example illustrates:

PROCEDURE astrology_reading

(sign_in IN VARCHAR2 := 'LIBRA',

born_at_in IN DATE DEFAULT SYSDATE) IS

By using default values, you can call programs with different numbers of actual parameters. The program uses the default value of any unspecified parameters, and overrides the default values of any parameters in the list that have specified values. Here are all the different ways you can ask for your astrology reading using positional notation:

BEGIN

astrology_reading ('SCORPIO',

TO_DATE ('12-24-2001 17:56:10', 'MM-DD-YYYY HH24:MI:SS');

astrology_reading ('SCORPIO');

astrology_reading;

END;

The first call specifies both parameters explicitly. In the second call, only the first actual parameter is included, so born_at_in is set to the current date and time. In the third call, no parameters are specified, so we cannot include the parentheses. Both of the default values are used in the body of the procedure.

What if you want to specify a birth time, but not a sign? To "skip over" leading parameters that have default values, you will need to use named notation. By including the name of the formal parameter, you can list only those parameters to which you need to pass values. In this (thankfully) last request for a star-based reading of my fate, I have successfully passed in a default of Libra as my sign and an overridden birth time of 5:56 PM.

BEGIN

astrology_reading (

born_at_in =>

TO_DATE ('12-24-2001 17:56:10', 'MM-DD-YYYY HH24:MI:SS');

END;