Who is diana, and why won't she let my database objects compile?

Question

OK, so the question title is a little tongue-in-cheek, but the question is serious enough. Occasionally, when compiling the objects in a schema or importing a dump file, I see the following error message:

ORA-04028: cannot generate diana for object SCOTT.VW_EMP

what does this actually mean, and how can I avoid it?

Answer 1

Relevant here: PL/SQL, Package Size, Parse Tree Nodes, Lines of Code.

Diana is Oracle’s interface definition language to represent the structure of database tables and the logic of PL/SQL program units as attributed trees.

There are internal limits on the number of parse tree nodes. Compiler version sets the maximum lines of code.

So check the size of PL/SQL logic and the lines of code. It may not be possible, or even necessary to know the actual limits that your version can handle.

Once you know where to right size your package, half the problem is solved.

Let us know when you have solved the other half, too, thanks.

Answer 2

Can you please share your snippet of code where you are getting the error.

Here is description which might help you understand why you are getting the error : PL/SQL is based on a programming language called ADA. so when ever you write a pgram in PL/SQL it generats a "DIANA" -> Descriptive Intermediate Attributed Notation for Ada , a tree-structured intermediate language. DIANA is used internally by compilers and other tools.

How it works : 1) At compile time, PL/SQL source code is translated into system code and generates corresponding DIANA.

2)Both the DIANA and system code for a subprogram or package are stored in the database.

3)At run time, they are loaded into the shared memory pool.

4)The DIANA is used to compile dependent subprograms; more specifically to check/validate that the sub program is still valid. this is required because as we know that a sub-program can use database objects such as Tables,Views,Synonyms or other stored procs. it could be possible that the the objects may have changed/removed/droped when next time you run the program. For ex : some one might have droped the table, the stored proc or function singnature may have changed.

5) Once the validation is done using DIANA, the system code simply runs.

Restriction on your program :

In the shared memory pool, a package specification, ADT specification, standalone subprogram, or anonymous block is limited to 67108864 (2**26) DIANA nodes which correspond to tokens such as identifiers, keywords, operators, and so on. This allows for ~6,000,000 lines of code unless you exceed limits imposed by the PL/SQL compiler

you can refer to this link : http://docs.oracle.com/cd/E14072_01/appdev.112/e10472/limits.htm#

Now coming to your issue - ora-04028 :

It could be due one of the following reason :

1)There are some bugs which gives this error when you select from a view in which a function is called which also selects from the same view

2)The database server, client or rman catalog are not on an adequate version. You will need to patch

3)You try to register an Oracle 11g instance to a 10.2.0.1 RMAN catalog. In order for this to succeed, upgrade the catalog to at least version 10.2.0.3

Answer 3

As per Oracle documentation,

PL/SQL is based on the programming language Ada. PL/SQL uses a variant of Descriptive Intermediate Attributed Notation for Ada (DIANA), a tree-structured intermediate language. It is defined using a meta-notation called Interface Definition Language (IDL). DIANA is used internally by compilers and other tools.

At compile time, PL/SQL source code is translated into machine-readable m-code. Both the DIANA and m-code for a procedure or package are stored in the database. At run time, they are loaded into the shared memory pool. The DIANA is used to compile dependent procedures; the m-code is simply executed.

Unfortunately, you cannot estimate the number of DIANA nodes from the parsed size. Two program units with the same parsed size might require 1500 and 2000 DIANA nodes, respectively because, for example, the second unit contains more complex SQL statements.

Ask tom says

More on DIANA node calcualtions, read this book "Ada-Europe '93: 12th Ada-Europe International Conference, "Ada Sans Frontieres", Paris, France, June 14-18, 1993. Proceedings"

The following support note covers this topic well...

Article-ID:         <Note:62603.1>
Folder:             PLSQL
Topic:              General Information Articles
Title:              'PLS-123 Program too Large' - Size Limitations on PLSQL 
                    Packages
Document-Type:      BULLETIN
Impact:             MEDIUM
Skill-Level:        NOVICE
Server-Version:     07 to 08
Updated-Date:       13-JUN-2000 17:41:01
References:         

Overview

This article contains information on PL/SQL package size limitations. When limits are reached, you receive the following error:

PLS-123 Program too large

Size Limitations on PL/SQL Packages

In releases prior to 8.1.3, large programs resulted in the PLS-123 error. This occurred because of genuine limits in the compiler; not as a result of a bug.

When compiling a PL/SQL unit, the compiler builds a parse tree. The maximum size of a PL/SQL unit is determined by the size of the parse tree. A maximum number of diana nodes exists in this tree.

Up to 7.3, you could have 2 * * 14 (16K) diana nodes, and from 8.0 to 8.1.3, 2 * * 15 (32K) diana nodes were allowed. With 8.1.3, this limit has been relaxed so that you can now have 2 * * 26 (i.e., 64M) diana nodes in this tree for package and type bodies.

Source Code Limits

While there is no easy way to translate the limits in terms of lines of source code, it has been our observation that there have been approximately 5 to 10 nodes per line of source code. Prior to 8.1.3, the compiler could cleanly compile up to about 3,000 lines of code.

Starting with 8.1.3, the limit was relaxed for package bodies and type bodies which can now have approximately up to about 6,000,000 lines of code.

Notes: This new limit applies only to package bodies and type bodies. Also, you may now start hitting some other compiler limits before you hit this particular compiler limit.

In terms of source code size, assume that tokens (identifiers, operators, functions, etc.), are on average four characters long. Then, the maximum would be:

   Up to 7.3:         4 * (2 * * 14)=64K
   From 8.0 to 8.1.3: 4 * (2 * * 15)=128K
   With 8.1.3:        4 * (2 * * 25)=256M

This is a rough estimate. If your code has many spaces, long identifiers, etc., you may end up with source code larger than this. You may also end up with source code smaller than this if your sources use very short identifiers, etc.

Note that this is per program unit, so package bodies are most likely to encounter this limit.

How to Check the Current Size of a Package

To check the size of a package, the closest related number you can use is PARSED_SIZE in the data dictionary view USER_OBJECT_SIZE. This value provides the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables and is NOT the size in the shared pool.

The size of the DIANA portion of PL/SQL code (used during compilation) is MUCH bigger in the shared pool than it is in the system table.

For example, you may begin experiencing problems with a 64K limit when the PARSED_SIZE in USER_OBJECT_SIZE is no more than 50K.

For a package, the parsed size or size of the DIANA makes sense only for the whole object, not separately for the specification and body.

If you select parsed_size for a package, you receive separate source and code sizes for the specification and body, but only a meaningful parsed size for the whole object which is output on the line for the package specification. A 0 is output for the parsed_size on the line for the package body.

The following example demonstrates this behaviour:

CREATE OR REPLACE PACKAGE example AS  
  PROCEDURE dummy1;  
END example;  
/  
CREATE OR REPLACE PACKAGE BODY example AS  
  PROCEDURE dummy1 IS  
  BEGIN  
    NULL;  
  END;  
END;  
/  

SQL> start t1.sql;  

Package created.  


Package body created.  

SQL> select parsed_size from user_object_size where name='EXAMPLE';  


PARSED_SIZE  
-----------  
        185  
          0  


SQL> select * from user_object_size where name='EXAMPLE';  

  .....

Oracle stores both DIANA and MCODE in the database. MCODE is the actual code that runs, while DIANA for a particular library unit X contains information that is needed to compile procedures using library unit X.

The following are several notes:

a) DIANA is represented in IDL. The linear version of IDL is stored on disk. The actual parse tree is built up and stored in the shared pool. This is why the size of DIANA in the shared pool is typically larger than on disk.

b) DIANA for called procedures is required in the shared pool only when you create procedures. In production systems, there is no need for DIANA in the shared pool (but only for the MCODE).

c) Starting with release 7.2, the DIANA for package bodies is thrown away, not used, and not stored in the database. This is why the PARSED_SIZE (i.e. size of DIANA) of PACKAGE BODIES is 0.

Therefore, large procedures and functions should always be defined within packages!

A package is stored in DIANA in the database, just like a procedure. A package can be used to break the dependency chain however, perhaps making this go away. It is my belief that ALL production (real) code should be in a package, never in a standalone procedure or function.

Answer 4

There is a bug in Oracle 10.2.5 (Unpublished Bug 9342254; see Doc ID 1505092.1) for which there is a fix Oracle 11.1 and later; I think this may have been the issue at play here.

You can work round it by issuing a flush of the shared pool:

ALTER SYSTEM FLUSH SHARED_POOL

NB. This is a very old question, but I leave this here in case anyone else comes across the question. Ninesided and I actually work for the same company, and came across this independently.