This is a preliminary page mostly consisting of the outline. I will be filling this out in pieces over the next day or so. If you have anything you'd like to add or change, feel free.

PL/SQL, the Procedural Language for SQL, was first introduced with Oracle SQL*Forms 3.0. Subsequently, after having been released in 1991 as the Procedural Option for Oracle 6, PL/SQL has become one of Oracle's premier development languages.

Background

Since its inception, PL/SQL has been based on the high-level Ada programming language. Accordingly, most of PL/SQL's useful features such as packages, types, functions, and procedures, are all derived from Ada.

Preprocessing

In that it supports conditional compilation and basic definitions, Oracle 10g PL/SQL Conditional Compilation resembles a source language preprocessor. However, unlike a traditional preprocessor, PL/SQL preprocessing is not performed as the first step of the compilation process nor is it performed by a specialized preprocessing component. In fact, the selection directive (similar to the C #ifdef) it is actually built into the PL/SQL compiler and is able to reuse much of the compiler's standard boolean logic.

Basic definitions (similar to the C #define) are called inquiry directives, and are also supported. It is important to note that, unlike other languages, the inquiry directive cannot be used to define macros.

Compilation

Before PL/SQL can be used, it must be compiled into PL/SQL virtual machine executable code (m-code), C code (which must also be compiled and linked), or native machine code (11g only). Like a traditional compiler, the PL/SQL compiler consists of several modules specialized to handle certain phases of the compilation process:

The Compiler Front-End

The PL/SQL compiler front-end is responsible for parsing PL/SQL compilation units and performing semantic and syntactic validation of them. Similarly, the front-end translates the compilation unit into an intermediate representation called Descriptive Intermediate Attributed Notation for Ada (DIANA); this intermediate representation is understood by the back-end.

The Compiler Back-End

After DIANA has been generated, the back-end performs optimization and code generation from DIANA into either m-code, C-language calls to the PVM, or native code linked as shared libraries.

Within recent years, the PL/SQL compiler has been improved to support compile-time optimization and standard compiler features such as inlining.

Execution

PL/SQL execution can take two forms, native and interpreted. The default and most common method employed by users is interpreted mode. In interpreted mode, the DIANA is compiled into m-code, a proprietary byte code, and executed in the PL/SQL virtual machine (PVM).

On the other hand, native mode compilation consists of a code generator which is able to transform DIANA into C (prior to 11g), or assembling and linking it directly. Native code execution calls the same instructions as m-code, but generally has less overhead and is usually faster.

Debugging

PL/SQL debugging can be accomplished using the DBMS_DEBUG package. This same package is used by all the graphical PL/SQL debuggers.

Wrapping

In order to hide proprietary algorithms and prevent users from modifying commercial PL/SQL programs, Oracle has provided a utility called wrap. The sole purpose of the wrap utility is to perform obfuscation of PL/SQL, thereby making it difficult for people to understand.

Prior to Oracle Database 10.2, the wrap utility stripped out comments and translated compilation units into DIANA, which it then wrote back out into the file as plain text. Certain components of plain-text DIANA contained a readable symbol table which made it somewhat easy to decipher.

As of 10.2, the wrap utility strips out comments and compresses the original PL/SQL. Then, wrap checksums the compressed code and stores it in a buffer. After this step, wrap performs basic character substitution and appends the substituted compressed code to the checksum buffer. Once this is complete, the entire buffer is base-64 encoded and written out to the file.

Contrary to old Oracle documentation, it has always been possible to un-wrap wrapped PL/SQL. While components of the former, pre-10g method could be read, whether it was more secure than the new method is debatable.

Best Practices

• PL/SQL Best Practices on Oracle Wiki
• PL/SQL Best Practices (Columns in Oracle Magazine)
  
• PL/SQL Best Practices (Book by Steven Feuerstein)

Links

• PL/SQL on wikipedia http://en.wikipedia.org/wiki/PL/SQL
• PL/SQL Obsession, Steven Feuerstein's online home for all things PL/SQL
• "I Love PL/SQL And..." Help the PL/SQL development team set priorities for future enhancements to PL/SQL!
• PL/SQL Wiki (independent site, not run by Oracle)