[Chapter 1] 1.3 The Origins of PL/SQL

1.3 The Origins of PL/SQL

Oracle has a history of leading the software industry in providing declarative, nonprocedural approaches to designing both databases and applications. The Oracle Server technology is among the most advanced, powerful, and stable relational databases in the world. Its application development tools, such as Oracle Forms, offer high levels of productivity, precisely by relying heavily on a "paint-your-screen" approach in which extensive default capabilities allow developers to avoid heavy customized programming efforts.

In Oracle's early years, this declarative approach, combined with its groundbreaking relational technology, was enough to satisfy developers. But as the industry matured, expectations and requirements became more demanding. Developers needed to get under the skin of the products. They needed to build complicated formulas, exceptions, and rules into their forms and database procedures.

In 1991, Oracle Corporation released Oracle Version 6.0, a major advance in its relational database technology. A key component of Oracle Version 6.0 was the so-called "procedural option" or PL/SQL. At roughly the same time, Oracle released its long-awaited upgrade to SQL*Forms Version 2.3. SQL*Forms V3.0 incorporated the PL/SQL engine for the first time on the tool side, allowing developers to code their procedural logic in a natural, straightforward manner.

This first release of PL/SQL was very limited in its capabilities. On the server side, you could use PL/SQL only to build "batch-processing" scripts of procedural and SQL statements. In other words, you could not store procedures or functions for execution at some later time. You could not construct a modular application or store complex business rules in the server. On the client side, SQL*Forms V3.0 did allow you to create procedures and functions, though support for functions was not documented and therefore not used by many developers for years. In addition, this release of PL/SQL did not implement array support and could not interact with the operating system (for input or output). It was a far cry from a full-fledged programming language.

For all its limitations, PL/SQL was warmly, even enthusiastically, received in the developer community. The hunger for the ability to code a simple IF statement inside SQL*Forms was strong. The need to perform multi-SQL statement batch processing was overwhelming.

What few developers realized at the time was that the original motivation and driving vision behind PL/SQL extended beyond the desire to offer programmatic control within products like SQL*Forms. Very early in the life cycle of Oracle's database and tools, Oracle Corporation had recognized two key weaknesses in their architecture: lack of portability and problems with execution authority.

1.3.1 Improved Application Portability with PL/SQL

The concern about portability might seem odd to those of us familiar with Oracle Corporation's marketing and technical strategies. One of the hallmarks of the Oracle solution from the early 1980s was its portability. At the time that PL/SQL came along, the C-based RDBMS ran on many different operating systems and hardware platforms. SQL*Plus and SQL*Forms adapted easily to a variety of terminal configurations. Yet for all that coverage, there were still many applications which needed the more sophisticated and granular control offered by host languages like COBOL, C, and FORTRAN. As soon as a developer stepped outside of the port-neutral Oracle tools, the resulting application would no longer be portable.

The PL/SQL language was (and is) intended to widen the range of application requirements which can be handled entirely in operating-system independent programming tools.

1.3.2 Improved Execution Authority and Transaction Integrity with PL/SQL

Even more fundamental an issue than portability was that of execution authority. The RDBMS and SQL language give you the capability to tightly control access to, and changes in, any particular table. For example, with the GRANT command you can make sure that only certain roles and users have the ability to perform an UPDATE on a given table. On the other hand, this GRANT statement can't ensure that the full set of UPDATEs performed by a user or application is done correctly. In other words, the database can't guarantee the integrity of a transaction that spans more than one table.

Let's look at an example. In a typical banking transaction, you might need to transfer funds from account A to account B. The balance of account B must be incremented, and that of account A decremented. Table access is necessary, but not sufficient, to guarantee that both of these steps are always performed by all programmers who write code to perform a transfer. Without stored objects, the best you can do is require extensive testing and code review to make sure that all transactions are properly constructed. With stored objects, on the other hand, you can guarantee that a funds transfer either completes successfully or is completely rolled back -- regardless of who executes the process.

The secret to achieving this level of transaction integrity is the concept of execute authority (also known as run authority). Instead of granting to a role or user the authority to update a table, you grant privileges to only execute a procedure. This procedure controls and provides access to the underlying data structures. The procedure is owned by a separate Oracle RDBMS account, which, in turn, is granted the actual update privileges on those tables needed to perform the transaction. The procedure therefore becomes the "gatekeeper" for the transfer transaction. The only way a program (whether it's an Oracle Forms application or a Pro*C executable) can execute the transfer is through the procedure. The result is that transaction integrity is guaranteed.

The long-term objective of PL/SQL, realized with PL/SQL Version 2.0, was to provide a programming environment which would allow developers both to create reusable, callable modules and also to grant authority to those modules. Given Oracle Corporation's strategic technical investment in relational databases, it's only logical that these programs would reside in the database itself. This way, the same data dictionary that controls access to data could control access to the programs.

PL/SQL has come a long way from its humble beginnings. It is a rapidly maturing programming language that will play a prominent role in all future Oracle application development and database management efforts.