book

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

Name: Real World SQL and PL/SQL: Advice from the Experts
ISBN: 9781259640988

by Arup Nanda, Brendan Tierney, Heli Helskyaho, Martin Widlake, Alex Nuitjen

August 2016

Intermediate to advanced

512 pages

21h 2m

English

McGraw-Hill

Read now

Unlock full access

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/SQLSQLPL/SQLSummary
2 Expert Data Modeling and Implementing Business Logic
Implementing Business LogicBusiness Logic in Database ObjectsBusiness Logic in the CodeDesigning a Database and Data ModelsThe Design ProcessIntroduction to Oracle SQL Developer Data ModelerSummary
PART II Underutilized Advanced SQL Functionality
3 Handling Advanced and Complex Data Sets
Some Tools for Designing the DatabaseIntroduction to TablesTable ClusterViews and Materialized ViewsIntroduction to Data TypesInvisible ColumnsVirtual ColumnsAttribute ClusteringPartitioningConstraintsSome Tools for Implementing SQL and PL/SQL RequirementsCursorsRecordsCollectionsParallel QueryTable Functions and Pipelined Table FunctionsSummary
4 Regular Expressions
Basic Search and Escape PossibilitiesREGEXP FunctionsCharacter ClassesGreediness and Negating the ExpressionBackreferencesCheck ConstraintsReal-World ExamplesBreaking Up a Delimited StringSorting by the Numeric Part of a StringPattern Matching: MATCH_RECOGNIZESummary
5 Edition-Based Redefinition
Planned DowntimeTerminology UsedThe ConceptPreparation: Enable EditionsNE on E ProhibitionCreating a New EditionComplexity LevelsReplacing PL/SQL CodeChanging Table StructuresKeeping the Data in Sync Between EditionsLost UpdateRetiring the Old EditionTo Drop or Not to Drop?Changing the Default EditionSQL Developer and EBREBR and DBMS_REDACTSummary
PART III Essential Everyday Advanced PL/SQL
6 Running PL/SQL from SQL
SQL and PL/SQL FunctionsSTANDARD and DBMS_STANDARDSimplifying Nested SQL Functions with PL/SQLPL/SQL Function ConsiderationsParameters, Purity Levels, and DeterministicContext Switching OverheadLoss of Point-in-Time ViewPL/SQL Results CachingCorrect Implementation for the DISP_NAME FunctionalitySummary
7 Instrumenting and Profiling PL/SQL
SQL and RDBMS InstrumentationInstrumentation OverheadInstrumentation Is Built In by the Developer but Sometimes Seen Only by the DBAInstrumentation for DebuggingThe Difference Between Instrumentation, Profiling, and DebuggingInstrumentationProfilingDebuggingInstrumentation of PL/SQLDBMS_OUTPUTLogging TablesDBMS_APPLICATION_INFOOverview of Instrumentation OptionsInstrumentation PackagesProfilingDrawbacks of Profiling Production Code with DBMS_OUPUTUsing the PLSQL_LOG TableA Real-World Example of the Power of InstrumentationProfiling and Debugging PackagesOverview of the Profiling OptionsSummary
8 Dynamic SQL
Using Native Dynamic SQLUsing the DBMS_SQL PackageReturning a Result Set to the ClientCalling an Implicit Result Set from PL/SQLThe DBMS_SQL.TO_REFCURSOR FunctionThe DBMS_SQL.TO_CURSOR_NUMBER FunctionSummary
9 PL/SQL for Automation and Administration
PL/SQL and the DBASimple Task-Specific PL/SQL ScriptsInvestigating LONGs with PL/SQLComplex SQL or Simple PL/SQL: Identifying SQL with Identical Execution PlansA Lightweight Tool for Gathering and Preserving Session StatsHandling Database Statistics Rapidly Becoming StaleA Flexible Emergency Backup Script via PL/SQLControlling Administrative and Batch Tasks with PL/SQLThe Core Master-Detail Control TablesLogging and Error TablesProcess-Specific TablesPL/SQL Packages for Aiding Database Developers and AdministrationBuilt-in PL/SQL Packages Covered Elsewhere in This BookDBMS_WORKLOAD_REPOSITORYDBMS_METADATAUTL_FILEDBMS_UTILITYSummary
PART IV Advanced Analytics
10 In-Database Data Mining Using Oracle Data Mining
Overview of Oracle Advanced Analytics OptionOracle Data Miner GUI ToolSetting Up Oracle Data Miner and the Demo Data SetsCreating an Oracle Data Miner WorkflowOracle Data Mining Using SQL and PL/SQLOracle Data Mining PL/SQL APIOracle Data Mining SQL FunctionsClassification Using Oracle Data MiningPreparing Your DataBuilding the Classification ModelEvaluating the Classification ModelApplying the Classification Model to New DataOracle Data Mining: Other TechniquesSummary
11 Oracle R Enterprise
The ORE Transparency LayerInstalling Oracle R EnterpriseInstallation PrerequisitesServer InstallationClient InstallationUsing Oracle Pre-Built AppliancesGetting Started and Connecting to the Oracle DatabaseExploring Your Data Using OREBuilding Data Mining Models Using OREAssociation Rule AnalysisBuilding a Decision Tree Model and Scoring New DataBuilding a Neural Network Model and Scoring New DataEmbedded R ExecutionUsing rqEval to Call Functions and Return a Data SetUsing rqTableEval to Apply a Data Mining Model to Your DataCreating and Using ORE Graphics in Your DashboardsSummary
12 Predictive Queries in Oracle 12c
What Are Predictive Queries and Why Do You Need Them?Oracle Analytic FunctionsThe Magic of the Partitioning ClauseCreating Predictive QueriesCreating Predictive Queries in SQL DeveloperCreating Predictive Queries in Oracle Data MinerPredictive Queries Using SQLClassification Using Predictive QueriesRegression Using Predictive QueriesAnomaly Detection Using Predictive QueriesClustering Using Predictive QueriesWorking with Predictive QueriesSummary
PART V Database Security
13 Redaction and Masking
Why Redaction?PL/SQL-Only Solution for RedactionRandomizationThe View for RedactionCleanupRedaction and Masking PackFixed ValuesOther Types of RedactionSQL Developer AccessPolicy AdministrationCleanupSummary
14 Encryption and Hashing
What Is Encryption?Introduction to EncryptionEncryption ComponentsThe Effect of Key LengthSymmetric vs. Asymmetric EncryptionEncryption AlgorithmsPadding and ChainingThe Crypto PackageDecrypting DataInitialization Vector or SaltKey ManagementProtecting the Data from the DBAEncrypting RAW DataA Complete Encryption SolutionOption 1: Alter the TableOption 2: Encrypt the Columns Themselves, and Use the View to Show the Decrypted DataStore the Keys Separate from the TableStoring the KeysTransparent Data EncryptionSetting Up TDEAdding TDE to Existing TablesTablespace TDEPerforming TDE Key and Password ManagementAdding SaltCryptographic HashingThe Case of the Suspicious SandwichHashing with PL/SQLOther Uses of HashingMessage Authentication CodePutting It All Together: A ProjectOption 1Option 2Quick ReferenceGETRANDOMBYTESENCRYPTDECRYPTHASHMACSummary
15 SQL Injection and Code Security
Execution ModelsProgram SecurityThe Traditional ApproachRole-Based Program SecurityCode WhitelistingRestricting Inherited PrivilegePL/SQL Injection AttacksSanitization of the Input StringReducing SQL Injection PossibilitiesSummary
16 Fine Grained Access Control and Application Contexts
Introduction to Fine Grained Access ControlVirtual Private DatabaseWhy Learn about VPD?A Simple ExampleIntermediate VPDPerforming an Update CheckStatic vs. Dynamic PoliciesImproving PerformanceControlling the Type of Table AccessColumn-Sensitive VPDOther Classes of DynamismShared Static PolicyContext-Sensitive PolicyShared Context-Sensitive PolicyTroubleshootingORA-28110: Policy Function or Package Has ErrorORA-28112: Failed to Execute Policy FunctionORA-28113: Policy Predicate Has ErrorDirect-Path OperationsChecking the Query RewriteInteractions with Other Oracle FeaturesReferential Integrity ConstraintsReplicationMaterialized ViewsApplication ContextsA Simple ExampleThe Security in Application ContextsContexts as Predicates in VPDIdentifying Nondatabase UsersClean UpQuick ReferencePackage DBMS_RLSData Dictionary ViewsSummary
Index

Overview

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Beginning Oracle SQL for Oracle Database 18c: From Novice to Professional

Publisher Resources

ISBN: 9781259640988

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills