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, can offer high levels of productivity 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 rose and requirements became more stringent. 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 1988, 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 (the original name for the product now known as Oracle Forms or Forms Developer). SQL*Forms V3.0 incorporated the PL/SQL engine for the first time on the tools 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, although support for functions was not documented, and was 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.
But 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 for 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.
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 that needed the more sophisticated and granular control offered by host languages like COBOL, C, and FORTRAN. As soon as a developer stepped outside 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 that can be handled entirely in operating-system-independent programming tools. Today, Java and other programming languages have also made great strides in portability. Yet PL/SQL stands out as an early pioneer in this field and, of course, it continues to allow developers to write highly portable application code.
An even more fundamental issue than portability was execution authority. The RDBMS and the SQL language give you the capability to tightly control access to, and changes in, any particular database 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, as is common with most business transactions.
The PL/SQL language was intended by Oracle to provide tight control and management over logical transactions. One way that PL/SQL does this is with implementation of execution authority. Instead of granting to a role or user the authority to update a table, you grant privileges only to execute a procedure, which 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 transaction. The only way that a program (whether it’s an Oracle Forms application or a Pro*C executable) can execute the transfer is through the procedure. In this way, transaction integrity is guaranteed.
In the time that Oracle has been working to make Java available as an alternative to PL/SQL in the database, it has also made steady, fundamental improvements to the PL/SQL language itself. It has added a great variety of supplied (or built-in) packages that extend the PL/SQL language in numerous ways and directions. It has introduced object-oriented capabilities, implemented a variety of array-like data structures, and in general improved the breadth and depth of the language.
As powerful as SQL is, it simply does not offer the flexibility and power that developers need to create full-blown applications. Oracle’s PL/SQL language ensures that we can stay entirely within the operating-system-independent Oracle environment and still write highly efficient applications that meet our users’ requirements.
PL/SQL has come a long way from its humble beginnings. With Version 1.0, it was not uncommon for a developer to have to tell his or her manager, “You can’t do that with PL/SQL.” Today, that statement has moved from fact to excuse. If you are ever confronted with a requirement and find yourself saying, “There’s no way to do that,” don’t repeat it to your manager. Instead, dig deeper into the language, or explore the range of built-in PL/SQL packages offered by Oracle. There is a very good chance that PL/SQL will today allow you to do pretty much whatever you need to do.
In the next section, we take a look at some examples of PL/SQL programs that will familiarize you with the basics of PL/SQL programming.
Get Oracle PL/SQL Programming, Third Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
