oracle-developer.net

pls-00436 removed in oracle 11g

Since its release in Oracle 8i, there has been a frustrating restriction with FORALL: the PLS-00436 "implementation restriction". Readers who are familiar with FORALL will be aware of PLS-00436. It is the implementation restriction that prevents us from accessing specific record attributes within a FORALL DML construct. One of the first things I check whenever there is a new major release of Oracle is whether this restriction has been removed. With the release of 11g, it has (finally) and this short article will demonstrate the techniques that are now available to us.

Readers who are less familiar with this restriction (or wish to recap), should read this oracle-developer.net article for an overview, including suggested workarounds.

a simple example

Using the examples from the article referenced above, we will perform a simple update on a column in EMP using FORALL. This is for demonstration purposes only: under normal circumstances the code would be sufficiently complex to warrant the use of PL/SQL over SQL in the first place. The example is as follows.

In versions prior to 11g, both lines 16 and 17 would raise PLS-00436 because they reference attributes within the EMP record variable (or to be precise, attributes of each EMP record element within the associative array). As we can see from the above, however, this restriction has gone.

implications for inserts

The removal of the PLS-00436 restriction simplifies several areas of PL/SQL FORALL coding, including inserts. As discussed in the referenced background article, FORALL INSERTs that involve a partial set of target columns can be achieved in previous versions using a record-based insert into an in-line view over the target table (an 8i alternative would be to have an array for each target column). The relaxation of PLS-00436 removes the need for such coding techniques, however. We can now use a simple VALUES () list, as we will see below.

In the following example, we will create a copy of the EMP table and populate it with a subset of columns. First, we create the table as follows.

implications for row-based updates

Oracle 9i introduced record-based inserts and updates for PL/SQL (described here). Record-based inserts are convenient when dealing with entire target records or as a workaround to PLS-00436 (prior to 11g of course). Record-based updates, defined by the SET ROW syntax, have proved to be less useful with FORALL because of PLS-00436. One of the reasons is that we cannot constrain the updates without additional arrays to store the keys for each target update (details and examples of this are available in the article referenced above).

The removal of the PLS-00436 restriction means that the SET ROW syntax is more accessible than in previous versions. We still have the issue of updates to all columns, including the primary key (although a workaround to this is described here). The following short example demonstrates the technique, though for simplicity we haven't included the workaround that prevents the primary key from being updated.

In versions prior to 11g, the inclusion of the predicate in line 17 needed to be served by a separate array. 11g makes this much simpler.

a final consideration

One of the workarounds to the PLS-00436 restriction in previous versions was to use SQL object types instead of PL/SQL record types and use the TREAT function to access the individual attributes. The workaround article (referenced earlier) notes that the object type method is comparable in performance to the alternatives available at that time. Now that the PLS-00436 restriction has been removed, we will compare the workaround to the new, simpler syntax. To enable us to do this, we will create an EMP_LARGE table with 100,000 rows, as follows.

We will now compare the time it takes to simply update each column in EMP_LARGE with itself, using both the workaround syntax and the newly-supported syntax. We will use a version of Tom Kyte's RUNSTATS utility to compare the time and resources used by each approach. We will begin with the workaround method, as follows.

When compared with the newly-supported syntax, the inherent cost of using object types in the workaround becomes apparent (note that there is no direct evidence above to support this statement: just several years of experience using object types in various performance scenarios). In the above example, the new method ran in just over half the time of the workaround method. The object method had slightly more physical I/O to do, but in repeated runs of the comparison, the same results appear, with the workaround taking over twice as long in some comparisons.

further reading

Several references to oracle-developer.net articles have been provided throughout this article. To recap, workarounds to PLS-00436 in previous versions of Oracle are available here. Workarounds to avoid updated primary key columns with SET ROW are available here. An overview of FORALL is available in this article and additionally in the official PL/SQL Language Reference. The version of RUNSTATS used in this article is available here.

source code

The source code for the examples in this article can be downloaded from here.