10.1.1 Restrictions on NOCOPY

A number of situations will cause the PL/SQL compiler to ignore the NOCOPY hint and instead use the default by-value method to pass the OUT or IN OUT parameter. These situations are the following:

• The actual parameter is an element of an index-by table (which could be an entire record structure). You can request NOCOPY for an entire index-by table, but not for an individual element in the table. A suggested workaround is to copy the structure to a standalone variable, either scalar or record, and then pass that as the NOCOPY parameter. That way, at least you aren't copying the entire structure.

• Certain constraints applied to actual parameters will result in the NOCOPY hint being ignored. These constraints include a scale specification for a numeric variable and the NOT NULL constraint. You can, however, pass a string variable that has been constrained by size.

• Both the actual and formal parameters are record structures. One or both records were declared using %ROWTYPE or %TYPE, and the constraints on corresponding fields in these two records are different.

• The actual and formal parameters are record structures. The actual parameter was declared by the PL/SQL engine as the index of a cursor FOR loop, and the constraints on corresponding fields in the records are different.

• In passing the actual parameter, the PL/SQL engine must perform an implicit datatype conversion. A suggested workaround is this: you are always better off performing explicit conversions, so do that and then pass the converted value as the NOCOPY parameter.

• The subprogram requesting the NOCOPY hint is used in an external or remote procedure call. In these cases, PL/SQL will always pass the actual parameter by value.

10.1.2 Impact of NOCOPY Use

Depending on your application, NOCOPY can improve the performance of programs with IN OUT or OUT parameters. As you might expect, these potential gains are only available with a trade-off: 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 that error is propagated out of calculate_totals unhandled, my actual parameter collection will be only half-changed. How will I know what is good data and what is bad?

The following test script (available in nocopy.tst on the companion disk) demonstrates this problem. I create two versions of the same program, each of which moves through a five-row collection, doubling the value of each row. When they hit row 3, however, I raise a VALUE_ERROR exception. I then examine the contents of the collection before and after each program call.

Here are the programs:

/* Filename on companion disk: nocopy.tst */
CREATE OR REPLACE PACKAGE BODY nocopy_test
IS
   PROCEDURE pass_by_value (nums IN OUT number_varray)
   IS
   BEGIN
      FOR indx IN nums.FIRST .. nums.LAST
      LOOP
         nums(indx) := nums(indx) * 2;
         IF indx > 2 THEN RAISE VALUE_ERROR; END IF;
      END LOOP;
   END;
      
   PROCEDURE pass_by_ref (nums IN OUT NOCOPY number_varray)
   IS
   BEGIN
      FOR indx IN nums.FIRST .. nums.LAST
      LOOP
         nums(indx) := nums(indx) * 2;
         IF indx > 2 THEN RAISE VALUE_ERROR; END IF;
      END LOOP;
   END;
   
END;
/

Here is the block that exercises these two programs:

DECLARE
   nums1 number_varray := number_varray (1, 2, 3, 4, 5);
   nums2 number_varray := number_varray (1, 2, 3, 4, 5);
   
   PROCEDURE shownums (
      str IN VARCHAR2, nums IN number_varray) IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (str);
      FOR indx IN nums.FIRST .. nums.LAST
      LOOP
         DBMS_OUTPUT.PUT (nums(indx) || '-');
      END LOOP;
      DBMS_OUTPUT.NEW_LINE;
   END;
BEGIN
   
   shownums ('Before By Value', nums1);
   BEGIN
      nocopy_test.pass_by_value (nums1);
   EXCEPTION
      WHEN OTHERS THEN shownums ('After By Value', nums1);
   END;

   shownums ('Before NOCOPY', nums2);
   BEGIN
      nocopy_test.pass_by_ref (nums2);
   EXCEPTION
      WHEN OTHERS THEN shownums ('After NOCOPY', nums2);
   END;

END;
/

And here are the results:

Before By Value
1-2-3-4-5-
After By Value
1-2-3-4-5-
Before NOCOPY
1-2-3-4-5-
After NOCOPY
2-4-6-4-5-

As you can see from the last set of output numbers, the first three rows of the nums2 variable array have been modified, even though the pass_by_ref procedure did not finish its job.

One concern about this trade-off is that the behavior of your application can change even when you don't actually change any of your code. Suppose that you are running in a distributed database environment, and you rely on remote procedure calls (RPCs). In the current implementation of PL/SQL, the NOCOPY hint is always ignored for RPCs. For the last six months, your application has been calling a program that happened to reside on the same database instance as your application. Then a DBA reconfigured databases and the distribution of code, and now your application is calling a remote procedure -- which may cause your application to behave differently, at least when an exception occurs.

On the other hand, you are not likely to encounter this scenario or others that might cause a change in behavior: for example, parameter aliasing (discussed in the next section) or reliance on the values of parameters after a program call fails with an unhandled exception. Standard, reasonable coding practices should keep you away from such problems.

10.1.3 Parameter Aliasing

Potential corruption of your data structures is not the only complication with NOCOPY. This hint also increases the possibility that you will encounter programs with a situation known as parameter aliasing , where two different identifiers in your program refer to the same memory location (two aliases for the same data), and the behavior of your code does not match your expectations.

TIP: Parameter aliasing as a potential issue predates the introduction of the NOCOPY feature. Programs should not rely on aliasing behavior, and you should examine your code carefully to ferret out such dependencies.

Here is an example of parameter aliasing and the trouble it can cause (see parmalias.sql for the contiguous code). First, I declare a record type containing information about a prisoner (name and number of years incarcerated). Then I declare a variable type array of those records:

/* Filename on companion disk: parmalias.sql */
DECLARE
   TYPE prisoner IS RECORD (
      name VARCHAR2(100),
      years_incarcerated NUMBER);
      
   TYPE prisoner_list IS VARRAY(2000) OF prisoner;
   innocents_on_deathrow prisoner_list := prisoner_list();

Since the death penalty was reinstated in the state of Illinois in 1977, 10 men have been executed (as of May 1999). Eleven men have been found to be innocent of the crimes for which they were to be killed and have been released, but often after spending many years on Death Row (Anthony Porter was there for 18 years!). Now that is what I call a travesty of justice. So here's a procedure -- defined within the same PL/SQL block or scope -- to add a travesty to the list:

PROCEDURE add_travesty (
   illinois_inhumanity IN OUT NOCOPY prisoner_list) 
IS
BEGIN
   illinois_inhumanity(1).name := 'Rolando Cruz';
   illinois_inhumanity(1).years_incarcerated := 10;
     
   innocents_on_deathrow(1).name := 'Anthony Porter';
   innocents_on_deathrow(1).years_incarcerated := 17.75;
END;

This program populates the first rows of what seem to be two different variable arrays: illinois_inhumanity and innocents_on_deathrow. Nothing wrong with that, right? Well, let's see how we are going to use this program:

BEGIN
   innocents_on_deathrow.EXTEND;
   add_travesty (innocents_on_deathrow);
   DBMS_OUTPUT.PUT_LINE (innocents_on_deathrow(1).name);  
END;
/

I extend the global innocents_on_deathrow array and then call add_travesty, passing in that variable array. Then I display the name of the person in the first row.

You will see in parmalias.sql that I have two different versions of this block: one that uses NOCOPY for the parameter in add_travesty, and another that relies on the default parameter passing mechanism (by value). When I run these two blocks, I see this result on my screen:

With NOCOPY: Anthony Porter
Without NOCOPY: Rolando Cruz

Why do I get these different results? We'll first analyze the action with the NOCOPY option (see Figure 10.1 ):

1. When I call add_travesty, I pass innocents_on_deathrow as the IN OUT prisoner list. Since it is passed using NOCOPY, any changes made to the array take place immediately.

2. I set the first row of illinois_inhumanity with information about Rolando Cruz.

3. Then I set the first row of innocents_on_deathrow with the information about Anthony Porter. Since I have used NOCOPY, the two arrays are the same, and so illinois_inhumanity is modified as well.

4. When the program ends, no copying takes place and so I see "Anthony Porter" when I display the contents of the innocents_on_deathrow array.

   Figure 10.1: Parameter aliasing with NOCOPY

   

Now let's step through the default processing (no use of NOCOPY):

1. When I call add_travesty, I pass innocents_on_deathrow by value.

2. I set the first row of my formal parameter (a local copy of innocents_on_deathrow) with information about Rolando Cruz.

3. Then I set the first row of my global collection (innocents_on_deathrow itself) with the information about Anthony Porter.

4. When the program ends, any changes made to the formal parameter are copied to the actual parameter, innocents_on_deathrow. This action overwrites the Anthony Porter information. Consequently, after the program is run, I see "Rolando Cruz" when I display the contents of the innocents_on_deathrow array.

Figure 10.2 illustrates the sequence of events without the use of NOCOPY.

Figure 10.2: Parameter aliasing without NOCOPY

You can see that the results of my program depend on the method of parameter passing chosen by the compiler. The compiler might change its mind, when and if circumstances change and a recompile is required. This introduces a level of uncertainty in your application. How can you avoid this problem?

You should be very careful in your use of both the NOCOPY hint and global variables. You are almost always better off passing global data structures through a parameter list rather than referencing them directly within a program unit. When this is not practical, set clear rules about how and when your globals can be used.

10.1.4 Performance Gains with NOCOPY

How much faster might your application run if you use the NOCOPY hint? It will certainly depend very much on your data structures: the number of rows in your collections, the size of your records, and so on.

I put together a test comparing the management of an index-by table of records with NOCOPY and the default by-value passing method. Here is the procedure, combining both versions within the [ ] brackets to save some space (see nocopy3.tst for the full script):

/* Filename on companion disk: nocopy3.tst */
PROCEDURE pass_by_[value | ref ] (
   emps IN OUT [NOCOPY] emp_tabtype,
   raise_err IN BOOLEAN := FALSE)
IS
BEGIN
   FOR indx IN emps.FIRST .. emps.LAST
   LOOP
      emps(indx).last_name := RTRIM (emps(indx).last_name || ' ');
      emps(indx).salary := emps(indx).salary + 1;
   END LOOP;
   IF raise_err THEN RAISE VALUE_ERROR; END IF;
END;

Notice that I have set the procedure up to run so that I can allow it to end successfully or terminate with an unhandled exception. I ran these programs in a variety of ways, one of which is shown below:

PLVtmr.capture;
BEGIN
   FOR indx IN 1 .. num
   LOOP
      pass_by_[value | ref ] (emptab, TRUE);
   END LOOP;
EXCEPTION
   WHEN OTHERS THEN 
      PLVtmr.show_elapsed ('By value raising error ' || num);
END;

Here are the results of executing the script 10 and then 100 times (with correspondingly larger volumes of data in the index-by table:

By value no error 10 Elapsed: .65 seconds.
NOCOPY no error 10 Elapsed: .06 seconds.

By value raising error 10 Elapsed: .03 seconds.
NOCOPY raising error 10 Elapsed: .01 seconds.

By value no error 100 Elapsed: 317.78 seconds.
NOCOPY no error 100 Elapsed: 6.67 seconds.

By value raising error 100 Elapsed: 1.57 seconds.
NOCOPY raising error 100 Elapsed: .07 seconds.

As you can see, there is a significant improvement in performance with NOCOPY, especially for the largest-scale test. I have run other tests, with less dramatic gains (see nocopy2.tst ); you can easily modify my test scripts to test your own code to verify the impact of NOCOPY in your application.