How Does Oracle Execute PL/SQL Code?

Before I explore how an Oracle database executes PL/SQL programs, I first need to define a couple of terms of art:[26]

PL/SQL runtime engine (a.k.a. PL/SQL Virtual Machine)

The PL/SQL Virtual Machine (PVM) is the database component that executes a PL/SQL program’s bytecode. In this virtual machine, the bytecode of a PL/SQL program is translated to machine code that makes calls to the database server and returns results to the calling environment. The PVM itself is written in C. Historically, Oracle has included a PVM in some client-side tools such as Oracle Forms, where the runtime engine opens a session to a remote database, communicating with the SQL engine over a networking protocol.

Database session

For most (server-side) PL/SQL, the database session is the process and memory space associated with an authenticated user connection. Each session has its own memory area where it can hold an executing program’s data. Sessions begin with logon and end with logoff. The sessions connected to a database are visible through the view V$SESSION.

To put these terms into context, let’s take a look at several variations on running a trivial program from a very common frontend, SQL*Plus. This is a good representative of a session-oriented tool that gives you direct access to the PL/SQL environment inside the database server. (I introduced SQL*Plus and showed how to use it with PL/SQL back in Chapter 2.) Of course, you may be calling the server from other tools ...

Get Oracle PL/SQL Programming, 5th Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.