Section 23.6.  Server-Side PL/SQL Processing: Reprise

23.6. Server-Side PL/SQL Processing: Reprise To review and amplify some of the main points covered in the chapter, I'd like to enumerate the steps Oracle takes when you send PL/SQL to the database server. 23.6.1. Compiling an Anonymous Block Here are the major steps involved in compiling an anonymous block: The first phase is tokenizingthat is, reading the program and splitting it into atomic units such as keywords, operators, and identifiers. The PL/SQL preprocessor[] tests the tokenized form of the code for the presence of any conditional compilation directives. If found, the preprocessor evaluates them, makes any needed substitutions in the code, and passes the resulting (possibly unmodified) code to the compiler. [] Beginning with Oracle Database 10g Release 2. The PL/SQL compiler takes the tokenized (and possibly preprocessed) code and performs a syntax check. If the code does not pass the syntax check, compiling will stop here and return a compile error to the calling environment. 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 doing comparisons 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 reexecute the block, skip to Step 1 of execution. If the block is not already in the library cache, Oracle passes the block to the PL/SQL compiler. The PL/SQL compiler resolves the names of PL/SQL identifiers but sends any embedded SQL statements to the SQL parser. 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). 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 on any data structures, such as tables. This is true even when compiling invoker rights programs. Automatic recompilation of invalid referenced objects (even remote procedures) occurs when the PL/SQL compiler encounters the reference to the objects (as described in the earlier section "Dependency Management"). The compiler reads the DIANA of any referenced objects (including remote procedures) in order to validate that the call matches their signature. If the DIANA for a referenced object is not already present in the data dictionary, then it is generated at this point and stored persistently. If compilation succeeds, Oracle creates bytecode and loads it into library cache. The DIANA for the anonymous block is discarded. 23.6.2. Compiling a Stored Object There are some variations on the compilation process for stored objects (package, package body, procedure, function, trigger, type, type body): Before recompiling an existing program, Oracle determines whether it is already running and waits until the run completes before recompiling. Steve Adams points out that this is "because a (S)hare mode KGL pin held during execution, and compilation needs to take that pin in e(X)clusive mode." Watch out, because these pins can cause deadlocks or long-term waits. If the object being compiled is a package with state information, Oracle will tear down this package state and in some cases, the state of other packages in session memory. When you issue a DDL statement such as CREATE OR REPLACE PROCEDURE, Oracle saves the source code in the SOURCE$ table even if compilation ultimately fails. In addition, for each stored program, Oracle stores the current compiler flags in the SETTINGS$ table. Successful compilation causes DIANA to be stored in the database for everything except package bodies and type bodies (even for natively compiled programs). Once the DIANA exists for a stored program, Oracle also saves information about the objects on which the program depends in the DEPENDENCY$ table. If the program uses any remote procedures, at compilation time Oracle retrieves the timestamp and the DIANA-based "signature" of the remote object and saves it with the compiled code of the local program. If the session or database flags are set for native compilation, the PL/SQL compiler still performs the usual preprocessing, syntactic and semantic analysis, and generates the usual mcode. An internal translator then converts the mcode into C and invokes the operating system's C compiler to generate a shared object file. A copy of the shared object file is retained in the NCOMP_DLL$ table (beginning with Oracle Database 10g). If compiling a stored object fails, Oracle makes an entry in the ERROR$ table, which users may see via the USER_ERRORS view. Successful compilation removes any previous error information from the ERROR$ table. As with anonymous blocks, successful compilation loads all forms of compiled code into the library cache; with stored programs, Oracle also saves the DIANA, bytecode, and any debug data in the data dictionary. 23.6.3. Executing PL/SQL Once the bytecode exists in the library cache, Oracle can execute it. Here are some of the steps involved in execution: If the invoker of the program is not its owner, Oracle checks whether the invoker has been granted EXECUTE privilege on the program. If the program is a stored program that has been marked AUTHID CURRENT_USER (invoker rights), the PL/SQL runtime engine re-resolves external references to SQL objects at runtime based on the identity of the invoker. External references to PL/SQL programs are not re-resolved unless they are embedded in an anonymous block. Oracle opens a cursor and associates it with the program, even if it is an anonymous block. If there are any remote procedures, the PL/SQL runtime engine compares the local copy of the timestamp or signature with its counterpart on the remote side. If they don't match, Oracle discards the current session state (ORA-04068) and gives an ORA-04062: timestamp (or signature) of procedure "procedure_name" has been changed error. A second invocation attempt will trigger an automatic recompilation of the local program; if it succeeds, it will execute. The PL/SQL runtime engine makes any needed calls to Oracle's SQL processor, which opens and caches cursors as needed. Normally, a PL/SQL program runs inside the transaction context established by the current session. If, however, the runtime engine calls a program marked as an autonomous transaction, the runtime engine suspends the current transaction and initiates a second transaction context for the called program. After the autonomous transaction commits or rolls back, the original transaction resumes. (See Chapter 14 for more details.) When a running PL/SQL program calls a second program and passes an IN OUT parameter to it, the runtime engine makes an internal copy of the argument before completing the call. If the called program ends with an unhandled exception, the runtime engine will revert the argument to its old value when control reverts to the caller. If you use the IN OUT NOCOPY compiler hint and Oracle accepts it, the runtime engine makes no copy and cannot reverse what may be partial or incorrect changes made by the called program. (See Chapter 17 for a discussion and examples.) For natively compiled PL/SQL, the runtime engine calls out to the appropriate shared library, which has already been linked with the Oracle executable. (See Chapter 20 for complete coverage.) For external procedures, the Oracle Net listener spawns a session-specific process called extproc through which the PL/SQL runtime exchanges arguments and results with the shared library containing the external procedure. (See Chapters 26 and 27 for details.) When the program completes, Oracle passes the results back to the caller in the form of status information and/or return and OUT parameter values. If there were any unhandled exceptions, the runtime engine populates an error stack with information about the exception, which the calling environment normally interrogates and reports to the user.