JunkYardDog
Technical User
is there a maximum number of lines that a pl/sql block can be? i've heard that only ~2500 executable lines of code can be intrepreted by the compiler? anyone have experiece whith this?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
max lines for pl/sql block
- Thread starter JunkYardDog
- Start date
- Status
Here is what the PL/SQL manual says:
"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), which is a tree-structured intermediate language. It is defined using a meta-notation called Interface Definition Language (IDL). DIANA provides for communication internal to 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 pool, a package spec, object type spec, stand-alone subprogram, or anonymous block is limited to 64K DIANA nodes. The nodes correspond to tokens such as identifiers, keywords, operators, and so on. The m-code is limited to 64K compiler-generated temporary variables.
To estimate how much memory a program unit requires, you can query the data dictionary view user_object_size. The column parsed_size returns the size (in bytes) of the "flattened" DIANA.
SQL> SELECT * FROM user_object_size WHERE name = 'PKG1';"
I would think you would want to "modularize" your code through the use of packages,procedures, and/or functions rather than deal with a very large PL/SQL block.
"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), which is a tree-structured intermediate language. It is defined using a meta-notation called Interface Definition Language (IDL). DIANA provides for communication internal to 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 pool, a package spec, object type spec, stand-alone subprogram, or anonymous block is limited to 64K DIANA nodes. The nodes correspond to tokens such as identifiers, keywords, operators, and so on. The m-code is limited to 64K compiler-generated temporary variables.
To estimate how much memory a program unit requires, you can query the data dictionary view user_object_size. The column parsed_size returns the size (in bytes) of the "flattened" DIANA.
SQL> SELECT * FROM user_object_size WHERE name = 'PKG1';"
I would think you would want to "modularize" your code through the use of packages,procedures, and/or functions rather than deal with a very large PL/SQL block.
- Thread starter
- #3
JunkYardDog
Technical User
Thanks,That helps, i'm with you on the modularization of code but an old cobol programmer is working on this particular script. Is there any way to tell the size of the program since it is a standalone pl/sql block and not stored?
Thanks
Thanks
Is there any reason you can't just run your code and see if you get a size limit error? That would be pretty conclusive evidence that your block needs to be modularized.
Conversely, if it runs successfully you should be able to find the size by querying v$sqlarea.
Conversely, if it runs successfully you should be able to find the size by querying v$sqlarea.
- Status