Advanced Oracle PL/SQL Developer's Guide - Second Edition

Advanced Oracle PL/SQL Developer's Guide - Second Edition

by Saurabh K. Gupta
Released February 2016
Publisher(s): Packt Publishing
ISBN: 9781785284809

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Master the advanced concepts of PL/SQL for professional-level certification and learn the new capabilities of Oracle Database 12c

About This Book

  • Learn advanced application development features of Oracle Database 12c and prepare for the 1Z0-146 examination
  • Build robust and secure applications in Oracle PL/SQL using the best practices
  • Packed with feature demonstrations and illustrations that will help you learn and understand the enhanced capabilities of Oracle Database 12c

Who This Book Is For

This book is for Oracle developers responsible for database management. Readers are expected to have basic knowledge of Oracle Database and the fundamentals of PL/SQL programming. Certification aspirants can use this book to prepare for 1Z0-146 examination in order to be an Oracle Certified Professional in Advanced PL/SQL.

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

In Detail

Oracle Database is one of the most popular databases and allows users to make efficient use of their resources and to enhance service levels while reducing the IT costs incurred. Oracle Database is sometimes compared with Microsoft SQL Server, however, Oracle Database clearly supersedes SQL server in terms of high availability and addressing planned and unplanned downtime. Oracle PL/SQL provides a rich platform for application developers to code and build scalable database applications and introduces multiple new features and enhancements to improve development experience. 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.

Style and approach

The book follows the structure of the Oracle Certification examination but doesn't restrict itself to the exam objectives. Advanced concepts have been explained in an easy-to-understand style, supported with feature demonstrations and case illustrations.

Table of contents

  1. Advanced Oracle PL/SQL Developer's Guide Second Edition
    1. Table of Contents
    2. Advanced Oracle PL/SQL Developer's Guide Second Edition
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why subscribe?
        2. Free access for Packt account holders
        3. Instant updates on new Packt books
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    8. 1. Overview of PL/SQL Programming Concepts
      1. Introduction to PL/SQL
        1. PL/SQL program fundamentals
      2. Cursors – an overview
        1. The cursor execution cycle
        2. Cursor attributes
        3. Cursor FOR loop
      3. Exception handling in PL/SQL
        1. System-defined exceptions
        2. User-defined exceptions
          1. The RAISE_APPLICATION_ERROR procedure
        3. Exception propagation
      4. Creating stored procedures
        1. Executing a procedure
      5. Functions
        1. Functions – execution methods
        2. Restrictions on calling functions from SQL expressions
      6. A PL/SQL package
      7. Oracle Database 12c enhancements to PL/SQL subprograms
      8. Managing database dependencies
        1. Displaying the direct and indirect dependencies
        2. Dependency metadata
        3. Dependency issues and enhancements
      9. Reviewing Oracle-supplied packages
      10. Oracle SQL Developer
        1. Oracle SQL Developer for DBA, Developers, and Application Architects
        2. SQL Developer 4.0
      11. Summary
      12. Practice exercise
    9. 2. Oracle 12c SQL and PL/SQL New Features
      1. Database consolidation and the new Multitenant architecture
        1. The Oracle Database 12c Multitenant architecture – features
          1. Multitenant for Consolidation
          2. Plug/unplug
          3. Manage Many as One
          4. Rapid provisioning
          5. CDB Resource Management
          6. Common users and local users
      2. Oracle 12c SQL and PL/SQL new features
        1. IDENTITY columns
        2. Default column value to a sequence in Oracle 12c
          1. The DEFAULT ON NULL clause
        3. Support for 32K VARCHAR2
        4. Row limiting using FETCH FIRST
        5. Invisible columns
        6. Temporal databases
        7. In-Database Archiving
        8. Defining a PL/SQL subprogram in the SELECT query and PRAGMA UDF
          1. Test setup
          2. Comparative analysis
        9. The PL/SQL program unit white listing
        10. Granting roles to PL/SQL program units
          1. Test setup
        11. Miscellaneous PL/SQL enhancements
      3. The Oracle Database 12c (12.1.0.2) In-Memory option
        1. The challenge
        2. The problem statement and Oracle Database 12c In-Memory
        3. Oracle Database 12c In-Memory option features
        4. The Oracle Database 12c In-Memory Architecture
        5. Controlling the In-Memory column store
        6. The INMEMORY clause
        7. Performance optimizations
        8. In-Memory Advisor
        9. Oracle Database In-Memory benefits
      4. Summary
    10. 3. Designing PL/SQL Code
      1. Cursor structures
        1. Cursor execution cycle
        2. Cursor attributes
        3. Implicit cursors
        4. Explicit cursors
      2. Cursor variables
        1. Strong and weak ref cursor types
          1. Working with cursor variables
          2. SYS_REFCURSOR
        2. Cursor variables as arguments
        3. Cursor variables – restrictions
        4. Cursor design considerations
        5. Cursor design–guidelines
      3. Implicit statement results in Oracle Database 12c
      4. Subtypes
        1. Subtype classification
        2. Type compatibility with subtypes
      5. Summary
      6. Practice exercise
    11. 4. Using Collections
      1. Introduction to collections
      2. Collection types
      3. Associative arrays
      4. Nested tables
        1. Modify and drop a nested table object type
        2. Design considerations of a nested table
          1. Nested table storage
          2. Nested table in an index - organized table
          3. Nested table locators
        3. Nested table as the schema object
          1. Operations on a nested table type column
            1. Create a nested table instance
            2. Querying a nested table column
          2. Nested table collection type in PL/SQL
            1. Collection initialization
        4. Querying the nested table metadata
        5. Nested table comparison functions
        6. Multiset operations on nested tables
      5. Varray
        1. Varray as a schema object
          1. Operations on varray type columns
            1. Inserting varray collection type instance
            2. Querying varray column
            3. Updating the varray instance
        2. Varray in PL/SQL
      6. Comparing the collection types
        1. Selecting the appropriate collection type
      7. Oracle 12c enhancements to collections
      8. PL/SQL collection methods
        1. EXISTS
        2. COUNT
        3. LIMIT
        4. FIRST and LAST
        5. PRIOR and NEXT
        6. EXTEND
        7. TRIM
        8. DELETE
      9. Summary
      10. Practice exercise
    12. 5. Using Advanced Interface Methods
      1. Overview of External Procedures
        1. External Procedures
      2. Components of external procedure execution flow
        1. The extproc agent
        2. The Library object
          1. Callout and Callback
        3. Call Specification
        4. How an External Procedure executes
        5. Environment setup
          1. TNSNAMES.ora
          2. EXTPROC.ora
      3. Executing external C programs from PL/SQL
      4. Securing External Procedures with Oracle Database 12c
      5. Executing Java programs from PL/SQL
        1. Loading a Java class into a database
        2. Steps to execute a Java class from an Oracle PL/SQL unit
      6. Summary
      7. Practice exercise
    13. 6. Virtual Private Database
      1. Oracle Database Security overview
      2. Fine-Grained Access Control
        1. How FGAC works
      3. Virtual Private Database
        1. How does Virtual Private Database work?
          1. Column-level Virtual Private Database
          2. Virtual Private Database with Oracle Database 12c Multitenant
        2. Virtual Private Database components
          1. Application Context
          2. Virtual Private Database policy function
          3. Policy types
          4. The DBMS_RLS package
        3. Demonstration
        4. Virtual Private Database features and best practices
      4. Virtual Private Database metadata
        1. Policy utilities—refresh and drop
      5. Oracle Database 12c Security enhancements
        1. Oracle Database 12c Data Redaction
          1. Data Redaction exemptions and miscellaneous features
          2. Data Redaction function types
          3. Demonstration
          4. The Data Redaction metadata
      6. Summary
      7. Practice exercise
    14. 7. Oracle SecureFiles
      1. Introduction to Large Objects
        1. Classification of Large Object datatypes
          1. Internal LOB
            1. Persistent and Temporary LOB
          2. External LOB
        2. LOB restrictions
      2. LOB data types in Oracle
        1. BLOB and CLOB
        2. BFILE
        3. Some more related stuff
          1. The LOB locator
          2. LOB instance initialization
          3. The DBMS_LOB package
            1. The DBMS_LOB constants
            2. The DBMS_LOB data types
            3. The DBMS_LOB subprograms
        4. LOB usage notes
      3. Oracle SecureFiles
        1. Deduplication and compression
        2. Encryption
        3. File System Logging
        4. Write Gather Cache
        5. Free space management
        6. BasicFiles and SecureFiles
        7. The db_securefile parameter
      4. Working with LOBs
        1. LOB metadata
        2. Enabling the advanced features of a SecureFile
        3. Populating the LOB data
        4. Temporary LOB operations
          1. Managing temporary LOBs
        5. Working with a temporary LOB
      5. Migrating LONG to LOBs
        1. Use the ALTER TABLE command
        2. Using the TO_LOB function
        3. Online Table Redefinition
      6. Migrating BasicFiles to SecureFiles
      7. Oracle Database 12c enhancements to SecureFiles
      8. Summary
      9. Practice exercise
    15. 8. Tuning the PL/SQL Code
      1. The PL/SQL Compiler
        1. Subprogram inlining in PL/SQL
          1. PRAGMA INLINE
          2. PLSQL_OPTIMIZE_LEVEL
            1. Case 1: When PLSQL_OPTIMIZE_LEVEL = 0
            2. Case 2: When PLSQL_OPTIMIZE_LEVEL = 1
            3. Case 3: When PLSQL_OPTIMIZE_LEVEL = 2
            4. Case 4: When PLSQL_OPTIMIZE_LEVEL = 3
      2. Native and interpreted compilation techniques
        1. Oracle Database 11g Real Native Compilation
        2. Selecting the appropriate compilation mode
        3. Setting the compilation mode
        4. Querying the compilation settings
        5. Compiling a program unit for native or interpreted compilation
        6. Recompiling a database for a PL/SQL native or interpreted compilation
      3. Tuning PL/SQL code
        1. Build secure applications using bind variables
        2. Call parameters by reference
        3. Avoiding an implicit data type conversion
        4. Understanding the NOT NULL constraint
        5. Selection of an appropriate numeric data type
        6. Bulk processing in PL/SQL
          1. BULK COLLECT
          2. FORALL
            1. FORALL and exception handling
      4. Summary
      5. Practice exercise
    16. 9. Result Cache
      1. Oracle Database 11g Result Cache
        1. What is the Server Result Cache?
        2. Configuring the Server Result Cache
        3. Result Cache versus Buffer Cache
        4. Result Cache versus Oracle 12c Database In-Memory
        5. Result Cache versus In-Memory Database Cache
      2. SQL query Result Cache
        1. Monitoring the SQL Result Cache
        2. Invalidation of the SQL Result Cache
        3. Read consistency of the SQL Result Cache
        4. Limitations
      3. PL/SQL Function Result Cache
        1. Does it sound similar to deterministic functions?
        2. Differences between Result Cache and other caching techniques
        3. Illustration
        4. Monitoring the PL/SQL Result Cache
        5. Invalidation of the PL/SQL Result Cache
        6. Limitation
      4. OCI Client results cache
      5. The DBMS_RESULT_CACHE package
        1. Displaying the result cache memory report
        2. Oracle Database 12c enhancements to the PL/SQL function Result Cache
      6. Result cache in Real Application Clusters
      7. Summary
      8. Practice exercise
    17. 10. Analyzing, Profiling, and Tracing PL/SQL Code
      1. A sample PL/SQL program
      2. Tracking PL/SQL coding information
        1. USER_ARGUMENTS
        2. USER_OBJECTS
        3. USER_OBJECT_SIZE
        4. USER_SOURCE
        5. USER_PROCEDURES
        6. USER_PLSQL_OBJECT_SETTINGS and USER_STORED_SETTINGS
        7. USER_DEPENDENCIES
        8. The DBMS_DESCRIBE package
        9. Tracking the program execution subprogram call stack
        10. Tracking propagating exceptions in PL/SQL code
      3. Determining identifier types and usages
        1. USER_IDENTIFIERS
        2. The PL/Scope tool
          1. The PLSCOPE_SETTINGS parameter
      4. The DBMS_METADATA package
        1. DBMS_METADATA data types and subprograms
        2. Parameter requirements
        3. The DBMS_METADATA transformation parameters and filters
        4. Demonstration
      5. Tracing PL/SQL programs using DBMS_TRACE
        1. Installing the DBMS_TRACE package
        2. DBMS_TRACE subprograms
        3. Compiling a PL/SQL program for debugging
        4. Viewing the PL/SQL trace information
        5. Steps to trace PL/SQL program execution
      6. Profiling PL/SQL code
        1. The DBMS_HPROF package
          1. Differences between DBMS_PROFILER and DBMS_HPROF
          2. DBMS_HPROF subprograms
        2. Collecting raw profile data
          1. Interpreting the raw profiler data
        3. Analyzing profiler data
          1. Creating the profiler tables
          2. Analyzing the profiler output
          3. Querying the profiler tables
        4. The plshprof utility
          1. What do these reports reveal?
      7. Summary
      8. Practice exercise
    18. 11. Safeguarding PL/SQL Code against SQL injection
      1. What is SQL injection?
        1. SQL injection targets
        2. How to exploit the PL/SQL code?
      2. Preventing SQL injection attacks
        1. Sanitizing inputs using DBMS_ASSERT
          1. Choose the right subprogram for the right identifier
            1. Unquoted identifiers
            2. Quoted identifiers
            3. Literals
          2. DBMS_ASSERT – limitations
        2. Use of bind variables to prevent injection attacks
        3. Best practices to avoid SQL injection
      3. Testing the code for SQL injection flaws
        1. Test strategy
        2. An effective code review
        3. Static code analysis
        4. Fuzz tools
        5. Generating test cases
      4. Summary
      5. Practice exercise
    19. 12. Working with Oracle SQL Developer
      1. An overview of SQL Developer
        1. Key differentiators
        2. History and background
        3. SQL Developer for Developers
        4. SQL Developer for Database Administrators
        5. SQLcl – The new SQL command line
      2. Getting started with SQL Developer
        1. Creating a database connection
        2. Using the SQL worksheet
        3. Core features of SQL Developer
          1. Object Browser
          2. PL/SQL Editor and Debugger
          3. DBA Panel
          4. Database Utilities
          5. The Data Modeler
          6. SQL Developer reports
          7. Version control
          8. The SQL Translation Framework
      3. SQL Developer 4.0 and 4.1 New Features
      4. Summary
    20. Index

Product information

  • Title: Advanced Oracle PL/SQL Developer's Guide - Second Edition
  • Author(s): Saurabh K. Gupta
  • Release date: February 2016
  • Publisher(s): Packt Publishing
  • ISBN: 9781785284809