Modern Oracle Database Programming: Level Up Your Skill Set to Oracle's Latest and Most Powerful Features in SQL, PL/SQL, and JSON

Part I: Advanced Basics....2

Chapter 1: Underutilized Functionality and Enhancements....3

Merge....3

Collections....4

Bulk Collect....6

Limit....8

forall....10

Range....11

indices of....12

values of....15

Bulk Exceptions....19

Compound Triggers....19

Journaling....21

Mutating Table....25

DML Error Logging....28

Log Errors....28

Save Exceptions....32

Summary....34

Chapter 2: Analytic Functions and (un)Pivoting....35

Analytic Functions....35

Building Blocks....36

Running Totals....37

Accessing Values from Other Rows....42

The Fastest Lap and the Slowest Lap....45

Ranking: Top-N....47

Deduplication....48

Reusable Windowing Clause....51

Pivot and Unpivot....53

Pivot....53

Unpivot....57

Summary....61

Chapter 3: Joins....62

Why Choose ANSI Joins?....62

Natural Joins....63

Inner Joins....65

Outer Joins....67

Almost Outer Joins....68

Full Outer Joins....70

Cross Joins....71

Partitioned Outer Joins....72

Lateral Joins....74

Summary....79

Chapter 4: Finding Patterns....80

Looking for Contracts....80

Classifying the Records....82

Logical Groups....87

Faster at the End....89

Winning?....93

Undefined Classification....96

Longest Winning Streak....98

Pit Stops and Hazards....100

Summary....105

Chapter 5: Pagination and Set Operators....106

Top-N and Pagination....106

Row-Limiting Clause....109

Pagination....113

Under the Hood....116

Set Operators....117

Union....119

Minus and Except....121

Intersect....124

Summary....126

Chapter 6: Conditional Compilation....127

Directives....127

Selection Directive....127

Inquiry Directive....128

The DBMS_DB_VERSION Package....128

Predefined CCFLAGS....133

Error Directive....133

DEPRECATE Pragma Directive....134

Using Directives....136

Use Cases....137

Checking for the Presence of New Features....137

Showing Output of Debug and Test Code....139

Checking for the Correct Database Environment....146

Showing What’s Compiled....149

Summary....150

Chapter 7: Iterations and Qualified Expressions....151

Iterations....151

Iteration Controls....151

Multiple Iteration Controls....152

Stepped Iteration Controls....153

The while Loop in Iteration Controls....155

when in Iteration Controls....157

Cursor in Iteration Controls....159

Mutable Iterator....165

Augmenting the Iterator....167

The for loop in an Array....168

The Indices of an Array....169

The Values of an Array....171

The pairs of Array....173

PL/SQL Qualified Expressions....176

Records....176

Collections....180

Summary....184

Chapter 8: Polymorphic Table Functions and SQL Macros....185

Polymorphic Table Functions....186

Function....186

Package....187

describe....187

open....188

fetch_rows....188

close....188

Usage....188

Use Cases....189

Split....189

Calling the Polymorphic Function....196

Sum Intervals....197

Aggregating Intervals....205

SQL Macros....206

Table-Type SQL Macros....207

Scalar-Type SQL Macro....208

Use Cases....208

Parametrized View....208

Column Splitter (Table)....210

Splitting Columns with a SQL Macro....212

Parametrized View....213

Mimic Other Databases (Scalar)....216

Date Functions....216

String Functions....220

expand_sql_text....221

Summary....223

Chapter 9: Subquery Factoring, the WITH Clause, Explained....224

Monaco Podium....224

Functions in the WITH clause....229

Recursive Subquery Factoring....234

Simulate Built-in Recursive Functions....239

Level Pseudocolumn....240

sys_connect_by_path, connect_by_root, connect_by_isleaf....241

Sorting the Results....244

Cycle Detection....247

Summary....250

Chapter 10: Calling PL/SQL from SQL....251

UDF Pragma....251

Deterministic....252

Result Cache....255

Hierarchical Profiler....257

DBMS_HPROF....260

create_tables....260

start_profiling....261

stop_profiling....262

analyze....263

Report....268

Summary....274

Chapter 11: Storing JSON in the Database....276

Stage Data Before Processing....276

Which Data Type to Choose?....277

JSON Constraint....278

Under the Hood....283

Major Differences Between TREAT and JSON Constructor....287

Determining the Structure....290

Indexing JSON Documents....295

Searching for a Specific Key....296

Ad Hoc Searching with the JSON Search Index....299

Multivalue Indexes....301

Summary....302

Chapter 12: Path Expressions in JSON....303

Sample Data....303

JSON Data Types....305

Path Expressions and Filters....305

Using Filter Conditions....308

Passing Variables....312

Empty String and NULL....313

Item Methods....315

Handling Errors in JSON Path Expressions....319

Dot Notation....322

Summary....326

Chapter 13: SQL/JSON and Conditions....327

SQL/JSON Functions....327

JSON_VALUE....327

JSON_QUERY....333

JSON_TABLE....337

JSON_SERIALIZE....346

SQL Conditions....348

Summary....353

Chapter 14: Generate, Compare, and Manipulate JSON....354

Generating JSON Documents....354

Comparing JSON Documents....362

Changing a JSON Document....364

JSON and PL/SQL....369

Constructing and Manipulating JSON with PL/SQL....378

Summary....382

Chapter 15: Useful APEX Packages....383

APEX Availability....383

Calling Web Services....384

Getting Data from JSON....389

Basic Spatial Functionality....392

Utilities for Text Manipulations....394

Formatting a Message....396

Please Initial Here....397

Parsing Data....398

Business Case....398

Let’s Parse!....399

Working with ZIP Files....403

Unzipping Files....404

Zipping Files....405

Summary....406

Chapter 16: Processing Data in the Background....407

Resource Intensive....407

Queuing....407

The Use Case....408

Setup....409

Queue Table and Queue....410

Enqueue and Dequeue....412

Wrapper Procedures....414

PL/SQL Callback Notification....416

Seeing It in Action....421

Exception Queue....423

Advanced Queuing and JSON....426

Summary....428

Chapter 17: Introspecting PL/SQL....429

DBMS_SESSION....429

current_is_role_enabled....429

set_role....430

set_nls....430

set_context....431

clear_context....434

clear_all_context....435

set_identifier....435

clear_identifier....436

sleep....436

DBMS_APPLICATION_INFO....437

set_module....437

set_action....437

read_module....438

DBMS_UTILITY....439

compile_schema....439

format_call_stack....440

format_error_stack....442

comma_to_table....443

expand_sql_text....447

UTL_CALL_STACK....451

subprogram....454

concatenate_subprogram....455

owner....457

unit_line....457

Summary....458

Chapter 18: See What You Need to See....459

Temporal Validity....459

Setup....459

as of Queries....463

Versions Between Queries....465

DBMS_FLASHBACK_ARCHIVE....467

Virtual Private Database....469

The Policy Function....470

Context....471

Policy....472

add_policy....472

enable_policy....480

drop_policy....480

alter_policy....480

Complex Policies....481

Redaction....483

add_policy....484

alter_policy....489

enable_policy....491

disable_policy....491

drop_policy....492

Summary....492

Chapter 19: Upgrade Your Application with Zero Downtime....493

Downtime....493

Definitions....494

Concept....495

The Challenges of Zero-Downtime Upgrades....496

Solution....497

Dependencies....498

Preparation....499

Privileges....500

Several Levels of Complexity....501

Only Changing PL/SQL Objects....502

Table Changes: Don’t Sync Between Editions....507

Table Changes with Data Sync....511

Retiring Older Editions....517

Change Default Edition....518

Summary....518

Chapter 20: Choosing the Right Table Type....519

Heap Table....519

Index....520

B-tree....522

Bitmap Index....522

Index-Organized Table (IOT)....522

Clusters....524

Temporary Table....529

Global Temporary Table....529

Private Temporary Table....535

Restrictions on Temporary Tables....540

External Table....541

Immutable Table....547

Blockchain Table....550

Summary....551

0.PNG....1

Level up your skill set to the latest that Oracle Database can offer. This book introduces features that are not well known that can transform your development efforts. You’ll discover built-in functionality that can save you massive amounts of time that otherwise would be spent reinventing the wheel. You’ll find that what used to take a lot of programming some years ago can be done with less code in a more reliable way today. Anyone using Oracle Database without the knowledge in this book is leaving valuable functionality–that their company has paid for–on the table, and this book opens the door to that functionality so that you can deliver reliable and performant solutions faster and more easily than ever.Part I looks at features in SQL and PL/SQL that are underused and not well known. You’ll learn about new join types, pattern matching across rows, Top N pagination (useful in reporting!), qualified expressions, and enhancements to iterators that reduce code complexity and make your logic easier to understand.Part II covers how and when to invoke PL/SQL from SQL while maintaining performance. You'll learn about SQL macro functions for creating reusable SQL fragments, polymorphic table functions with return types determined by incoming argument types, and constructing and parsing JSON documents for data interchange with other systems.Part III introduces a vast array of built-in functionality that Oracle provides that is just waiting to be used. Edition-based redefinition enables zero-downtime application and schema upgrades. Data redaction enables easier compliance with privacy laws and similar regulations by protecting sensitive data from those who have no need to see it. Virtual private databases provide the appearance of giving each user their own database, again helping to secure sensitive data. These features are just a taste of what the book provides. Soon you’ll be improving your skillsand wondering why you ever worked so hard to solve problems that Oracle Database already solves for you.

What You Will Learn

• Write more powerful code by incorporating underused features in SQL and PL/SQL
• Optimize your integration between SQL and PL/SQL for best performance
• Take advantage of enhanced set operators, lateral joins, row-based pattern matching, and other advanced features in SQL
• Make your code easier to understand through your use of newer PL/SQL features, such as qualified expressions and iterator enhancements
• Integrate with web services and external data sources directly from the database
• Create and parse JSON documents for easy data exchange and flexible schema design

Who This Book Is For

Any developer who is writing SQL or PL/SQL, PL/SQL experts who want to level up their knowledgeand skills to the latest features that Oracle Database provides, and developers who don’t want to write their own solutions only to find out later that they’ve wasted their time by building something that Oracle Database provides out of the box