DIANAs Who Grew Too Much

Sometimes, large PL/SQL programs may encounter the server error PLS-00123: Program too large, or on the client side, CDI-11005: (SQL execution error) line %1: End of line in string literal. This means that the compiler, while attempting to create the DIANA parse tree, bumped into the maximum allowed number of "nodes" in the tree. The normal workaround for this error is to split the program up into several smaller programs. Sometimes, however, you just don't want to do that.

It's difficult to predict how many nodes a program will need because nodes don't directly correspond to anything easily measurable, such as tokens or lines of code. However, Table 20-2 shows the upper limits for different server versions and different types of PL/SQL programs. While the rightmost column purports to give an estimate of source code size, don't take it too literally. It's based on "typical" code that averages four bytes of source per DIANA node, but your code might not be typical.

Table 20-2. Some upper limits on PL/SQL program size
PL/SQL program types	Server version	Maximum size of DIANA parse tree (nodes)	Oracle's estimate of maximum size of source code (bytes)
Package and type bodies, standalone functions and procedures	7.3	214 (16,384)	64K
	8.0.x and below	215 (32,768)	128K
	8.1.5 and above	226 (67,108,864)	256M
Signature (header) of standalone functions and procedures	7.3	214	64K
	8.0.x and above	215	128K
Package and type specifications, anonymous blocks	7.3	Theoretical limit: 214	64K in theory
	8.1.6 and below	215 in theory, between 213 and 214 in practice	128K in theory, 32K to 64K in practice
	8.1.7 and above	215	128K

You can see from the table that Oracle has been working over the years to increase the limits. With the newer releases, it is fairly rare to run out of DIANA nodes.

As an aside, there are other documented limits in the PL/SQL compiler, such as the maximum number of levels ofblock nesting (255) and the number of parameters you can pass to aprocedure or function (65,536). Few of these are likely to cause a problem, but you can find a complete list of them in an appendix of Oracle's official PL/SQL documentation, PL/SQL User's Guide and Reference.

20.2 Dependency Management

Another important phase of PL/SQL compilation and execution is the checking of program dependencies, defined as follows. A dependency (in PL/SQL) is a usage relationship between a program and some Oracle object outside the program. Server-based PL/SQL programs can have dependencies on tables, views, types, procedures, functions, sequences, or package specifications, but not on package bodies or type bodies. Client-based PL/SQL programs can have additional dependencies on items such as form fields that exist only in the client-side module.

Oracle's basic dependency objective for PL/SQL is, loosely speaking:

Do not allow a program to run if any of the objects on which it depends have changed since it was compiled.

The good news is that most dependency management happens automatically, from the tracking of dependencies to the recompilation required to keep everything synchronized. You can't completely ignore this topic, though, and the following sections should help you understand how, when, and why you'll need to intervene.

20.2.1 Dependencies in Server-Side PL/SQL

If you're working with server-side PL/SQL programs, you can use the server's data dictionary to explore usage relationships in quite a bit of detail.

Here's a simple illustration of this rule in action, using the data dictionary to give us eyes into the database. Let's say that I have a package named bookworm on the server. In this package is a function that selects from the books table. If I create the table and then create the package, I expect to see the following:

SQL> SELECT object_name, object_type, status 2 FROM USER_OBJECTS 3 WHERE object_name = 'BOOKWORM'; OBJECT_NAME OBJECT_TYPE STATUS------------------------------ ------------------ -------BOOKWORM PACKAGE VALIDBOOKWORM PACKAGE BODY VALID

That is, there are two objects with the name BOOKWORM; the first is the package spec and the second is the body. Right now, they're both VALID.

Behind the scenes, Oracle has used its DIANA to determine a list of other objects that BOOKWORM needs in order to compile successfully. I can explore this dependency graph using a somewhat expensive (that is, slow) query of the data dictionary viewUSER_DEPENDENCIES:

SQL> SELECT name, type, referenced_name, referenced_type 2 FROM USER_DEPENDENCIES 3 WHERE name = 'BOOKWORM'; NAME TYPE REFERENCED_NAME REFERENCED_TYPE--------------- -------------- --------------- ---------------BOOKWORM PACKAGE STANDARD PACKAGEBOOKWORM PACKAGE BODY STANDARD PACKAGEBOOKWORM PACKAGE BODY BOOKS TABLEBOOKWORM PACKAGE BODY BOOKWORM PACKAGE

Figure 20-5 illustrates this information as a directed graph, where the arrows indicate a "depends-on" relationship.