{"id":112,"date":"2020-04-21T18:57:04","date_gmt":"2020-04-21T18:57:04","guid":{"rendered":"http:\/\/oracle-internals.com\/blog\/?p=112"},"modified":"2020-05-06T18:02:56","modified_gmt":"2020-05-06T18:02:56","slug":"demystifying-pl-sql-the-life-of-a-compilation-unit","status":"publish","type":"post","link":"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/","title":{"rendered":"Demystifying PL\/SQL: The Life of a Compilation Unit"},"content":{"rendered":"\n<p>With a <a rel=\"noreferrer noopener\" href=\"https:\/\/www.tiobe.com\/tiobe-index\/pl-sql\/\" target=\"_blank\">solid history of use<\/a>, <a rel=\"noreferrer noopener\" href=\"https:\/\/www.oracle.com\/database\/technologies\/application-development-PL\/SQL.html\" target=\"_blank\">Oracle&#8217;s PL\/SQL<\/a> is one of the <a rel=\"noreferrer noopener\" href=\"https:\/\/www.tiobe.com\/tiobe-index\/\" target=\"_blank\">top twenty programming languages<\/a> in the world.<\/p>\n\n\n\n<p><strong>Overview<\/strong><br>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&#8217;re reading my blog, I&#8217;m sure you&#8217;ve seen plenty of content illustrating how to use PL\/SQL. But, how does it work?<\/p>\n\n\n\n<p>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 <a rel=\"noreferrer noopener\" href=\"https:\/\/www.oracle.com\/database\/\" target=\"_blank\">Oracle Database<\/a>, but the PL\/SQL engine itself? How can you protect your code from public view? Why is Oracle&#8217;s PL\/SQL implementation so much better than all others? This series of blog posts will answer all (or most of) your questions!<\/p>\n\n\n\n<p><strong>Topics Covered<\/strong><br>In this series, we dive deep into PL\/SQL and gain a solid understanding of the lifecycle of your code&#8211;from start to finish&#8211;in the following areas:<\/p>\n\n\n\n<ul><li>Tokenization<\/li><li>Parsing<\/li><li>Preprocessing<\/li><li>Intermediate Representation<\/li><li>Important Database Tables, Views, and Packages<\/li><li>Virtual Machine (m-code)<\/li><li>Native Compilation<\/li><li>Security<\/li><li>Debugging<\/li><\/ul>\n\n\n\n<p>As concepts are more easily understood and retained when applied to a real-world case, within those topics, we&#8217;re also going to cover PL\/SQL in the context of the following:<\/p>\n\n\n\n<ul><li>Object-oriented Programming<\/li><li>Optimization Considerations and Techniques<\/li><li>Version Control (Production Best Practices)<\/li><li>Code Protection (Wrapping and Obfuscation)<\/li><li>Cool, non-database, stuff developed in PL\/SQL!<\/li><li>Architecture of PL\/SQL in TimesTen<\/li><li>Thick Database Concepts and Reasoning<\/li><li>Comparing Oracle&#8217;s canonical PL\/SQL implementation to <a href=\"http:\/\/oracle-internals.com\/blog\/2020\/04\/22\/imitation-is-the-sincerest-form-of-flattery\/\">alternatives<\/a>.<\/li><\/ul>\n\n\n\n<p>This series of blogs will contain copious references to the most useful documentation, examples, and blogs from Oracle. In addition, I&#8217;m also going to reference a ton of useful third-party tools as well as blogs and research from other independent consultants like me!<\/p>\n\n\n\n<p><strong>The History of Oracle PL\/SQL<\/strong><br>Unlike every other post I&#8217;ve done, the first post in this series is non-technical. Instead, <span style=\"text-decoration: underline;\">it contains a never-before-seen history of the PL\/SQL programming language<\/span>. That&#8217;s right: I&#8217;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.<\/p>\n\n\n\n<p>If you enjoy nostalgic pieces of Oracle lore, like <a rel=\"noreferrer noopener\" href=\"https:\/\/www.amazon.com\/Softwar-Intimate-Portrait-Ellison-Oracle\/dp\/0743225058\/ref=tmm_pap_swatch_0?_encoding=UTF8&amp;qid=&amp;sr=\" target=\"_blank\">Softwar<\/a>, you&#8217;ll love <em><a href=\"http:\/\/oracle-internals.com\/blog\/2020\/04\/29\/a-not-so-brief-but-very-accurate-history-of-pl-sql\/\">A (Not So) Brief But (Very) Accurate History of PL\/SQL<\/a><\/em> &#8211; an insightful look into the inception of something many of us use on a near-daily basis, but know little about!<\/p>\n\n\n\n<p><strong>Obligatory Disclaimer<\/strong><br>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&#8217;ve tried to confirm my understanding with Oracle Corporation. That being said, <span style=\"text-decoration: underline;\">I have never worked for Oracle Corporation nor do I have access to internal documentation or source code.<\/span> Accordingly, as I can&#8217;t speak definitively as to how something works, your mileage may vary.<\/p>\n\n\n\n<p><strong>Acknowledgments<\/strong><br>This series of posts wouldn&#8217;t have been possible if it weren&#8217;t for <a rel=\"noreferrer noopener\" href=\"https:\/\/www.oracle.com\/index.html\" target=\"_blank\">Oracle Corporation<\/a>, for creating awesome software, my <a rel=\"noreferrer noopener\" href=\"http:\/\/www.oaktable.net\/\" target=\"_blank\">OakTable Network<\/a> colleagues, for their ideas\/reviews, and the countless PL\/SQL developers worldwide who have contributed to my knowledge in this area.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>With a solid history of use, Oracle&#8217;s PL\/SQL is one of the top twenty programming languages in the world. OverviewWhile 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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,9],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Demystifying PL\/SQL: The Life of a Compilation Unit - Oracle Internals<\/title>\n<meta name=\"description\" content=\"A deep-dive into the PL\/SQL language from tokenization to parsing, preprocessing, compilation, optimization, debugging, and execution.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Demystifying PL\/SQL: The Life of a Compilation Unit - Oracle Internals\" \/>\n<meta property=\"og:description\" content=\"A deep-dive into the PL\/SQL language from tokenization to parsing, preprocessing, compilation, optimization, debugging, and execution.\" \/>\n<meta property=\"og:url\" content=\"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/\" \/>\n<meta property=\"og:site_name\" content=\"Oracle Internals\" \/>\n<meta property=\"article:published_time\" content=\"2020-04-21T18:57:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-05-06T18:02:56+00:00\" \/>\n<meta name=\"author\" content=\"Jonah Harris\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@jonahharris\" \/>\n<meta name=\"twitter:site\" content=\"@jonahharris\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonah Harris\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/#article\",\"isPartOf\":{\"@id\":\"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/\"},\"author\":{\"name\":\"Jonah Harris\",\"@id\":\"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4\"},\"headline\":\"Demystifying PL\/SQL: The Life of a Compilation Unit\",\"datePublished\":\"2020-04-21T18:57:04+00:00\",\"dateModified\":\"2020-05-06T18:02:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/\"},\"wordCount\":620,\"publisher\":{\"@id\":\"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4\"},\"articleSection\":[\"Oracle Database\",\"PL\/SQL\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/\",\"url\":\"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/\",\"name\":\"Demystifying PL\/SQL: The Life of a Compilation Unit - Oracle Internals\",\"isPartOf\":{\"@id\":\"https:\/\/oracle-internals.com\/blog\/#website\"},\"datePublished\":\"2020-04-21T18:57:04+00:00\",\"dateModified\":\"2020-05-06T18:02:56+00:00\",\"description\":\"A deep-dive into the PL\/SQL language from tokenization to parsing, preprocessing, compilation, optimization, debugging, and execution.\",\"breadcrumb\":{\"@id\":\"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/oracle-internals.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Demystifying PL\/SQL: The Life of a Compilation Unit\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/oracle-internals.com\/blog\/#website\",\"url\":\"https:\/\/oracle-internals.com\/blog\/\",\"name\":\"Oracle Internals\",\"description\":\"Researching the Inner Workings of the World&#039;s Most Powerful Database\",\"publisher\":{\"@id\":\"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/oracle-internals.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4\",\"name\":\"Jonah Harris\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/a6d16ed0f510e8de0929f129471dc1e5?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/a6d16ed0f510e8de0929f129471dc1e5?s=96&d=mm&r=g\",\"caption\":\"Jonah Harris\"},\"logo\":{\"@id\":\"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/in\/jonahharris\/\",\"https:\/\/x.com\/jonahharris\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Demystifying PL\/SQL: The Life of a Compilation Unit - Oracle Internals","description":"A deep-dive into the PL\/SQL language from tokenization to parsing, preprocessing, compilation, optimization, debugging, and execution.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/","og_locale":"en_US","og_type":"article","og_title":"Demystifying PL\/SQL: The Life of a Compilation Unit - Oracle Internals","og_description":"A deep-dive into the PL\/SQL language from tokenization to parsing, preprocessing, compilation, optimization, debugging, and execution.","og_url":"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/","og_site_name":"Oracle Internals","article_published_time":"2020-04-21T18:57:04+00:00","article_modified_time":"2020-05-06T18:02:56+00:00","author":"Jonah Harris","twitter_card":"summary_large_image","twitter_creator":"@jonahharris","twitter_site":"@jonahharris","twitter_misc":{"Written by":"Jonah Harris","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/#article","isPartOf":{"@id":"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/"},"author":{"name":"Jonah Harris","@id":"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4"},"headline":"Demystifying PL\/SQL: The Life of a Compilation Unit","datePublished":"2020-04-21T18:57:04+00:00","dateModified":"2020-05-06T18:02:56+00:00","mainEntityOfPage":{"@id":"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/"},"wordCount":620,"publisher":{"@id":"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4"},"articleSection":["Oracle Database","PL\/SQL"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/","url":"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/","name":"Demystifying PL\/SQL: The Life of a Compilation Unit - Oracle Internals","isPartOf":{"@id":"https:\/\/oracle-internals.com\/blog\/#website"},"datePublished":"2020-04-21T18:57:04+00:00","dateModified":"2020-05-06T18:02:56+00:00","description":"A deep-dive into the PL\/SQL language from tokenization to parsing, preprocessing, compilation, optimization, debugging, and execution.","breadcrumb":{"@id":"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/oracle-internals.com\/blog\/2020\/04\/21\/demystifying-pl-sql-the-life-of-a-compilation-unit\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/oracle-internals.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Demystifying PL\/SQL: The Life of a Compilation Unit"}]},{"@type":"WebSite","@id":"https:\/\/oracle-internals.com\/blog\/#website","url":"https:\/\/oracle-internals.com\/blog\/","name":"Oracle Internals","description":"Researching the Inner Workings of the World&#039;s Most Powerful Database","publisher":{"@id":"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/oracle-internals.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4","name":"Jonah Harris","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/a6d16ed0f510e8de0929f129471dc1e5?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/a6d16ed0f510e8de0929f129471dc1e5?s=96&d=mm&r=g","caption":"Jonah Harris"},"logo":{"@id":"https:\/\/oracle-internals.com\/blog\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/in\/jonahharris\/","https:\/\/x.com\/jonahharris"]}]}},"_links":{"self":[{"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/posts\/112"}],"collection":[{"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/comments?post=112"}],"version-history":[{"count":20,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/posts\/112\/revisions"}],"predecessor-version":[{"id":305,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/posts\/112\/revisions\/305"}],"wp:attachment":[{"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/media?parent=112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/categories?post=112"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/tags?post=112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}