Preface

PL/SQL is one of the top twenty programming languages in the world. While I’m no Matthew Symonds, and this is no Softwar, the history of PL/SQL deserves a helluva lot more than, “Oracle 7 had an advanced architecture and, like Sybase, was fully programmable via its elegant new PL/SQL programming language.” That’s why, during the process working on my OOW/OCO session and related blog series, “Demystifying PL/SQL: The Life of a Compilation Unit,” I wanted to include some of that history.

When I first began writing this history, I figured it would be a few paragraphs at most. In fact, the original title was A Brief But Accurate History of PL/SQL. The deeper I dug and the more people I spoke with, however, the more fascinating the story became. At one point, I decided there was no way I could leave all of this impressive history on the cutting-room floor.

As this is the longest post I’ve written–and I have even more content available–I’m working with Aaron Lazenby at Oracle to (hopefully) extend this into a collaborative multi-part set of articles for Oracle Connect. For that reason, this entry covers only a subset of the origins of PL/SQL. While much of the original underlying code still exists, this post does not cover the history of the optimizing compiler improvements.

Now, without any further ado whatsoever, I present the origins of PL/SQL.

Creating a Language (PL/SQL V1)

Peter Clare’s career began in 1972 at Control Data Corporation (CDC). Although CDC no longer exists, throughout the 1960s and 1970s, it was one of the top mainframe and supercomputer companies in the United States. In terms of software, CDC’s Compiler Products Division created the first commercial Pascal compiler. Peter’s role in this division consisted of language and compiler development focused on building the world’s fastest computer language processors. During his six-year tenure there, Peter not only improved CDC’s compilers but also learned a great deal about overall language development and, more importantly, the practical issues related to it. With this knowledge, Peter left CDC and became a consultant.

Years later, in 1984, Peter thumbed through one of the local bay area tech publications looking for additional consulting-related work. Contained inside that publication was an advertisement from Oracle. Through the grapevine, Peter had heard Oracle was in the process of porting the database to many different platforms, one of which he knew rather well. Thinking it may be an excellent opportunity for some contract work, Peter gave Oracle a call. A few days later, Peter met with Bob Miner and Bill Friend for an interview.

Initially, the trio reviewed Peter’s porting-related experience. As Oracle was also working on SQL precompilers and a forms-based interface to the database, the group additionally discussed Peter’s background in language and compiler development. By the end of that meeting, Bob and Bill convinced Peter to accept a full-time job; Peter became the thirteenth developer at Oracle.

Peter wasn’t your stereotypical Oracle developer. Described by his teammates as a friendly, easy-going, competent, and highly-capable architect, Peter was not only known for being smart, but also for his love of the water and windsurfing, which he did almost every day. Peter took the old, “work hard, play hard” motto to heart, striving to achieve a fulfilling work-life balance – one he honed to perfection throughout his career.

In his first six months at Oracle, Peter’s responsibilities were more project management related than technical. Bob wanted to focus on development and relied on Peter to manage day-to-day project tasks. Although Peter enjoyed the work, communication became increasingly challenging. “By that time, Bob and Larry had been working together for quite a while,” Peter remembered, “it was natural for them to communicate directly with each other and, as the man-in-the-middle, it didn’t take long for everyone to realize I was the third wheel.” To reduce this friction, everyone agreed it would be better for Peter to focus on technical work.

Given his background in programming languages, Peter began working on Oracle’s precompilers. It wasn’t long until another issue arose: competition.

In those days, a couple of database vendors had simple triggers. Similarly, the only way for the database to execute a procedural function was to write it in another language, compile it, and write an interface. Ken Rudin, PL/SQL’s initial product manager, recalls, “as these approaches were somewhat limiting, rather than implement comparable functionality, Oracle wanted to leapfrog the competition and offer a complete procedural programming capability that lived inside the database. Thankfully, Oracle had realized early on that a consistent procedural language would be beneficial across multiple tools.” That language was PL/SQL, and the best person to invent it was Peter Clare.

To create PL/SQL, Peter worked closely with Kendall Jensen, the first official developer of PL/SQL. “Kendall was the key person,” mentions Peter, “he really understood building modern compilers and knew all the latest tools.” To begin, Peter asked Kendall to find everything he could on the Ada programming language.

Immediately, Kendall went straight to the Stanford Library to search for whatever Ada research material was available. Kendall grabbed a copy of everything he could, returned to the Oracle office, and began reading. One of the books he picked-up was a publication titled, “DIANA: An Intermediate Language for Ada,” by Gerhard Goos, William A. Wulf, Arthur Evans Jr., and Kenneth J. Butler. This publication, written as a reference manual, contained not only the Ada language grammar but also the Interface Definition Language (IDL) specification of the Descriptive Intermediate Attributed Notation for Ada – the predominant Ada intermediate representation of the time. With this information at hand, in January of 1987, the first line of PL/SQL compiler code was written.

The PL/SQL compiler began as a verbatim copy of the specification. Kendall wrote the original YACC-based parser using the manual’s Ada grammar and generated a DIANA tree directly from the syntactic rule actions. Unlike compilers for other languages, which often generate an abstract syntax tree and then translate that tree to the intermediate language, this direct approach worked well for an Ada-like language – particularly as the reference manual represented the IDL and grammar in an interlaced fashion. Why were PL/SQL’s syntax and feature-set modeled after Ada?

Considered the successor to Pascal, in the 1980s, Ada was an up-and-coming programming language that was particularly popular within the United States government. It is because of this, and Oracle’s well-known working relationship with the government at that time, that many believe Ada was selected. While that aspect added a degree of credibility to the language, it was more of a tangential benefit than an intentional design decision. In reality, the development team wanted PL/SQL to be a strongly typed language. In those days, most programming languages were strongly typed, and Oracle didn’t want to offer reduced capabilities within the database. Although the developers made concessions for type conversion between the database and PL/SQL engines, which some regretted later, Ada was known to be a simple, safe, and modular language. Likewise, PL/I was the basis of both SQL and Ada, which meant they shared many similarities and made Ada ideal for modeling Oracle’s procedural language after.

Eventually, there were more tasks than Peter and Kendall could accomplish on their own. As such, additional developers joined the team. While Kendall continued to own the parser and overall PL/SQL syntax, which he enjoyed, each new developer specialized in a specific component. The first, Laurence Hughes, performed some cleanup work, picked up various bits-and-pieces, started building toward some semblance of object-orientedness, and landed on overseeing code generation and execution. Pierre Dufour joined soon after, making a few improvements to the parser before focusing on the semantic layer and improving the DIANA tree annotations. Last to join was Gray Clossman.

“By the time I came to Oracle, the group had already implemented a compiler and interpreter for its flow-of-control commands and scalar data types.”, recalls Gray. “The development of PL/SQL came at an exciting time because many programming-language concepts had been explored in academia, but were not yet available in scripting languages for commercial products.” Gray worked on several aspects of the language, such as improving memory management and taking over Laurence’s initial work on object-oriented support. “Gray understood languages like no one else I’ve ever known.”, Peter recalls, “he had a lot of experience in functional programming and had a clear understanding of all the issues related to it.” While multiple developers worked on PL/SQL, one worked with it: James Mallory.

James began his first stint at Oracle during his summer break from Stanford in 1987. As the “first PL/SQL programmer” not developing the language itself, he wrote the canonical “Hello World” as well as a quality assurance test suite for PL/SQL V1. “We had been working on the language for a while,” Peter recalls, “and when you test a language yourself, you know to avoid certain things the compiler can’t handle. It’s good to have a fresh set of eyes play with the language every once in a while.”

Over time, Kendall’s foresight–to implement the entire specification upfront, rather than only the subset required to support PL/SQL V1–resulted in an increased ability to add additional functionality later in development. As an example, while PL/SQL V1 only supported simple procedures, the addition of packages in PL/SQL V2 primarily required additions to the code generator and interpreter – the majority of parser and DIANA support was already present. Gray remembers, “Kendall always maintained a clear overview of the project; he never got lost in the weeds, always saw the priorities calmly, and was available to help work through a knotty problem.”

The next knotty problem: integrating PL/SQL into Oracle Database.

Integrating with Oracle 7 (PL/SQL V2)

It all began during a series of discussions that took place at Bob Miner’s Pacific Heights home office. There, Bob, Peter, and Roger Bamford met to exchange ideas concerning how to provide non-Oracle developers with the ability to extend core server functionality easily. “Getting together face-to-face with Roger was always beneficial,” Peter remembers, “he’s an incredibly intelligent guy – I always learned a lot about the database from him, and he learned a lot about PL/SQL from me.”

The architecture of the PL/SQL engine, as an embeddable component for Oracle products, afforded Oracle several advantages. First, the engine could execute server-side from within the database itself. Second, the engine could operate client-side, integrated into other Oracle products (e.g., SQL*Forms.) Lastly, the engine could execute in a similar client-side fashion, but more independently, as it does to provide PL/SQL support in TimesTen.

While server-side PL/SQL is the form most people are familiar with, it took a significant amount of time and effort to get working. “Getting PL/SQL to run server-side was a nightmare,” Pierre recalled, “initially, it fit into the database about as well as round pegs fit into trapezoids.” Peter also remembers, “getting PL/SQL into the V7 database was a bit of a hack job, but a necessary hack job.” “The first cut was beneficial,” Peter continues, “because the database team learned a lot from us, and we learned a lot from them. It was the first time each team saw the problems the other team faced.”

From a client-side perspective, the PL/SQL engine was not only available in Forms, but also several custom vertical tools. Gray recalls, “We had stressful meetings with designers of Oracle’s client-side products where we tried to show how they could model the semantics of their applications in PL/SQL by using scoping rather than namespaces.”

Although the PL/SQL engine is dependent on a substantial amount of underlying platform support and memory management code shared with the Oracle Database, the PL/SQL engine sports an abstract cursor interface to the underlying database. “The cursor interface we came up with to separate the database from PL/SQL was one of the neatest parts,” recalls Kendall. In Oracle, the PL/SQL cursor interface is mapped to the database kernel query layer while, in TimesTen, it’s an independent connection to the TimesTen engine.

In late 1989 or early 1990, Oracle had grown considerably. As a result, the consolidation of multiple development teams into larger groups occurred. The PL/SQL team was no exception, and several other members of the precompiler team joined PL/SQL development. Given the similarity in developer skillsets, this restructuring worked to Oracle’s advantage and resulted in several immediate improvements to PL/SQL. Notable immediate improvements were the fruit born from the work of Terry and Jeffrey Olkin; brothers referred to fondly as “The Wondertwins.”

“Terry was super to have around,” recalled Pierre, “he owned our YACC technology that underlay PL/SQL and precompilers.” Terry has always had a passion for programming languages. “Building a programming language that allows a user to specify what the computer should do, at a high level, and transforming that desire into something the computer understands and can execute has always seemed somewhat magical to me,” Terry says. That’s why, before joining PL/SQL development, he spent several years improving Oracle’s precompilers. During that time, in addition to many other things, Terry made significant improvements to SQLLIB, the underlying shared library all Oracle precompilers generated code for, and created SLAX, the parser generator PL/SQL relies on to this day. SLAX, however, was not created for PL/SQL.

In the 1980s, Oracle supported SQL precompilers for the Ada, C/C++, COBOL, FORTRAN, Pascal, and PL/I host programming languages. At the time, each precompiler used a hand-written parser – this made it difficult to track which features each supported. Alternatively, by using a parser generator, the developers could more easily specify the grammar of the host language and generate the code to parse it. Unfortunately, while this made it easy to define the grammar of the embedded SQL supported by the precompiler (via EXEC SQL) and the host language, parsing both at the same time was problematic.

In language parsing, an island grammar is a grammar of one programming language contained within that of another programming language. Embedded SQL, stored within a host language (C, COBOL, etc.), is considered the island grammar. YACC, the preeminent parser generator of the time, did not (and still does not) truly support island grammars. Getting YACC to parse an island grammar is painful.

From a technical standpoint, for YACC to switch between multiple grammars in the same parser, a specific symbol, invisible to the user, must be specified. The parser and lexer must then communicate with each other around which language to tokenize and which to parse. When the lexer identifies the island grammar, it switches states and must inject this specific symbol so YACC can select the proper island grammar rule (rather than the host language rule). Likewise, when YACC believes it has parsed the island grammar, it must inform the lexer that it should switch its state back to the host language. Although there are other ways to do this with YACC, all are equally painful. As the precompiler group needed to be able to integrate the same embedded SQL parser within multiple host languages, they needed a tool that would allow them to do that easily. Enter SLAX.

SLAX, pronounced as slack, is an internal mostly-YACC-compatible parser generator. While it may look like an acronym, it isn’t. SLAX stands for Segmented Language YACC. That’s right, the proper pronunciation of the X in SLAX is as a K. While SLAX shared no code with YACC, when he named it, Terry decided to give a respectful tip of the hat to YACC. Like most tools, it did its job well. After a while, however, SLAX needed to be modified and was eventually rewritten. John Ciminski performed the rewrite in what Terry referred to as “The Right Way.” Already a proven tool, SLAX was a natural replacement for the PL/SQL parser’s use of YACC. Terry transitioned PL/SQL from YACC to SLAX when he began working on PL/SQL sometime in 1990, before improving the underlying parser itself.

During this time, Terry brought his brother, Jeffrey, on board. Unlike Terry, who enjoyed frontend compiler work, Jeffrey preferred the backend: specifically, code generation and execution. “Jeffrey was phenomenal,” remarked Peter.

Initially, the focus on PL/SQL development was the language itself, and performance wasn’t a critical aspect. But, as more and more customers began to use PL/SQL, performance became an issue. The primary performance bottleneck was the first version of the PL/SQL virtual machine (“VM”), which was a stack-based byte code interpreter similar to standard Pascal byte code (p-code) interpreters of the time.

In a stack-based virtual machine, expression operands are pushed onto the stack then popped when needed for computation. For machines of the time, this was too slow. The overhead interpreting each byte code instruction in software was significant; A simple computation could take dozens of instructions, and most of the time spent in execution went toward that overhead.

In Ada, this is worse as operands can refer to variables within multiple scopes and across multiple packages. As such, Jeffrey first rewrote all of that and created a kind of “mini-interpreter” for operands, so that retrieval of an operand, no matter how complex its address, could be performed as part of a single byte code instruction. This change substantially reduced the overhead for instruction decoding and made large PL/SQL programs more feasible. On this improvement, Kendall recalls, “I remember looking at it. It was brilliant and beautiful code – really elegant.” James, who rejoined Oracle again in 1990, mentioned how much more efficient the new virtual machine was as well. 

Life After PL/SQL

Eventually, everyone from the initial days would move on from PL/SQL – either leaving Oracle or moving to other projects. Gray Clossman went on to build other language-related features at Oracle, such as SQLJ. Pierre Dufour moved to the applications-side, where he worked on CRM as well as built an internal-only PL/SQL compiler, Rosetta, that made it possible to make calls between Java and PL/SQL without modification. After seeing a demo of a tool implementing the ANSI SQL Module Language–over the weekend–Terry Olkin built a prototype that did the same for Oracle, which led to the creation of the SQL*Module product.

Reflections

When asked what he thought of working at Oracle, Peter says, “I got the golden years. The database group was amazing, a truly incredible group of developers. I also loved working with the sales guys–the energy–it was the kind of place where if you had an idea and could make someone believe you could execute on it, you could.” “It was a blast,” recalls Kendall, “Until PL/SQL became popular, we got to manage everything about it.” Gray shares a similar memory, “The team was self-starting and completely reliable. Each member held up their end of any joint development effort. It was a joy to work with them.”

From a leadership perspective, all of the initial team members had a fond recollection of Peter in terms of both personality and technical competency. Kendall states, “I’ve never worked with anyone else who communicated at such a high level.” Terry continues, “Peter was the brains behind PL/SQL and also one of the nicest people I’ve ever met.” Gray seconded this with, “Peter was the best possible project leader and manager; he often jolted us from our complacency as workaday programmers by proposing audacious possibilities for the project or by asking surprising, even futuristic, questions.” James remembers, “Peter was a great guy and fun to work for, and it was his vision that gave rise to PL/SQL.”

Acknowledgments

For taking their time to contribute, with both memories and references, I sincerely want to thank the following:

From the original team: Peter Clare, Kendall Jensen, Laurence Hughes, Pierre Dufour, Gray Clossman, James Mallory, and Ken Rudin.

From subsequent developers: Terry and Jeffrey Olkin.

From Oracle Alumni and OakTable: Bill Friend, Roger Bamford, Kirk Bradley, Graham Wood, Mogens Nørgaard, Bob Jenkins, Bryn Llewellyn, and Tuomas Pystynen.

From those at Oracle (for the potential Connect article): Aaron Lazenby, Steven Feuerstein, and Charles Wetherell.

While I’ve reached out to Larry Ellison and Andy Mendelsohn (who wrote the first PL/SQL execution engine), for comment, I haven’t yet received a response. I’m hopeful we can one day add their recollections and insights to this archive as well.