10. More Goodies for Oracle8i PL/SQL Developers

Contents:
The NOCOPY Parameter Mode Hint
Calling Packaged Functions in SQL
SQL99 Compliance
SQL Operations on Collections
Miscellaneous and Minor Improvements

I've already covered a veritable cornucopia of new features available for PL/SQL developers in Oracle8 i . Even if that were all that Oracle8 i offered to developers, we would all be very happy -- and very busy learning how to use all the new stuff. Yet there is still more! This chapter covers other features that improve either the performance or the usability of PL/SQL in Oracle8 i :

The NOCOPY compiler hint: You can avoid the overhead of copying IN OUT parameter values with this enhancement. When you are working with large collections and records, NOCOPY can have a noticeable impact on program performance.
Calling PL/SQL functions from SQL: Oracle8 i offers some big relief for PL/SQL developers when it comes to calling their own functions: you no longer have to use the RESTRICT_REFERENCES pragma! Oracle8 i also offers two new keywords, DETERMINISTIC and PARALLEL ENABLE, to help you integrate your PL/SQL, C, and Java code into all aspects of your database.
SQL99 compliance: Oracle8 i adds or expands the TRIM and CAST operators to better support the SQL99 standard.
SQL operations on collections: Oracle8 i makes it even easier to integrate PL/SQL (transient) collections into SQL statements.

I also review transparent improvements, that is, changes to the language that improve the performance or behavior of your PL/SQL-based applications without necessitating any modifications to your code.

10.1 The NOCOPY Parameter Mode Hint

PL/SQL 8.1 offers a new option for definitions of parameters: the NOCOPY clause. NOCOPY is a hint to the compiler about how you would like the PL/SQL engine to work with the data structure being passed in as an OUT or IN OUT parameter. To understand NOCOPY and its potential impact, it will help to review how PL/SQL handles parameters. Let's start with some definitions:

Formal parameter: The parameter defined in the parameter list and used in the program.
Actual parameter: The actual expression or variable passed to the program when it is called.
By reference: When an actual parameter is passed by reference , it means that a pointer to the actual parameter is passed to the corresponding formal parameter. Both the actual and formal parameters then reference, or point to, the same location in memory that holds the value of the parameter.
By value: When an actual parameter is passed by value , the value of the actual parameter is copied into the corresponding formal parameter. If the program then terminates without an exception, the formal parameter value is copied back to the actual parameter. If an error occurs, the changed values are not copied back to the actual parameter.

Parameter passing in PL/SQL (without the use of NOCOPY) follows the rules shown in the following table.

Parameter Mode	Passed by Value or Reference? (Default Behavior)
IN	By reference
OUT	By value
IN OUT	By value

We can infer from all of these definitions and rules that when you pass a large data structure (such as a collection, record, or instance of an object type) as an OUT or IN OUT parameter, your application could experience performance and memory degradation due to all of this copying.

PL/SQL 8.1 offers the NOCOPY hint as a way for you to attempt to avoid this copying. The syntax of this feature is as follows:


parameter_name
 [ IN | IN OUT | OUT | IN OUT NOCOPY | OUT NOCOPY ] 
parameter_datatype

You can specify NOCOPY only in conjunction with the OUT or IN OUT mode. Here, for example, is a parameter list that uses the NOCOPY hint for both of its IN OUT arguments:

PROCEDURE analyze_results (    date_in IN DATE,    values IN OUT NOCOPY numbers_varray,    validity_flags IN OUT 

NOCOPY

 validity_rectype    );

Remember that NOCOPY is a hint, not a command. This means that the compiler might silently decide that it cannot fulfill your request for a NOCOPY parameter treatment. The next section lists the restrictions on NOCOPY that might cause this to happen.

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 ):

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.
I set the first row of illinois_inhumanity with information about Rolando Cruz.
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.
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):

When I call add_travesty, I pass innocents_on_deathrow by value.
I set the first row of my formal parameter (a local copy of innocents_on_deathrow) with information about Rolando Cruz.
Then I set the first row of my global collection (innocents_on_deathrow itself) with the information about Anthony Porter.
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.