Architecture of PLSQL

PL/SQL Architecture includes below:

■ PL/SQL Engine

■ PL/SQL Units and Compilation Parameter

PL/SQL Engine

The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL units. The engine can be installed in the database or in an application development tool, such as Oracle Forms.

In either environment, the PL/SQL engine accepts as input any valid PL/SQL unit. The engine executes procedural statements, but sends SQL statements to the SQL engine in the database, as shown in below:

PLSQL Engine

Typically, the database processes PL/SQL units. When an application development tool processes PL/SQL units, it passes them to its local PL/SQL engine. If a PL/SQL unit contains no SQL statements, the local engine processes the entire PL/SQL unit. This is useful if the application development tool can benefit from conditional and iterative control.

For example, Oracle Forms applications frequently use SQL statements to test the values of field entries and do simple computations. By using PL/SQL instead of SQL, these applications can avoid calls to the database.

PL/SQL Units and Compilation Parameters

A PL/SQL unit is any one of the following:

■ PL/SQL block

■ FUNCTION

■ PACKAGE

■ PACKAGE BODY

■ PROCEDURE

■ TRIGGER

■ TYPE

■ TYPE BODY

PL/SQL units are affected by PL/SQL compilation parameters (a category of database initialization parameters). Different PL/SQL units—for example, a package specification and its body—can have different compilation parameter settings. For more information about these parameters, see Oracle Database Reference. To display the values of these parameters, use the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS. For more information about this view, see Oracle Database Reference.

The compile-time values of most of the parameters are stored with the metadata of the PL/SQL unit, which means you can reuse those values when you explicitly recompile the program unit by doing the following:

1.    Use one of the following statements to recompile the program unit:

■ ALTER FUNCTION COMPILE

■ ALTER PACKAGE COMPILE

■ ALTER PROCEDURE COMPILE

2.    Include the REUSE SETTINGS clause in the statement. This clause preserves the existing settings and uses them for the recompilation of any parameters for which values are not specified elsewhere in the statement.

If you use the SQL statements CREATE OR REPLACE to explicitly compile a PL/SQL subprogram, or if you do not include the REUSE SETTINGS clause in the ALTER COMPILE statement, then the value of the compilation parameter is its value for the session.