Modern Oracle Database programming : level up your skill set to Oracle's latest and most powerful features in SQL, PL/SQL, and JSON

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 skills and 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 knowledge and 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 Table of Contents 4 About the Authors 14 About the Technical Reviewer 15 Foreword 16 Acknowledgments 17 Introduction 18 Part I: Advanced Basics 26 Chapter 1: Underutilized Functionality and Enhancements 27 Merge 27 Collections 28 Bulk Collect 30 Limit 32 forall 34 Range 35 indices of 36 values of 39 Bulk Exceptions 43 Compound Triggers 43 Journaling 45 Mutating Table 49 DML Error Logging 52 Log Errors 52 Save Exceptions 56 Summary 58 Chapter 2: Analytic Functions and (un)Pivoting 59 Analytic Functions 59 Building Blocks 60 Running Totals 61 Accessing Values from Other Rows 66 The Fastest Lap and the Slowest Lap 69 Ranking: Top-N 71 Deduplication 72 Reusable Windowing Clause 75 Pivot and Unpivot 77 Pivot 77 Unpivot 81 Summary 85 Chapter 3: Joins 86 Why Choose ANSI Joins? 86 Natural Joins 87 Inner Joins 89 Outer Joins 91 Almost Outer Joins 92 Full Outer Joins 94 Cross Joins 95 Partitioned Outer Joins 96 Lateral Joins 98 Summary 103 Chapter 4: Finding Patterns 104 Looking for Contracts 104 Classifying the Records 106 Logical Groups 111 Faster at the End 113 Winning? 117 Undefined Classification 120 Longest Winning Streak 122 Pit Stops and Hazards 124 Summary 129 Chapter 5: Pagination and Set Operators 130 Top-N and Pagination 130 Row-Limiting Clause 133 Pagination 137 Under the Hood 140 Set Operators 141 Union 143 Minus and Except 145 Intersect 148 Summary 150 Chapter 6: Conditional Compilation 151 Directives 151 Selection Directive 151 Inquiry Directive 152 The DBMS_DB_VERSION Package 152 Predefined CCFLAGS 157 Error Directive 157 DEPRECATE Pragma Directive 158 Using Directives 160 Use Cases 161 Checking for the Presence of New Features 161 Showing Output of Debug and Test Code 163 Checking for the Correct Database Environment 170 Showing What’s Compiled 173 Summary 174 Chapter 7: Iterations and Qualified Expressions 175 Iterations 175 Iteration Controls 175 Multiple Iteration Controls 176 Stepped Iteration Controls 177 The while Loop in Iteration Controls 179 when in Iteration Controls 181 Cursor in Iteration Controls 183 Mutable Iterator 189 Augmenting the Iterator 191 The for loop in an Array 192 The Indices of an Array 193 The Values of an Array 195 The pairs of Array 197 PL/SQL Qualified Expressions 200 Records 200 Collections 204 Summary 208 Part II: Multiple Techniques and Languages 209 Chapter 8: Polymorphic Table Functions and SQL Macros 210 Polymorphic Table Functions 211 Function 211 Package 212 describe 212 open 213 fetch_rows 213 close 213 Usage 213 Use Cases 214 Split 214 Calling the Polymorphic Function 221 Sum Intervals 222 Aggregating Intervals 230 SQL Macros 231 Table-Type SQL Macros 232 Scalar-Type SQL Macro 233 Use Cases 233 Parametrized View 233 Column Splitter (Table) 235 Splitting Columns with a SQL Macro 237 Parametrized View 238 Mimic Other Databases (Scalar) 241 Date Functions 241 String Functions 245 expand_sql_text 246 Summary 248 Chapter 9: Subquery Factoring, the WITH Clause, Explained 249 Monaco Podium 249 Functions in the WITH clause 254 Recursive Subquery Factoring 259 Simulate Built-in Recursive Functions 264 Level Pseudocolumn 265 sys_connect_by_path, connect_by_root, connect_by_isleaf 266 Sorting the Results 269 Cycle Detection 272 Summary 275 Chapter 10: Calling PL/SQL from SQL 276 UDF Pragma 276 Deterministic 277 Result Cache 280 Hierarchical Profiler 282 DBMS_HPROF 285 create_tables 285 start_profiling 286 stop_profiling 287 analyze 288 Report 293 Summary 299 Chapter 11: Storing JSON in the Database 301 Stage Data Before Processing 301 Which Data Type to Choose? 302 JSON Constraint 303 Under the Hood 308 Major Differences Between TREAT and JSON Constructor 312 Determining the Structure 315 Indexing JSON Documents 320 Searching for a Specific Key 321 Ad Hoc Searching with the JSON Search Index 324 Multivalue Indexes 326 Summary 327 Chapter 12: Path Expressions in JSON 328 Sample Data 328 JSON Data Types 330 Path Expressions and Filters 330 Using Filter Conditions 333 Passing Variables 337 Empty String and NULL 338 Item Methods 340 Handling Errors in JSON Path Expressions 344 Dot Notation 347 Summary 351 Chapter 13: SQL/JSON and Conditions 352 SQL/JSON Functions 352 JSON_VALUE 352 JSON_QUERY 358 JSON_TABLE 362 JSON_SERIALIZE 371 SQL Conditions 373 Summary 378 Chapter 14: Generate, Compare, and Manipulate JSON 379 Generating JSON Documents 379 Comparing JSON Documents 387 Changing a JSON Document 389 JSON and PL/SQL 394 Constructing and Manipulating JSON with PL/SQL 403 Summary 407 Part III: Oracle-Provided Functionality 408 Chapter 15: Useful APEX Packages 409 APEX Availability 409 Calling Web Services 410 Getting Data from JSON 415 Basic Spatial Functionality 418 Utilities for Text Manipulations 420 Formatting a Message 422 Please Initial Here 423 Parsing Data 424 Business Case 424 Let’s Parse! 425 Working with ZIP Files 429 Unzipping Files 430 Zipping Files 431 Summary 432 Chapter 16: Processing Data in the Background 433 Resource Intensive 433 Queuing 433 The Use Case 434 Setup 435 Queue Table and Queue 436 Enqueue and Dequeue 438 Wrapper Procedures 440 PL/SQL Callback Notification 442 Seeing It in Action 447 Exception Queue 449 Advanced Queuing and JSON 452 Summary 454 Chapter 17: Introspecting PL/SQL 455 DBMS_SESSION 455 current_is_role_enabled 455 set_role 456 set_nls 456 set_context 457 clear_context 460 clear_all_context 461 set_identifier 461 clear_identifier 462 sleep 462 DBMS_APPLICATION_INFO 463 set_module 463 set_action 463 read_module 464 DBMS_UTILITY 465 compile_schema 465 format_call_stack 466 format_error_stack 468 comma_to_table 469 expand_sql_text 473 UTL_CALL_STACK 477 subprogram 480 concatenate_subprogram 481 owner 483 unit_line 483 Summary 484 Chapter 18: See What You Need to See 485 Temporal Validity 485 Setup 485 as of Queries 489 Versions Between Queries 491 DBMS_FLASHBACK_ARCHIVE 493 Virtual Private Database 495 The Policy Function 496 Context 497 Policy 498 add_policy 498 enable_policy 506 drop_policy 506 alter_policy 506 Complex Policies 507 Redaction 509 add_policy 510 alter_policy 515 enable_policy 517 disable_policy 517 drop_policy 518 Summary 518 Chapter 19: Upgrade Your Application with Zero Downtime 519 Downtime 519 Definitions 520 Concept 521 The Challenges of Zero-Downtime Upgrades 522 Solution 523 Dependencies 524 Preparation 525 Privileges 526 Several Levels of Complexity 527 Only Changing PL/SQL Objects 528 Table Changes: Don’t Sync Between Editions 533 Table Changes with Data Sync 537 Retiring Older Editions 543 Change Default Edition 544 Summary 544 Chapter 20: Choosing the Right Table Type 545 Heap Table 545 Index 546 B-tree 548 Bitmap Index 548 Index-Organized Table (IOT) 548 Clusters 550 Temporary Table 555 Global Temporary Table 555 Private Temporary Table 561 Restrictions on Temporary Tables 566 External Table 567 Immutable Table 573 Blockchain Table 576 Summary 577 ORA-00000: Normal, Successful Completion 578 Index 579