Real World SQL and PL/SQL: Advice from the Experts

Real World SQL and PL/SQL: Advice from the Experts

by Arup Nanda, Brendan Tierney, Heli Helskyaho, Martin Widlake, Alex Nuitjen
Released August 2016
Publisher(s): McGraw-Hill
ISBN: 9781259640988

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 Underutilized Advanced Features of SQL and PL/SQL

This hands-on guide from Oracle Press shows how to fully exploit lesser known but extremely useful SQL and PL/SQL features―and how to effectively use both languages together. Written by a team of Oracle ACE Directors, Real-World SQL and PL/SQL: Advice from the Experts features best practices, detailed examples, and insider tips that clearly demonstrate how to write, troubleshoot, and implement code for a wide variety of practical applications. The book thoroughly explains underutilized SQL and PL/SQL functions and lays out essential development strategies. Data modeling, advanced analytics, database security, secure coding, and administration are covered in complete detail.

Learn how to:

• Apply advanced SQL and PL/SQL tools and techniques
• Understand SQL and PL/SQL functionality and determine when to use which language
• Develop accurate data models and implement business logic
• Run PL/SQL in SQL and integrate complex datasets
• Handle PL/SQL instrumenting and profiling
• Use Oracle Advanced Analytics and Oracle R Enterprise
• Build and execute predictive queries
• Secure your data using encryption, hashing, redaction, and masking
• Defend against SQL injection and other code-based attacks
• Work with Oracle Virtual Private Database

Code examples in the book are available for download at www.MHProfessional.com.

TAG: For a complete list of Oracle Press titles, visit www.OraclePressBooks.com

Table of contents

  1. Cover
  2. Title Page
  3. Copyright
  4. About the Authors
  5. Contents at a Glance
  6. Contents
  7. Foreword
  8. Preface
  9. Acknowledgments
  10. Introduction
  11. PART I The Importance of SQL, PL/SQL, and a Good Data Model
    1. 1 SQL and PL/SQL
      1. Introduction to SQL and PL/SQL
      2. SQL
      3. PL/SQL
      4. Summary
    2. 2 Expert Data Modeling and Implementing Business Logic
      1. Implementing Business Logic
        1. Business Logic in Database Objects
        2. Business Logic in the Code
      2. Designing a Database and Data Models
        1. The Design Process
        2. Introduction to Oracle SQL Developer Data Modeler
      3. Summary
  12. PART II Underutilized Advanced SQL Functionality
    1. 3 Handling Advanced and Complex Data Sets
      1. Some Tools for Designing the Database
        1. Introduction to Tables
        2. Table Cluster
        3. Views and Materialized Views
        4. Introduction to Data Types
        5. Invisible Columns
        6. Virtual Columns
        7. Attribute Clustering
        8. Partitioning
        9. Constraints
      2. Some Tools for Implementing SQL and PL/SQL Requirements
        1. Cursors
        2. Records
        3. Collections
        4. Parallel Query
        5. Table Functions and Pipelined Table Functions
      3. Summary
    2. 4 Regular Expressions
      1. Basic Search and Escape Possibilities
      2. REGEXP Functions
      3. Character Classes
        1. Greediness and Negating the Expression
        2. Backreferences
        3. Check Constraints
      4. Real-World Examples
        1. Breaking Up a Delimited String
        2. Sorting by the Numeric Part of a String
      5. Pattern Matching: MATCH_RECOGNIZE
      6. Summary
    3. 5 Edition-Based Redefinition
      1. Planned Downtime
      2. Terminology Used
      3. The Concept
      4. Preparation: Enable Editions
        1. NE on E Prohibition
        2. Creating a New Edition
      5. Complexity Levels
        1. Replacing PL/SQL Code
        2. Changing Table Structures
        3. Keeping the Data in Sync Between Editions
        4. Lost Update
      6. Retiring the Old Edition
        1. To Drop or Not to Drop?
        2. Changing the Default Edition
      7. SQL Developer and EBR
      8. EBR and DBMS_REDACT
      9. Summary
  13. PART III Essential Everyday Advanced PL/SQL
    1. 6 Running PL/SQL from SQL
      1. SQL and PL/SQL Functions
        1. STANDARD and DBMS_STANDARD
        2. Simplifying Nested SQL Functions with PL/SQL
      2. PL/SQL Function Considerations
        1. Parameters, Purity Levels, and Deterministic
        2. Context Switching Overhead
        3. Loss of Point-in-Time View
        4. PL/SQL Results Caching
        5. Correct Implementation for the DISP_NAME Functionality
      3. Summary
    2. 7 Instrumenting and Profiling PL/SQL
      1. SQL and RDBMS Instrumentation
      2. Instrumentation Overhead
      3. Instrumentation Is Built In by the Developer but Sometimes Seen Only by the DBA
      4. Instrumentation for Debugging
      5. The Difference Between Instrumentation, Profiling, and Debugging
        1. Instrumentation
        2. Profiling
        3. Debugging
      6. Instrumentation of PL/SQL
        1. DBMS_OUTPUT
        2. Logging Tables
        3. DBMS_APPLICATION_INFO
        4. Overview of Instrumentation Options
        5. Instrumentation Packages
      7. Profiling
        1. Drawbacks of Profiling Production Code with DBMS_OUPUT
        2. Using the PLSQL_LOG Table
        3. A Real-World Example of the Power of Instrumentation
        4. Profiling and Debugging Packages
        5. Overview of the Profiling Options
      8. Summary
    3. 8 Dynamic SQL
      1. Using Native Dynamic SQL
      2. Using the DBMS_SQL Package
        1. Returning a Result Set to the Client
        2. Calling an Implicit Result Set from PL/SQL
        3. The DBMS_SQL.TO_REFCURSOR Function
        4. The DBMS_SQL.TO_CURSOR_NUMBER Function
      3. Summary
    4. 9 PL/SQL for Automation and Administration
      1. PL/SQL and the DBA
      2. Simple Task-Specific PL/SQL Scripts
        1. Investigating LONGs with PL/SQL
        2. Complex SQL or Simple PL/SQL: Identifying SQL with Identical Execution Plans
        3. A Lightweight Tool for Gathering and Preserving Session Stats
        4. Handling Database Statistics Rapidly Becoming Stale
        5. A Flexible Emergency Backup Script via PL/SQL
      3. Controlling Administrative and Batch Tasks with PL/SQL
        1. The Core Master-Detail Control Tables
        2. Logging and Error Tables
        3. Process-Specific Tables
      4. PL/SQL Packages for Aiding Database Developers and Administration
        1. Built-in PL/SQL Packages Covered Elsewhere in This Book
        2. DBMS_WORKLOAD_REPOSITORY
        3. DBMS_METADATA
        4. UTL_FILE
        5. DBMS_UTILITY
      5. Summary
  14. PART IV Advanced Analytics
    1. 10 In-Database Data Mining Using Oracle Data Mining
      1. Overview of Oracle Advanced Analytics Option
      2. Oracle Data Miner GUI Tool
        1. Setting Up Oracle Data Miner and the Demo Data Sets
        2. Creating an Oracle Data Miner Workflow
      3. Oracle Data Mining Using SQL and PL/SQL
        1. Oracle Data Mining PL/SQL API
        2. Oracle Data Mining SQL Functions
      4. Classification Using Oracle Data Mining
        1. Preparing Your Data
        2. Building the Classification Model
        3. Evaluating the Classification Model
        4. Applying the Classification Model to New Data
      5. Oracle Data Mining: Other Techniques
      6. Summary
    2. 11 Oracle R Enterprise
      1. The ORE Transparency Layer
      2. Installing Oracle R Enterprise
        1. Installation Prerequisites
        2. Server Installation
        3. Client Installation
        4. Using Oracle Pre-Built Appliances
      3. Getting Started and Connecting to the Oracle Database
      4. Exploring Your Data Using ORE
      5. Building Data Mining Models Using ORE
        1. Association Rule Analysis
        2. Building a Decision Tree Model and Scoring New Data
        3. Building a Neural Network Model and Scoring New Data
      6. Embedded R Execution
        1. Using rqEval to Call Functions and Return a Data Set
        2. Using rqTableEval to Apply a Data Mining Model to Your Data
        3. Creating and Using ORE Graphics in Your Dashboards
      7. Summary
    3. 12 Predictive Queries in Oracle 12c
      1. What Are Predictive Queries and Why Do You Need Them?
        1. Oracle Analytic Functions
        2. The Magic of the Partitioning Clause
      2. Creating Predictive Queries
        1. Creating Predictive Queries in SQL Developer
        2. Creating Predictive Queries in Oracle Data Miner
      3. Predictive Queries Using SQL
        1. Classification Using Predictive Queries
        2. Regression Using Predictive Queries
        3. Anomaly Detection Using Predictive Queries
        4. Clustering Using Predictive Queries
      4. Working with Predictive Queries
      5. Summary
  15. PART V Database Security
    1. 13 Redaction and Masking
      1. Why Redaction?
      2. PL/SQL-Only Solution for Redaction
        1. Randomization
        2. The View for Redaction
        3. Cleanup
      3. Redaction and Masking Pack
        1. Fixed Values
        2. Other Types of Redaction
        3. SQL Developer Access
        4. Policy Administration
        5. Cleanup
      4. Summary
    2. 14 Encryption and Hashing
      1. What Is Encryption?
      2. Introduction to Encryption
        1. Encryption Components
        2. The Effect of Key Length
        3. Symmetric vs. Asymmetric Encryption
        4. Encryption Algorithms
        5. Padding and Chaining
        6. The Crypto Package
        7. Decrypting Data
        8. Initialization Vector or Salt
        9. Key Management
        10. Protecting the Data from the DBA
        11. Encrypting RAW Data
      3. A Complete Encryption Solution
        1. Option 1: Alter the Table
        2. Option 2: Encrypt the Columns Themselves, and Use the View to Show the Decrypted Data
        3. Store the Keys Separate from the Table
        4. Storing the Keys
      4. Transparent Data Encryption
        1. Setting Up TDE
        2. Adding TDE to Existing Tables
        3. Tablespace TDE
        4. Performing TDE Key and Password Management
        5. Adding Salt
      5. Cryptographic Hashing
        1. The Case of the Suspicious Sandwich
        2. Hashing with PL/SQL
        3. Other Uses of Hashing
      6. Message Authentication Code
      7. Putting It All Together: A Project
        1. Option 1
        2. Option 2
      8. Quick Reference
        1. GETRANDOMBYTES
        2. ENCRYPT
        3. DECRYPT
        4. HASH
        5. MAC
      9. Summary
    3. 15 SQL Injection and Code Security
      1. Execution Models
      2. Program Security
        1. The Traditional Approach
        2. Role-Based Program Security
      3. Code Whitelisting
      4. Restricting Inherited Privilege
      5. PL/SQL Injection Attacks
        1. Sanitization of the Input String
        2. Reducing SQL Injection Possibilities
      6. Summary
    4. 16 Fine Grained Access Control and Application Contexts
      1. Introduction to Fine Grained Access Control
      2. Virtual Private Database
      3. Why Learn about VPD?
      4. A Simple Example
      5. Intermediate VPD
        1. Performing an Update Check
        2. Static vs. Dynamic Policies
      6. Improving Performance
        1. Controlling the Type of Table Access
        2. Column-Sensitive VPD
      7. Other Classes of Dynamism
        1. Shared Static Policy
        2. Context-Sensitive Policy
        3. Shared Context-Sensitive Policy
      8. Troubleshooting
        1. ORA-28110: Policy Function or Package Has Error
        2. ORA-28112: Failed to Execute Policy Function
        3. ORA-28113: Policy Predicate Has Error
        4. Direct-Path Operations
        5. Checking the Query Rewrite
      9. Interactions with Other Oracle Features
        1. Referential Integrity Constraints
        2. Replication
        3. Materialized Views
      10. Application Contexts
        1. A Simple Example
        2. The Security in Application Contexts
        3. Contexts as Predicates in VPD
        4. Identifying Nondatabase Users
      11. Clean Up
      12. Quick Reference
        1. Package DBMS_RLS
        2. Data Dictionary Views
      13. Summary
  16. Index

Product information

  • Title: Real World SQL and PL/SQL: Advice from the Experts
  • Author(s): Arup Nanda, Brendan Tierney, Heli Helskyaho, Martin Widlake, Alex Nuitjen
  • Release date: August 2016
  • Publisher(s): McGraw-Hill
  • ISBN: 9781259640988