Demystifying PL/SQL: The Life of a Compilation Unit

With a solid history of use, Oracle’s PL/SQL is one of the top twenty programming languages in the world.

Overview
While many people tend to think of it strictly as a database procedural language, PL/SQL is far more powerful than most give it credit for. PL/SQL is so popular, in fact, that at least fifteen independent implementations, and several PL/SQL-inspired languages, have been developed based on it. If you’re reading my blog, I’m sure you’ve seen plenty of content illustrating how to use PL/SQL. But, how does it work?

What happens to your PL/SQL code when you pass it to the database? How is your code processed, stored, and executed? How can you optimize your code to make the most out of not only the Oracle Database, but the PL/SQL engine itself? How can you protect your code from public view? Why is Oracle’s PL/SQL implementation so much better than all others? This series of blog posts will answer all (or most of) your questions!

Topics Covered
In this series, we dive deep into PL/SQL and gain a solid understanding of the lifecycle of your code–from start to finish–in the following areas:

  • Tokenization
  • Parsing
  • Preprocessing
  • Intermediate Representation
  • Important Database Tables, Views, and Packages
  • Virtual Machine (m-code)
  • Native Compilation
  • Security
  • Debugging

As concepts are more easily understood and retained when applied to a real-world case, within those topics, we’re also going to cover PL/SQL in the context of the following:

  • Object-oriented Programming
  • Optimization Considerations and Techniques
  • Version Control (Production Best Practices)
  • Code Protection (Wrapping and Obfuscation)
  • Cool, non-database, stuff developed in PL/SQL!
  • Architecture of PL/SQL in TimesTen
  • Thick Database Concepts and Reasoning
  • Comparing Oracle’s canonical PL/SQL implementation to alternatives.

This series of blogs will contain copious references to the most useful documentation, examples, and blogs from Oracle. In addition, I’m also going to reference a ton of useful third-party tools as well as blogs and research from other independent consultants like me!

The History of Oracle PL/SQL
Unlike every other post I’ve done, the first post in this series is non-technical. Instead, it contains a never-before-seen history of the PL/SQL programming language. That’s right: I’ve spent several weeks interviewing some of the most amazing developers, project managers, and individuals involved in the creation of PL/SQL and compiled the stories, background, and history into a post.

If you enjoy nostalgic pieces of Oracle lore, like Softwar, you’ll love A (Not So) Brief But (Very) Accurate History of PL/SQL – an insightful look into the inception of something many of us use on a near-daily basis, but know little about!

Obligatory Disclaimer
Similar to the rest of the information in my blog, everything contained in this series is based on my own research and personal experience. As a longtime database internals developer, I have a pretty good idea of how everything works and have tested my knowledge, in various ways, prior to posting it. As accuracy is very important to me, where possible, I’ve tried to confirm my understanding with Oracle Corporation. That being said, I have never worked for Oracle Corporation nor do I have access to internal documentation or source code. Accordingly, as I can’t speak definitively as to how something works, your mileage may vary.

Acknowledgments
This series of posts wouldn’t have been possible if it weren’t for Oracle Corporation, for creating awesome software, my OakTable Network colleagues, for their ideas/reviews, and the countless PL/SQL developers worldwide who have contributed to my knowledge in this area.