Advanced Oracle PL

Advanced Oracle PL/SQL

Developer's Guide

Second Edition

Master the advanced concepts of PL/SQL for

professional-level certification and learn the new

capabilities of Oracle Database 12c

Saurabh K. Gupta

PUBLISHING

professional expertise distilled

BIRMINGHAM - MUMBAI

Advanced Oracle PL/SQL Developer's Guide

Second Edition

Copyright © 2016 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval

system, or transmitted in any form or by any means, without the prior written

permission of the publisher, except in the case of brief quotations embedded in

critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy

of the information presented. However, the information contained in this book is

sold without warranty, either express or implied. Neither the author, nor Packt

Publishing and its dealers and distributors will be held liable for any damages

caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the

companies and products mentioned in this book by the appropriate use of capitals.

However, Packt Publishing cannot guarantee the accuracy of this information.

First published: May 2012

Second edition: February 2016

Production reference: 1080216

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78528-480-9

www.packtpub.com

Credits

Author

Saurabh K. Gupta

Reviewers

Kamran Aghayev A

Patrick Barel

Nassyam Basha

Ramakrishna Kandula

Wissem EL Khlifi

Sean Stacey

Davor Zelic

Commissioning Editor

Priya Singh

Acquisition Editor

Tushar Gupta

Content Development Editor

Arwa Manasawala

Technical Editor

Rohan Uttam Gosavi

Copy Editor

Stephen Copestake

Project Coordinator

Shweta H Birwatkar

Proofreader

Safis Editing

Indexer

Monica Ajmera Mehta

Graphics

Abhinash Sahu

Production Coordinator

Nilesh R. Mohite

Cover Work

Nilesh R. Mohite

About the Author

Saurabh K. Gupta is a seasoned database technologist with extensive experience

in designing high performance and highly available database applications. His

technology focus has been centered around Oracle Database architecture, Oracle

Cloud platform, Database In-Memory, Database Consolidation, Multitenant,

Exadata, Big Data, and Hadoop. He has authored the first edition of this book. He

is an active speaker at technical conferences from Oracle Technology Network,

IOUG Collaborate'15, AIOUG Sangam, and Tech Days. Connect with him on his

twitter handle (or SAURABHKG) or through his technical blog www.sbhoracle.

wordpress.com, with comments, suggestions, and feedback regarding this book.

About the Reviewers

Patrick Barel is a PL/SQL developer for AMIS Services (http://www.amis.nl/) in

the Netherlands. Besides working with SQL and PL/SQL, he co-developed

CodeGen together with Steven Feuerstein, and has written different plugins

(http://bar-solutions.com/) for PL/SQL developer at (http://www.

allroundautomations.com/). He publishes articles on AMIS Technology Blog

(http://technology.amis.nl/blog) and on his own blog (http://blog.bar￾solutions.com).

He has been a reviewer for several books including Oracle PL/SQL Programming by

Steven Feuerstein. He has been an Oracle ACE since 2011.

Nassyam Basha is a database administrator and an Oracle ACE Director. He

holds a master's degree in Computer Applications from the University of Madras.

He is an Oracle 11g Certified Master and Exadata implementation specialist, and

has good knowledge of Oracle technologies, such as Data Guard, RMAN, RAC, and

Exadata. He actively participates in Oracle-related forums, such as OTN, where he

has superhero status. He maintains an Oracle-technology-related blog (www.oracle￾ckpt.com) and has coauthored Oracle Data Guard 11gR2 administration beginners guide,

Packt Publishing. He actively writes many articles on OTN in various languages. He is

a speaker at OTN, IOUG, and SANGAM, and he is the co-founder of Oraworld-team

(www.oraworld-team.com). He is part of the AIOUG community on Twitter, where

he occasionally expresses his views via the Twitter handle @AIOUG. He is currently

working with Pythian as an Oracle database consultant.

Nassyam Basha has written Oracle Data Guard 11gR2 Beginner's Guide, Packt

Publishing.

I want to thank the almighty Allah and my parents, Abdul Aleem and

Rahimunnisa, for their support and blessings all the time—without

them, nothing is possible. Special thanks to my wife and 9-month-old

daughter Yashfeen Fathima, who've shared a lot of fun and crazy

things with me while I worked on this book, and, as always, I would

also like to thank my brother, Nawaz, and my cousins, for their

great support. Finally, thanks to Saurabh Gupta for referring me as a

technical reviewer, which was not an easy task for me, as this is my

first assignment as a reviewer. He did a great job on this book.

Wissem El Khlifi is the first Oracle ACE in Spain and an Oracle Certified

Professional DBA with over 12 years of IT experience.

He earned his Computer Science engineering degree from FST Tunisia, his master's

degree in Computer Science from the UPC, Barcelona, and another master's degree

in Big Data Science from the UPC, Barcelona.

His areas of interest are Linux System Administration, Oracle ERP and Databases

(RAC and Dataguard), big data NoSQL database management, and big data analysis.

His career has included the roles of Oracle and Java analyst/programmer, Oracle

DBA, architect, team leader, and big data scientist. He currently works as senior

database and application engineer for Schneider Electric/APC.

He writes numerous articles on his website, http://www.oracle-class.com, and

you can contact him via Twitter at @orawiss.

Davor Zelic is an IT professional with more than 15 years of experience in

designing, developing, and implementing IT systems.

After getting his master's degree in Electrical Engineering, he began his professional

career working with Oracle technology in the Croatian IT company TEB Informatika.

For more than 10 years, Davor worked on IT projects related to road management,

where he gained extensive experience working as an Oracle SQL, PLSQL, Forms, and

Reports and Spatial developer. He has proved his knowledge by becoming an Oracle

Certified Professional issued by the Oracle Corporation.

Apart from Oracle technology, Davor has gained expertise in design and

development of geographic information systems for collection, storage,

transformation, analysis, and visualization of geo-referenced data. He originally

worked with Intergraph technology, but later his focus moved to open source GIS

technologies, such as Geoserver and OpenLayers.

Davor currently works as a software architect at the IT department of Croatian

Central Bank, designing software solutions for Croatian financial market data

collection and analysis.

I want to thank my parents for the support that they gave me

in choosing my educational path, which allowed me to find a job

that is not just a routine, but also a source of satisfaction and

constant challenge.

www.PacktPub.com

Support files, eBooks, discount offers, and more

For support files and downloads related to your book, please visit www.PacktPub.com.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub

files available? You can upgrade to the eBook version at www.PacktPub.com, and as a print

book customer, you are entitled to a discount on the eBook copy. Get in touch with us at

[email protected] for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a

range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.

TM

https://www2.packtpub.com/books/subscription/packtlib

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book

library. Here, you can search, access, and read Packt's entire library of books.

Why subscribe? • Fully searchable across every book published by Packt

• Copy and paste, print, and bookmark content

• On demand and accessible via a web browser

Free access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib

today and view 9 entirely free books. Simply use your login credentials for immediate access.

Instant updates on new Packt books

Get notified! Find out when new books are published by following @PacktEnterprise on

Twitter or the Packt Enterprise Facebook page.

[ i ]

Table of Contents

Preface xi

Chapter 1: Overview of PL/SQL Programming Concepts 1

Introduction to PL/SQL 2

PL/SQL program fundamentals 3

Cursors – an overview 5

The cursor execution cycle 5

Cursor attributes 6

Cursor FOR loop 8

Exception handling in PL/SQL 9

System-defined exceptions 9

User-defined exceptions 11

The RAISE_APPLICATION_ERROR procedure 13

Exception propagation 16

Creating stored procedures 17

Executing a procedure 19

Functions 20

Functions – execution methods 21

Restrictions on calling functions from SQL expressions 22

A PL/SQL package 23

Oracle Database 12c enhancements to PL/SQL subprograms 25

Managing database dependencies 26

Displaying the direct and indirect dependencies 26

Dependency metadata 27

Dependency issues and enhancements 27

Reviewing Oracle-supplied packages 28

Oracle SQL Developer 30

Oracle SQL Developer for DBA, Developers, and Application Architects 30

SQL Developer 4.0 31

Table of Contents

[ ii ]

Summary 32

Practice exercise 32

Chapter 2: Oracle 12c SQL and PL/SQL New Features 35

Database consolidation and the new Multitenant architecture 36

The Oracle Database 12c Multitenant architecture – features 38

Multitenant for Consolidation 39

Plug/unplug 39

Manage Many as One 40

Rapid provisioning 40

CDB Resource Management 41

Common users and local users 42

Oracle 12c SQL and PL/SQL new features 43

IDENTITY columns 43

Default column value to a sequence in Oracle 12c 45

The DEFAULT ON NULL clause 45

Support for 32K VARCHAR2 46

Row limiting using FETCH FIRST 47

Invisible columns 49

Temporal databases 51

In-Database Archiving 53

Defining a PL/SQL subprogram in the SELECT query and PRAGMA UDF 55

Test setup 57

Comparative analysis 60

The PL/SQL program unit white listing 61

Granting roles to PL/SQL program units 62

Test setup 63

Miscellaneous PL/SQL enhancements 67

The Oracle Database 12c (12.1.0.2) In-Memory option 67

The challenge 68

The problem statement and Oracle Database 12c In-Memory 68

Oracle Database 12c In-Memory option features 68

The Oracle Database 12c In-Memory Architecture 69

Controlling the In-Memory column store 70

The INMEMORY clause 70

Performance optimizations 72

In-Memory Advisor 72

Oracle Database In-Memory benefits 73

Summary 73

Chapter 3: Designing PL/SQL Code 75

Cursor structures 76

Cursor execution cycle 76

Cursor attributes 78

Table of Contents

[ iii

]

Implicit cursors 78

Explicit cursors 80

Cursor variables 83

Strong and weak ref cursor types 85

Working with cursor variables 86

SYS_REFCURSOR 87

Cursor variables as arguments 88

Cursor variables – restrictions 89

Cursor design considerations 89

Cursor design–guidelines 90

Implicit statement results in Oracle Database 12c 91

Subtypes 93

Subtype classification 93

Type compatibility with subtypes 95

Summary 95

Practice exercise 96

Chapter 4: Using Collections 99

Introduction to collections 100

Collection types 100

Associative arrays 101

Nested tables 104

Modify and drop a nested table object type 105

Design considerations of a nested table 105

Nested table storage 105

Nested table in an index - organized table 105

Nested table locators 106

Nested table as the schema object 106

Operations on a nested table type column 107

Nested table collection type in PL/SQL

110

Querying the nested table metadata

112

Nested table comparison functions

113

Multiset operations on nested tables

114

Varray 116

Varray as a schema object

117

Operations on varray type columns

119

Varray in PL/SQL 122

Comparing the collection types 122

Selecting the appropriate collection type 124

Oracle 12c enhancements to collections 124

PL/SQL collection methods 127

EXISTS 127

COUNT 128

Table of Contents

[ iv ]

LIMIT 129

FIRST and LAST 129

PRIOR and NEXT 130

EXTEND 131

TRIM 132

DELETE 133

Summary 135

Practice exercise 136

Chapter 5: Using Advanced Interface Methods 139

Overview of External Procedures 139

External Procedures 140

Components of external procedure execution flow 140

The extproc agent 141

The Library object 141

Callout and Callback 142

Call Specification 142

How an External Procedure executes 143

Environment setup 143

TNSNAMES.ora 144

EXTPROC.ora 144

Executing external C programs from PL/SQL 145

Securing External Procedures with Oracle Database 12c 149

Executing Java programs from PL/SQL 150

Loading a Java class into a database 150

Steps to execute a Java class from an Oracle PL/SQL unit 151

Summary 155

Practice exercise 155

Chapter 6: Virtual Private Database 159

Oracle Database Security overview 160

Fine-Grained Access Control 163

How FGAC works 163

Virtual Private Database 164

How does Virtual Private Database work? 164

Column-level Virtual Private Database 164

Virtual Private Database with Oracle Database 12c Multitenant 165

Virtual Private Database components 165

Application Context 165

Virtual Private Database policy function 168

Policy types 169

The DBMS_RLS package 169

Demonstration 170

Virtual Private Database features and best practices 177

Table of Contents

[ v

]

Virtual Private Database metadata 178

Policy utilities—refresh and drop 179

Oracle Database 12c Security enhancements 180

Oracle Database 12c Data Redaction 181

Data Redaction exemptions and miscellaneous features 182

Data Redaction function types 182

Demonstration 183

The Data Redaction metadata 186

Summary 187

Practice exercise 187

Chapter 7: Oracle SecureFiles 191

Introduction to Large Objects 192

Classification of Large Object datatypes 193

Internal LOB 193

External LOB 194

LOB restrictions 194

LOB data types in Oracle 194

BLOB and CLOB 194

BFILE 195

Some more related stuff 195

The LOB locator 195

LOB instance initialization 196

The DBMS_LOB package 197

LOB usage notes 201

Oracle SecureFiles 201

Deduplication and compression 201

Encryption 202

File System Logging 203

Write Gather Cache 203

Free space management 203

BasicFiles and SecureFiles 204

The db_securefile parameter 204

Working with LOBs 205

LOB metadata 207

Enabling the advanced features of a SecureFile 209

Populating the LOB data 212

Temporary LOB operations 215

Managing temporary LOBs 215

Working with a temporary LOB 216

Migrating LONG to LOBs 218

Use the ALTER TABLE command 219

Using the TO_LOB function 219

Table of Contents

[ vi ]

Online Table Redefinition 220

Migrating BasicFiles to SecureFiles 220

Oracle Database 12c enhancements to SecureFiles 223

Summary 223

Practice exercise 224

Chapter 8: Tuning the PL/SQL Code 229

The PL/SQL Compiler 230

Subprogram inlining in PL/SQL 230

PRAGMA INLINE 231

PLSQL_OPTIMIZE_LEVEL 232

Native and interpreted compilation techniques 238

Oracle Database 11g Real Native Compilation 239

Selecting the appropriate compilation mode 240

Setting the compilation mode 240

Querying the compilation settings 241

Compiling a program unit for native or interpreted compilation 241

Recompiling a database for a PL/SQL native or interpreted compilation 243

Tuning PL/SQL code 247

Build secure applications using bind variables 247

Call parameters by reference 251

Avoiding an implicit data type conversion 252

Understanding the NOT NULL constraint 253

Selection of an appropriate numeric data type 254

Bulk processing in PL/SQL 257

BULK COLLECT 257

FORALL 262

Summary 270

Practice exercise 271

Chapter 9: Result Cache 275

Oracle Database 11g Result Cache 276

What is the Server Result Cache? 277

Configuring the Server Result Cache 278

Result Cache versus Buffer Cache 280

Result Cache versus Oracle 12c Database In-Memory 280

Result Cache versus In-Memory Database Cache 280

SQL query Result Cache 281

Monitoring the SQL Result Cache 284

Invalidation of the SQL Result Cache 288

Read consistency of the SQL Result Cache 289

Limitations 289