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