Real World SQL and PL/SQL

934 Pages·2017·45.45 MB·English

by  Arup Nanda

About the Authors
Arup Nanda, Oracle ACE Director, Oak Table Network member, and winner of
Oracle’s DBA of the Year award in 2003 and Enterprise Architect of the Year in
2012, has been an Oracle DBA and developer for 22 years and counting. He is
the principal global database architect of a New York–area multinational
company. He has coauthored six books, written more than 500 published
articles, presented more than 300 sessions, and taught multi-day-long seminars
in over 20 countries. He is an editor of SELECT Journal, the International
Oracle User Group publication, and is a member of the board of directors of
Exadata SIG.
Twitter: @ArupNanda
Blog: arup.blogspot.com
Email: [e-mail]
Brendan Tierney, Oracle ACE Director, is an independent consultant
(Oralytics) and lectures on data science, databases, and Big Data in the Dublin
Institute of Technology/Dublin Technological University. He has 24+ years of
experience working in the areas of data mining, data science, Big Data, and data
warehousing. Brendan is a recognized data science and Big Data expert and has
worked on projects in Ireland, the UK, Belgium, Holland, Norway, Spain,
Canada, and the U.S. Brendan is active in the Oracle User Group community,
where he is one of the leaders for the OUG in Ireland. Brendan is the editor of
the UKOUG Oracle Scene magazine, a regular speaker at conferences around
the world, and an active blogger, and he also writes articles for OTN, Oracle
Scene, IOUG SELECT Journal, ODTUG Technical Journal, and ToadWorld. He
is also on the board of directors for DAMA in Ireland. Brendan has published
two other books with Oracle Press (Predictive Analytics Using Oracle Data
Miner and Oracle R Enterprise: Harnessing the Power of R in Oracle
Database). These books are available online.
Twitter: @brendantierney
Web and Blog: www.oralytics.com
Email: [e-mail]
Heli Helskyaho, Oracle ACE Director, is the CEO of Miracle Finland Oy and
an ambassador for EOUC (EMEA Oracle Users Group Community). Heli holds
a master’s degree in computer science from Helsinki University and she
specializes in databases. At the moment she is working on her doctoral studies
on Big Data, schema discovery, and semi-structured data. She has been working
in IT since 1990 and with Oracle products since 1993. She has been in several
positions, but every role has always included database design. Heli believes that
good database design and good documentation reduce performance problems
and make solving them easier. Heli is also an Oracle ACE Director and a
frequent speaker at many conferences. She is the author of Oracle SQL
Developer Data Modeler for Database Design Mastery (Oracle Press, 2015) and
one of the first winners of Oracle Database Developer Choice Award (Devvy) in
the Database Design category in 2015.
Twitter: @helifromfinland
Blog: helifromfinland.wordpress.com
Email: [e-mail]
Martin Widlake, Oracle ACE Director and OakTable Network member, has
been working with Oracle technology since 1992 when he started as a Forms 3
and CASE developer, so he has been using PL/SQL for 24+ years. Martin has
been a development DBA for the last two decades, mostly working on VLDBs
and the latest versions of Oracle. He was named Oracle Beta Tester of the year
in 2003. For the last eight years he has been an independent consultant
(ORA600, Ltd.) specializing in database design, performance, and PL/SQL
development. Martin has been a regular presenter at conferences and user groups
since 2002, both at home (the UK) and internationally, and also writes articles
for magazines. He is a strong advocate of user groups and is an active member of
the UKOUG, chairing Special Interest Groups and helping organize the annual
technical conference for the last few years. He is also deputy editor of Oracle
Scene magazine. Martin maintains a blog that is both technical and also includes
his popular “Friday Philosophies”—light-hearted articles about IT management
and the odder aspects of our working lives.
Twitter: @MDWidlake
Blog: mwidlake.wordpress.com
Email: [e-mail]
Alex Nuijten, Oracle ACE Director, is an independent consultant (allAPEX),
specializing in Oracle database development with PL/SQL and Oracle
Application Express (APEX). Besides his consultancy work, he conducts
training classes, mainly in APEX, SQL, and PL/SQL. Alex has been a speaker at
numerous international conferences, such as ODTUG, Oracle Open World,
UKOUG, IOUG, OUGF, BGOUG, OGH APEX World, and OBUG. For his
presentations, Alex received several Best Speaker awards. He has written many
articles in Oracle-related magazines, and at regular intervals he writes about
Oracle Application Express and Oracle database development on his blog “Notes
on Oracle” (nuijten.blogspot.com). Alex is coauthor of the book Oracle APEX
Best Practices (published by Packt Publishers).
Twitter: @alexnuijten
Blog: nuijten.blogspot.nl
Email: [e-mail]
About the Technical Editor
Chet Justice, Oracle ACE Director, has been an Oracle professional since 2002.
He began by being given SQL*Plus and a tnsnames file, and he quickly
connected Microsoft Access to it so he could see the tables. He began working
extensively with PL/SQL and testing that PL/SQL with SQLUnit. He learned
some database administration skills along the way while setting up testing
environments. Then he found and fell in love with APEX. Since he lacks any
serious focus, he took a job in a data warehousing environment writing ETL in
PL/SQL—then back to OLTP systems, more PL/SQL, and modeling data, before
finally finding fulfillment in the chaos of consultancy. He is currently working in
the OBIEE realm.

Copyright © 2017 by McGraw-Hill Education (Publisher). All rights reserved.
Printed in the United States of America. Except as permitted under the United
States Copyright Act of 1976, no part of this publication may be reproduced or
distributed in any form or by any means, or stored in a database or retrieval
system, without the prior written permission of the publisher, with the exception
that the program listings may be entered, stored, and executed in a computer
system, but they may not be reproduced for publication.
ISBN: 978-1-25964098-8
MHID: 1-25-9640981
The material in this eBook also appears in the print version of this title: ISBN:
978-1-25-964097-1, MHID: 1-25-964097-3.
eBook conversion by codeMantra
Version 1.0
All trademarks are trademarks of their respective owners. Rather than put a
trademark symbol after every occurrence of a trademarked name, we use names
in an editorial fashion only, and to the benefit of the trademark owner, with no
intention of infringement of the trademark. Where such designations appear in
this book, they have been printed with initial caps.
McGraw-Hill Education eBooks are available at special quantity discounts to use
as premiums and sales promotions or for use in corporate training programs. To
contact a representative, please visit the Contact Us page at
www.mhprofessional.com.
Information has been obtained by Publisher from sources believed to be reliable.
However, because of the possibility of human or mechanical error by our
sources, Publisher, or others, Publisher does not guarantee to the accuracy,
adequacy, or completeness of any information included in this work and is not
responsible for any errors or omissions or the results obtained from the use of
such information.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. All
other trademarks are the property of their respective owners, and McGraw-Hill
Education makes no claim of ownership by the mention of products that contain
these marks.
Screen displays of copyrighted Oracle software programs have been reproduced
herein with the permission of Oracle Corporation and/or its affiliates.
Oracle Corporation does not make any representations or warranties as to the
Oracle Corporation does not make any representations or warranties as to the
accuracy, adequacy, or completeness of any information contained in this Work,
and is not responsible for any errors or omissions.
TERMS OF USE
This is a copyrighted work and McGraw-Hill Education and its licensors reserve
all rights in and to the work. Use of this work is subject to these terms. Except as
permitted under the Copyright Act of 1976 and the right to store and retrieve one
copy of the work, you may not decompile, disassemble, reverse engineer,
reproduce, modify, create derivative works based upon, transmit, distribute,
disseminate, sell, publish or sublicense the work or any part of it without
McGraw-Hill Education’s prior consent. You may use the work for your own
noncommercial and personal use; any other use of the work is strictly prohibited.
Your right to use the work may be terminated if you fail to comply with these
terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL EDUCATION AND
ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO
THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS
TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY
INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA
HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY
WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR
A PARTICULAR PURPOSE. McGraw-Hill Education and its licensors do not
warrant or guarantee that the functions contained in the work will meet your
requirements or that its operation will be uninterrupted or error free. Neither
McGraw-Hill Education nor its licensors shall be liable to you or anyone else for
any inaccuracy, error or omission, regardless of cause, in the work or for any
damages resulting therefrom. McGraw-Hill Education has no responsibility for
the content of any information accessed through the work. Under no
circumstances shall McGraw-Hill Education and/or its licensors be liable for any
indirect, incidental, special, punitive, consequential or similar damages that
result from the use of or inability to use the work, even if any of them has been
advised of the possibility of such damages. This limitation of liability shall apply
to any claim or cause whatsoever whether such claim or cause arises in contract,
tort or otherwise.
Contents at a Glance
PART I
The Importance of SQL, PL/SQL, and a Good Data Model
1 SQL and PL/SQL
2 Expert Data Modeling and Implementing Business Logic
PART II
Underutilized Advanced SQL Functionality
3 Handling Advanced and Complex Data Sets
4 Regular Expressions
5 Edition-Based Redefinition
PART III
Essential Everyday Advanced PL/SQL
6 Running PL/SQL from SQL
7 Instrumenting and Profiling PL/SQL
8 Dynamic SQL
9 PL/SQL for Automation and Administration
PART IV
Advanced Analytics
10 In-Database Data Mining Using Oracle Data Mining
11 Oracle R Enterprise
12 Predictive Queries in Oracle 12c
PART V
Database Security
13 Redaction and Masking
14 Encryption and Hashing
15 SQL Injection and Code Security
16 Fine Grained Access Control and Application Contexts
Index
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