Oracle7 Server Concepts

How Oracle Stores Procedures and Packages

When you create a procedure or package, Oracle automatically performs these steps:

1. Compiles the procedure or package.

2. Stores the compiled code in memory.

3. Stores the procedure or package in the database.

Compiling Procedures and Packages

The PL/SQL compiler compiles the source code. The PL/SQL compiler is part of the PL/SQL engine contained in Oracle. If an error occurs during compilation, a message is returned. Information on identifying compilation errors is contained in the Oracle7 Server Application Developer's Guide.

Storing the Compiled Code in Memory

Oracle caches the compiled procedure or package in the shared pool of the SGA. This allows the code to be executed quickly and shared among many users. The compiled version of the procedure or package remains in the shared pool according to the modified least-recently-used algorithm used by the shared pool, even if the original caller of the procedure terminates his/her session. See "The Shared Pool"

for specific information about the shared pool buffer.

Storing Procedures or Packages in Database

At creation and compile time, Oracle automatically stores the following information about a procedure or package in the database:

object name Oracle uses this name to identify the procedure or package. You specify this name in the CREATE PROCEDURE, CREATE FUNCTION, CREATE PACKAGE, or CREATE PACKAGE BODY statement.
source code and parse tree The PL/SQL compiler parses the source code and produces a parsed representation of the source code, called a parse tree.
pseudocode (P code) The PL/SQL compiler generates the pseudocode, or P code, based on the parsed code. The PL/SQL engine executes this when the procedure or package is invoked.
error messages Oracle might generate errors during the compilation of a procedure or package.

To avoid unnecessary recompilation of a procedure or package, both the parse tree and the P code of an object are stored in the database. This allows the PL/SQL engine to read the compiled version of a procedure or package into the shared pool buffer of the SGA when it is invoked and not currently in the SGA. The parse tree is used when the code calling the procedure is compiled.

All parts of database procedures are stored in the data dictionary (which is in the SYSTEM tablespace) of the corresponding database. The database administrator should plan the size of the SYSTEM tablespace, keeping in mind that all stored procedures require space in this tablespace.

object name	Oracle uses this name to identify the procedure or package. You specify this name in the CREATE PROCEDURE, CREATE FUNCTION, CREATE PACKAGE, or CREATE PACKAGE BODY statement.
source code and parse tree	The PL/SQL compiler parses the source code and produces a parsed representation of the source code, called a parse tree.
pseudocode (P code)	The PL/SQL compiler generates the pseudocode, or P code, based on the parsed code. The PL/SQL engine executes this when the procedure or package is invoked.
error messages	Oracle might generate errors during the compilation of a procedure or package.