Advanced Oracle PL/SQL Developer’s Guide – Second Edition

B04511_CoverPleased to announce the availability of my book “Advanced PL/SQL Developer’s Guide – Second edition” by Packt Publishing.

Advanced Oracle PL/SQL Developer’s Guide, Second Edition is a handy technical reference for seasoned professionals in the database development space. This book starts with a refresher of fundamental concepts of PL/SQL, such as anonymous block, subprograms, and exceptions, and prepares you for the upcoming advanced concepts. The next chapter introduces you to the new features of Oracle Database 12c, not limited to PL/SQL. In this chapter, you will understand some of the most talked about features such as Multitenant and Database In-Memory. Moving forward, each chapter introduces advanced concepts with the help of demonstrations, and provides you with the latest update from Oracle Database 12c context. This helps you to visualize the pre- and post-applications of a feature over the database releases. By the end of this book, you will have become an expert in PL/SQL programming and will be able to implement advanced concepts of PL/SQL for efficient management of Oracle Database.


What You Will Learn

  • Learn and understand the key SQL and PL/SQL features of Oracle Database 12c
  • Understand the new Multitenant architecture and Database In-Memory option of Oracle Database 12c
  • Know more about the advanced concepts of the Oracle PL/SQL language such as external procedures, securing data using Virtual Private Database (VPD), SecureFiles, and PL/SQL code tracing and profiling
  • Implement Virtual Private Databases to prevent unauthorized data access
  • Trace, analyze, profile, and debug PL/SQL code while developing database applications
  • Integrate the new application development features of Oracle Database 12c with the current concepts
  • Discover techniques to analyze and maintain PL/SQL code
  • Get acquainted with the best practices of writing PL/SQL code and develop secure applications

Book reviewers

  • Kamran Agayev, Oracle ACE Director, Oracle Certified Master
  • Patrick Barel, Oracle ACE, Oracle Developer Choice Award for PL/SQL (Oracle Open World 2015)
  • Nassyam Basha, Oracle ACE Director, Oracle Certified Master
  • Wissem-EL-Khilfi, Oracle ACE
  • Davor Zelic
  • Sean Stacey, Director, Database Product Management at Oracle

Place your orders at –

Packt – –
Barnes and Noble –

Chapter outline

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, 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, Protect PL/SQL Code from 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 in 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.