Oracle7 Server Concepts
How Oracle Executes Procedures and Packages
When you invoke a standalone or packaged procedure, Oracle performs these steps to execute it:
2. Verifies procedure validity.
3. Executes the procedure.
Verifying User Access
Oracle verifies that the calling user owns or has the EXECUTE privilege on the procedure or encapsulating package. The user who executes a procedure does not require access to any procedures or objects referenced within the procedure; only the creator of a procedure or package requires privileges to access referenced schema objects.
Verifying Procedure Validity
Oracle checks the data dictionary to see if the status of the procedure or package is valid or invalid. A procedure or package is invalid when one of the following has occurred since the procedure or package was last compiled:
- One or more of the objects referenced within the procedure or package (such as tables, views, and other procedures) have been altered or dropped (for example, if a user added a column to a table).
- A system privilege that the package or procedure requires has been revoked from PUBLIC or from the owner of the procedure or package.
- A required object privilege for one or more of the objects referenced by a procedure or package has been revoked from PUBLIC or from the owner of the procedure or package.
A procedure is valid if it has not been invalidated by any of the above operations.
If a valid standalone or packaged procedure is called, the compiled code is executed.
If an invalid standalone or packaged procedure is called, it is automatically recompiled before being executed.
For a complete discussion of valid and invalid procedures and packages, recompiling procedures, and a thorough discussion of dependency issues, see Chapter 16, "Dependencies Among Schema Objects".
Executing a Procedure
The PL/SQL engine executes the procedure or package using different steps, depending on the situation:
- If the procedure is valid and currently in memory, the PL/SQL engine simply executes the P code.
- If the procedure is valid and currently not in memory, the PL/SQL engine loads the compiled P code from disk to memory and executes it. For packages, all constructs of the package (all procedures, variables, and so on, compiled as one executable piece of code) are loaded as a unit.
The PL/SQL engine processes a procedure statement by statement, handling all procedural statements by itself and passing SQL statements to the SQL statement executor, as illustrated in Figure 11 - 1.