Home » SQL & PL/SQL » SQL & PL/SQL » DIANA Nodes. PLS-00123: program too large.
DIANA Nodes. PLS-00123: program too large. [message #260765] Mon, 20 August 2007 20:40 Go to next message
TimJF
Messages: 11
Registered: August 2007
Junior Member
Hello everyone,

To give you some background, I have developed a PL/SQL which is a rather large (ugly) thing. It contains approximately 60 procedures/functions. Unfortunately, I am unable to actually store this procedures, as I am just a lowly user of our 10g Oracle database.

Due to it's excessive size, I am now encountering the error message:
 
ORA-06550: line 7729, column 23:
PLS-00123: program too large (Diana nodes)


Fortunately for me, there is plenty of available documentation with common-sense solutions, which I have since implemented. (ie. multiple anonymous blocks, stored the procedures etc)

However, my curiosity isn't satisfied! Do you have any ideas what section(s) from the below Code Statistics Summary would be the most significant indicators of the program being too large? Any ideas or links to relevant documentation would be greatly appreciated.

Code Statistics Summary

Count  Statistic
-----  --------------

   *** Module Analysis
  303  Declarations
  773  Statements
   55  DML statements
   73  Stand-alone procedures
    3  Stand-alone functions
 6621  Code lines
 1418  Blank or comment lines

   *** Oracle Version Dependencies
  167  v7.1

   *** Declaration Analysis
    1  Variables with default value
  170  Variables without default value
    4  Constants
   43  Function declarations
    7  Procedure declarations
    2  Cursor declarations

   *** DML Analysis
   55  Implicit (SELECT INTO ) queries
   18  Maximum number of items in a SELECT list

   *** Cursor Analysis
   74  Cursor FOR loops using immediate SELECT
    2  Explicit cursor OPENs
    2  FETCH
    2  Explicit cursor CLOSEs
   35  Local program unit references to external variables

   *** Flow of Control Analysis
    1  EXIT statements in FOR loops
    5  Maximum number of nested IF levels

   *** Procedure/Function Analysis
    3  Maximum number of RETURN statements in a function
   11  Functions with OUT or IN OUT parameters
   73  Procedures declared without parameters
    7  Functions declared without parameters
    6  Maximum parameters in program units and cursors
   50  Local procedures and functions

   *** Exception Handling Analysis
   48  Exceptions blocks (EXCEPTION...)
  108  Exceptions handlers (WHEN...)
   48  WHEN OTHERS handler
   34  NO_DATA_FOUND
   26  TOO_MANY_ROWS

   *** Built-Ins Utilized
    1  ABS
    1  ADD_MONTHS
  979  CHR
   12  COUNT
   13  DECODE
    2  DUAL
    2  FLOOR
    4  INSTR
    3  LPAD
    3  LTRIM
   98  MAX
   15  MIN
    1  MONTHS_BETWEEN
   37  NULL
   41  NVL
    4  ROUND
   10  ROWNUM
    3  RPAD
   86  RTRIM
   86  SQLERRM
   61  SUBSTR
   13  SUM
  129  SYSDATE
   34  TO_CHAR
   16  TO_DATE
   16  TO_NUMBER
   99  TRUNC
   17  UPPER

   *** Built-In Packages Utilized
    1  DBMS_OUTPUT

   *** Comment Analysis
  589  Significant end-of-line comments
   40  Significant multi line comments
  201  Other comments



[Updated on: Mon, 20 August 2007 20:41]

Report message to a moderator

Re: DIANA Nodes. PLS-00123: program too large. [message #260834 is a reply to message #260765] Tue, 21 August 2007 01:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Are you compiling with PLSQL_DEBUG set to TRUE? There are a couple of bugs involved debugging, I thought.

Are we talking 10g release 1 or release 2? What OS?

MHE
Re: DIANA Nodes. PLS-00123: program too large. [message #260845 is a reply to message #260834] Tue, 21 August 2007 02:00 Go to previous messageGo to next message
TimJF
Messages: 11
Registered: August 2007
Junior Member
Thanks for replying Maaher!

I hadn't heard of PLSQL_DEBUG before, so after you mentioned it I tried the following code:
ALTER SESSION SET PLSQL_DEBUG = TRUE;


Again, I received the same error message so I tried:
ALTER SESSION SET PLSQL_DEBUG = FALSE;


(I read in Oracle documentation that the default is false.)

I'll try to find out if I have 10g release 1 or release 2 and the OS. Do you know any SQL that would help me find this out? Embarassed

Thanks again for replying.

TimJF.

[Updated on: Tue, 21 August 2007 02:01]

Report message to a moderator

Re: DIANA Nodes. PLS-00123: program too large. [message #260852 is a reply to message #260765] Tue, 21 August 2007 02:13 Go to previous messageGo to next message
TimJF
Messages: 11
Registered: August 2007
Junior Member
I googled this:
SELECT * FROM V$VERSION

Result was:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Re: DIANA Nodes. PLS-00123: program too large. [message #260856 is a reply to message #260845] Tue, 21 August 2007 02:24 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
To find out your Oracle version + OS:
SELECT banner 
FROM   v$version
/


Other things you could check:
- the usage of literals in the executable part. It's better to declare them as constants. That way Oracle can use binding.
- the number of anonymous blocks and how deep they are nested.
- Perhaps your code is a candidate for refactoring: try to reuse as much code as possible. If you have parts that are very much alike, have a look whether they can be rewritten as procedures/functions with parameters.

MHE
Re: DIANA Nodes. PLS-00123: program too large. [message #260857 is a reply to message #260852] Tue, 21 August 2007 02:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You learn something new Every day. Wink


http://download-uk.oracle.com/docs/cd/B14117_01/appdev.101/b10807/e_limits.htm

Quote:

PL/SQL is based on the programming language Ada. As a result, 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.

In the shared memory pool, a package spec, object type spec, standalone subprogram, or anonymous block is limited to 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, some of which are given in Table E-1.



Were you anywhere near 6 million lines of code?

Re: DIANA Nodes. PLS-00123: program too large. [message #260858 is a reply to message #260857] Tue, 21 August 2007 02:29 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Like I said, Thomas, there are a couple of bugs related to this error. Tim, if you have access I'd check metalink.oracle.com for more information.

MHE
Re: DIANA Nodes. PLS-00123: program too large. [message #260859 is a reply to message #260857] Tue, 21 August 2007 02:31 Go to previous messageGo to next message
TimJF
Messages: 11
Registered: August 2007
Junior Member
Haha no mate! Only about 8,000 lines of code. I'm guessing I exceeded the memory limits somewhere else in the program.

My hours upon hours of research led me to an article regarding the ADA language. Apparently it was implicated when a rocket exploded on a launch pad? Something to do with dodgy exception handling syntax. The PLSQL derivative seems to work just fine for me, however! Razz
Re: DIANA Nodes. PLS-00123: program too large. [message #260860 is a reply to message #260858] Tue, 21 August 2007 02:32 Go to previous messageGo to next message
TimJF
Messages: 11
Registered: August 2007
Junior Member
Maaher wrote on Tue, 21 August 2007 17:29
Like I said, Thomas, there are a couple of bugs related to this error. Tim, if you have access I'd check metalink.oracle.com for more information.

MHE


Thanks Maaher, I'll get onto that metalink.oracle.com site and have a good read. No doubt theres plenty of other interesting stuff there too. Cool
Re: DIANA Nodes. PLS-00123: program too large. [message #260867 is a reply to message #260860] Tue, 21 August 2007 02:54 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

The PLSQL derivative seems to work just fine for me, however!



As long as anyone carefully debugs those "select target from ...." statements when programming rockets. Laughing
Previous Topic: How To recall old data
Next Topic: Early Binding/Late Binding concept
Goto Forum:
  


Current Time: Mon Dec 05 12:45:02 CST 2016

Total time taken to generate the page: 0.05961 seconds