Oracle PL/SQL Programming, Third Edition

The Processing of Server-Side PL/SQL

To restate and amplify some of the main points covered so far, I’d like to enumerate the steps Oracle takes when you use a program such as SQL*Plus to submit PL/SQL to the server.

Compiling an Anonymous Block

Here are the major steps involved in compiling an anonymous block.

1. The PL/SQL compiler parses the code to check its syntax. If the code does not pass the syntax check, stop here and return a compile error to the calling environment.

2. The Oracle server determines whether valid parsed and executable versions of this block already exist in the library cache. It does this by computing the hash value of the text of the block, then by doing textual searches of potential matches using something like a memcmp, followed by a series of checks to make sure that the server environment hasn’t changed. If it is OK to re-execute the block, skip to Step 1 of execution.

3. If the block is not already in the library cache, Oracle passes the block to the PL/SQL compiler.

4. The PL/SQL compiler resolves the names of PL/SQL identifiers, but sends any embedded SQL statements off to the SQL parser.

5. The SQL parser does its own syntax check, name resolution, and semantics checks, but calls back to PL/SQL to resolve any remaining identifiers (such as bind variables).

6. At the time of compilation, Oracle checks to make sure that the user who is compiling the code has privileges to (a) execute any referenced programs, and (b) perform the requested operations on SQL statements ...