Compiling a Stored Object

There are some variations on the compilation process for stored objects (package, package body, procedure, function, trigger, type, type body):

1. 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 "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.

2. If the object being compiled is a package with state information, Oracle will tear down this package state and possibly the state of other packages in session memory.

3. 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 also stores the current compiler flags in the SETTINGS$ table.

4. Successful compilation causes DIANA to be stored in the database for everything except package bodies and type bodies (even for natively compiled programs).

5. Once the DIANA exists for a stored program, Oracle also saves information about the objects on which the program depends in the DEPENDENCY$ table.

6. 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

7. If the session or database flags are set for native compilation, the PL/SQL compiler performs the usual syntax and semantic analysis. An internal translator then converts the PL/SQL into C and invokes the operating system's C compiler to generate a shared object file.

8. 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.

9. 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.

20.4.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:

1. If the invoker of the program is not its owner, Oracle checks whether the invoker has been granted EXECUTE privilege on the program.

2. 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.

3. Oracle opens a cursor and associates it with the program, even if it is an anonymous block.

4. 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.

5. The PL/SQL runtime engine makes any needed calls to Oracle's SQL processor, which opens and caches cursors as needed.

6. 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 13 for more details).

7. 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 16 for a discussion and examples).

8. 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 19 for complete coverage.)

9. 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 Chapter 22 and Chapter 23 for details).

10. 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.

20.5 PL/SQL Code on the Client

If you are using Oracle's application development tools, such as Forms or Reports, you can use PL/SQL as the language in which you create an application's graphical user interface and supporting logic. Technically, you don't even have to connect to a database to use this feature. Also, with a little effort, you can even hook up client-side PL/SQL to databases other than Oracle. The normal arrangement, though, looks like Figure 20-10.