How many of us would believe that PL/SQL was introduced as a scripting language for executing a bunch of SQL scripts? Well, that's true. With the growing need to build computational logic and condition-based constructs, and to manage exception rules within databases, Oracle Corporation first released PL/SQL along with Oracle Database Version 6.0 with a limited set of capabilities. Within its capacity, PL/SQL was capable of creating program units that could not be stored inside the database. Eventually, Oracle's release in the application line, SQL *Forms version V3.0, included the PL/SQL engine and allowed developers to implement the application logic through procedures. Back then, PL/SQL used to be part of the transaction processing option in Oracle 6 and the procedural option in Oracle 7. Since the time of its ingenuous beginning, PL/SQL has matured immensely as a standard feature of Oracle Database. It has been enthusiastically received by the developer community, and the credit goes to its support for advanced elements such as modular programming, encapsulation, support for objects and collections, program overloading, native and dynamic SQL, and exception handling.
PL/SQL is loosely derived from Ada (named after Ada Lovelace, an English mathematician who is regarded as the first computer programmer), a high-level programming language, which complies with the advanced programming elements. Building a database backend for an application demands the ability to design the database architecture, skills to code complex business logics, and expertise in administering and protecting the database environment. One of the principal reasons why PL/SQL is a key enabler in the development phase is its tight integration with Oracle's SQL language. In addition to this, it provides a rich platform for implementing the business logic in the Oracle Database layer and store them as procedures or functions for subsequent use. As a procedural language, PL/SQL provides a diverse range of datatypes, iterative and control constructs, conditional statements, and exception handlers.
In a standard software development space, an Oracle database developer is expected to get involved in schema design; code business logics on the server side by using functions, procedures, or packages; implement action rules by using triggers; and support client-side programs in setting up the application interface. While building the server-side code, developers should understand that their code contributes to the application's performance and scalability. Language basics are expected to be resilient, but while building robust and secure applications using PL/SQL, developers must take advantage of best practices and try to use advanced language features. This book focuses on the advanced features of PL/SQL validated up to the latest Oracle Database 12c.
Learning by example has always been a well-attested approach for diving deep into a concept. This book will enable you to master the latest enhancements and new features of Oracle Database 12c. For efficient reading, you just have to be familiar with the PL/SQL fundamentals so that you can relate to the evolution of an advanced feature from its ever-expanding roots.
This book closely follows the outline of the Oracle University certification; that is, the Oracle Certified Advanced PL/SQL Developer Professional (1Z0-146) exam. One of the most sought after certifications in the developer community, the 1Z0-146 certification's objectives are quite comprehensive, and touch upon the various progressive areas of PL/SQL. To name a few, PL/SQL code performance, maintenance, bulk processing techniques, PL/SQL collections, security implementation, and the handling of large objects. For certification aspirants, this book will serve as a one-stop exam guide. At many stages, this book goes beyond the certification objectives and attempts to build a deep understanding of the concepts. Therefore, mid-level database developers will find this book a handy language reference and would be keen to have it on their bookshelves.
My last work on the same subject will remain close to my heart, but this one is straight from my experience. I hope that this book will help you improve your PL/SQL development skills and gain confidence in using advanced features, along with meticulous familiarization of Oracle Database 12c.
"The only real security that a man can have in the world is a reserve of knowledge, experience and ability" | ||
| --Henry Ford | ||
Chapter 1, Overview of PL/SQL Programming Concepts, provides an overview of PL/SQL fundamentals. It refreshes the basic concepts, such as PL/SQL language features, the anonymous block structure, exception handling, and stored subprograms.
Chapter 2, Oracle 12c SQL and PL/SQL New Features, talks about the new features of Oracle Database 12c. It starts with the idea of consolidation of databases on a cloud and how the Oracle 12c Multitenant architecture addresses the requirements. It consolidates the new features in Oracle 12c SQL and PL/SQL, and explains each of them with examples. It will help you to feel the essence of Oracle Database 12c and understand what the driving wheel of innovation is. A section on the Oracle Database 12c In-memory option will familiarize you with the breakthrough feature in the analytics and warehouse space.
Chapter 3, Designing PL/SQL Code, primarily focuses on the PL/SQL cursor's design and handling. You will get to learn the basics of cursor design, cursor types and cursor variables, handling cursors in PL/SQL, and design guidelines. This chapter will also include the enhancements made by Oracle Database 12c with respect to cursors.
Chapter 4, Using Collections, introduces you to the world of collections; namely, associative arrays, nested tables, and varrays. Taking you all the way from their creation in SQL and PL/SQL to design considerations, this chapter makes you wise enough to choose the right collection type in a given situation. A section on Oracle Database 12c enhancements to collections introduces a very handy feature that will allow you to join a table and collection.
Chapter 5, Using Advanced Interface Methods, focuses on a powerful feature of PL/SQL: how to execute external procedures in PL/SQL. You will learn and understand the specifics of executing a C or Java program in PL/SQL as an external procedure through step-by-step demonstration. This chapter also mentions the Oracle Database 12c enhancement which allows you to secure external procedures through an additional safety net.
Chapter 6, Virtual Private Database, provides a detailed overview of the Oracle Database Security Defense-in-depth architecture and focuses on one of the developer-centric features, known as the Virtual Private Database. Oracle Database 12c security enhancements and a demonstration of data redaction will make you understand Oracle's security offerings.
Chapter 7, Oracle SecureFiles, provides a thorough understanding of handling large objects in Oracle and focuses on storage optimizations made by SecureFiles. Introduced in Oracle 11g, SecureFiles is the new storage mechanism that scores high on its advanced features, such as compression, encryption, and deduplication. This chapter also helps you with the recommended migration methods from older LOBs to SecureFiles.
Chapter 8, Tuning the PL/SQL Code, introduces the best practices for tuning PL/SQL code. It starts with the PL/SQL optimizer and rolls through the benefits of native compilation, PL/SQL code writing skills, and code evaluation design. This chapter includes the changes in Oracle 12c with respect to large object handling.
Chapter 9, Result Cache, explains the result caching feature in Oracle Database. It is a powerful caching mechanism that enhances the performance of SQL queries and PL/SQL functions that are repeatedly executed on the server. This chapter also discusses the enhancements made to the feature in Oracle Database 12c.
Chapter 10, Analyzing, Profiling, and Tracing PL/SQL Code, details the techniques used to analyze, profile, and trace PL/SQL code. If you are troubleshooting PL/SQL code for performance, you must learn the profiling and tracing techniques. In an enterprise application environment, these practices are vital weapons in a developer's arsenal.
Chapter 11, Safeguarding PL/SQL Code against SQL injection, describes ways to protect your PL/SQL from being attacked. A vulnerable piece of code is prone to malicious attacks and runs the risk of giving away sensitive information. Efficient code writing and proofing the code from external attacks can help to minimizing the attack surface area. In this chapter, you will learn the practices for safeguarding your code against external threats.
Chapter 12, Working with Oracle SQL Developer, describes the benefits of the Oracle SQL Developer for developers, database administrators, and architects. This chapter not only helps you get started with SQL Developer, but also helps you gain a better understanding of the new features of SQL Developer 4.0 and 4.1.
If you are good with PL/SQL development basics, I'm sure you will enjoy reading this book. You will learn new ways to program efficiently in PL/SQL.
This book is for Oracle developers who are responsible for database management. Readers are expected to have basic knowledge of the Oracle Database and the fundamentals of PL/SQL programming. Certification aspirants can use this book to prepare for the 1Z0-146 examination in order to be come an Oracle Certified Professional in Advanced PL/SQL.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "The modified SELECT query is then executed in the HR schema of the database."
A block of code is set as follows:
/*Create the stored procedure to set the context attribute*/
CREATE OR REPLACE PROCEDURE p_app_context (p_val VARCHAR2)
IS
BEGIN
/*Create a namespace DEMO_CONTEXT*/
DBMS_SESSION.SET_CONTEXT(
NAMESPACE => 'DEMO_CONTEXT',
ATTRIBUTE => 'COUNTRY',
VALUE => P_VAL);
END;
/New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "This is how interpreted compilation works. In the case of native compilation,, a sharable dynamic linked library (DLL) is generated instead of a machine code."
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.