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