PL/SQL Architecture

PL/SQL architecture defines how the PL/SQL blocks are executed in Oracle. Oracle provides PL/SQL engine that executes the Procedural Language part of a block.

When an application program or a user invokes a PL/SQL block, it is submitted to the PL/SQL engine available locally. PL/SQL engine extracts the Procedural Language statements of the block. These statements are then executed separately by the PL/SQL processor. The SQL statements in the block are passed to the SQL processor of Oracle server. These are separately executed.

Here is the diagram showing architecture of PL/SQL

Features of PL/SQL Architecture

• The first feature is PL/SQL compilation. PL/SQL engine works as a run-time system. It is assigned the task of compilation and execution of PL/SQL blocks submitted to server by users or application programs.
• It is a separate component that can be installed in the database. It can also be installed as an application development tool for specific application needs.
• PL/SQL engine works as a compiler for PL/SQL block. If a block is submitted with PL/SQL errors they are reported by the PL/SQL Engine.  A PL/SQL block is not executed  until all errors are removed.
• The engine identifies only Procedure Language statements and sends the SQL statements to the SQL engine. If a PL/SQL block is coded with only PL/SQL statements, it is not sent to the server. The local PL/SQL engine processes the PL/SQL block and returns the result to user.  This way a trip to server is avoided.
• If the application contains a combination of PL/SQL and SQL as a block, it is first given to local PL/SQL engine. Doing such coding in programs and applications helps in taking advantage of conditional and iterative control of PL/SQL. These blocks are processed by the PL/SQL processor in Local Engine and the SQL statements are sent to the database server. Oracle Server SQL processor sends the processed statements back and they are combined with the output from PL/SQL local server. This way the traffic between the server and client/application is reduced significantly by grouping SQL statements as a PL/SQL block.

admin