Learning Oracle PL/SQL

9.5. The PL/SQL Compiler

Before Oracle can run a PL/SQL program, that program must be compiled. This means that the compiler (some software inside Oracle) converts your PL/SQL from source code form into another form.

Although I have described many aspects of the PL/SQL compiler throughout this book, you may want to know more details about how it works. Usually this interest reflects a desire to squeeze the greatest possible runtime performance out of your applications.

9.5.1. When Does Compilation Happen?

Unlike many programming languages, a "compile" command is not necessary with PL/SQL. Instead, Oracle attempts to compile your program when you load it into the database server—for example, using the CREATE OR REPLACE PACKAGE statement. If the compile fails, the source code will still be inside the database, but the program will be in an " INVALID" state. If the program failed because of a syntax error, you should correct the error and run the CREATE OR REPLACE again.

Once you have successfully loaded and compiled a program into Oracle, it will execute as long as it is in a "VALID" state. You can examine the state of your programs by querying the data dictionary:

SELECT object_name, object_type, status
  FROM USER_OBJECTS
 WHERE status = 'INVALID';

Several things can cause a program to become invalid, though, including:

• A structural change in a table to which the program refers—for example, the addition of a column

• The recreation or recompilation of any Oracle view or PL/SQL ...