PL/SQL Is My Super Power · Index

Index

Technical phrases from the book. Expand a phrase to see matching passages and open the relevant section.

A

analytic functions (1)

7.6.3 They Integrate Natively With SQL
7 Database Links Are My Superpower
analytic functions,

autonomous transaction (1)

A.12.4 LOG_MANAGER Package Body
Appendix A: Cursors and Logging Examples
…ugh the helper procedure WRITE_EVENT, which runs in an autonomous transaction so logging never interferes with the caller’s commit or rollback. The global variables remember the cu…

B

BULK COLLECT (6)

7.7.3.3 Bulk Operations
7 Database Links Are My Superpower
BULK COLLECT,
7.12.6 PL/SQL Usage Patterns
7.12 Database Link Performance Tuning Checklist
☐ Bulk collect, don’t loop
A.6 Better style cursor, bulk collect
Appendix A: Cursors and Logging Examples
A.6 Better style cursor, bulk collect
A.7 Better style cursor, bulk collect with Limit and FORALL
Appendix A: Cursors and Logging Examples
A.7 Better style cursor, bulk collect with Limit and FORALL
A.9 How to monitor memory consumption using PGA memory size
Appendix A: Cursors and Logging Examples
When using BULK COLLECT, every fetched row is stored in the session’s Program Global Area (PGA), which is the private memory allocated to your PL/SQL process. Large collections can…
B.7 Tying this back to the earlier PGA / LIMIT discussion
Appendix B: Parallel Processing and Scheduled Jobs
Earlier in your Appendix, you explain how LIMIT in a BULK COLLECT loop lets you control PGA consumption per batch. That idea carries nicely into AUTO concurrency:

bulk processing (2)

6.10 Why This Belongs in PL/SQL
6 Vectors Are My Superpower
bulk processing
7.6.3 They Integrate Natively With SQL
7 Database Links Are My Superpower
bulk processing.

C

canonicalization (3)

6.4.1 Step 1: Canonicalization (Stability First)
6 Vectors Are My Superpower
6.4.1 Step 1: Canonicalization (Stability First)
C.2 Package Spec
Appendix C: Vector Creation Logic
- Canonicalization is intentionally strict + deterministic.
C.3 Package Body (canonicalization + hash → vector)
Appendix C: Vector Creation Logic
C.3 Package Body (canonicalization + hash → vector)

collections (4)

2 Cursors
…e point it was the only cursor variant available since Collections really did not become viable until Oracle 9i in 2001.
3 Least Privilege
Using Collections in Stored Procedures, Functions, Packages
A.9 How to monitor memory consumption using PGA memory size
Appendix A: Cursors and Logging Examples
…private memory allocated to your PL/SQL process. Large collections can consume significant PGA, especially when rows contain many columns or when multiple collections are used at o…
A.10 Collection exceptions across multiple rows
Appendix A: Cursors and Logging Examples
Collections exceptions. Oracle is very good at providing an exception process for handling errors. In many cases this just means that certain types of errors will bubble up an exce…

COMMIT (4)

5.5 Autonomous Error Logging (DEBUG_PKG)
5 Logging Manager and Autonomous Error Logging
Commit independently of the caller
7.12.6 PL/SQL Usage Patterns
7.12 Database Link Performance Tuning Checklist
☐ Commit deliberately
A.12.4 LOG_MANAGER Package Body
Appendix A: Cursors and Logging Examples
…nsaction so logging never interferes with the caller’s commit or rollback. The global variables remember the current application, module, and last checkpoint. Runtime code can reco…
B.4.2 Job Responsibilities
Appendix B: Parallel Processing and Scheduled Jobs
Commit appropriately and update status if required.

constraints (1)

A.11 cursor exceptions, don’t kill me now, report later
Appendix A: Cursors and Logging Examples
…est records, some of which intentionally violate table constraints—for example, a NULL value in a NOT NULL column and a duplicate primary key. The FORALL statement attempts to inse…

context switching (1)

2 Cursors
…ows it can be significant. This is because there is no context switching (PL/SQL calling SQL to do line by line fetches of rows of data) in the collection implementation. This is a…

cursor (10)

2 Cursors
…ocedures, functions, and other Oracle program units. A cursor is essentially a local, in-memory result set: a collection of rows and columns returned by a SQL query that the PL/SQL…
7.7.3.3 Bulk Operations
7 Database Links Are My Superpower
chatty cursor fetches,
7.12.1 SQL & Query Design (Highest Impact)
7.12 Database Link Performance Tuning Checklist
Avoid row-by-row loops, cursor fetches, and procedural chatter across the link.
Appendices: Cursor and Parallel Processing Examples
Appendices: Cursor and Parallel Processing Examples
A.3 The view would be as shown and it is needed to define the cursor next
Appendix A: Cursors and Logging Examples
… view would be as shown and it is needed to define the cursor next
A.4 creating a cursor on the view v_persons (defined above in 2.3)
Appendix A: Cursors and Logging Examples
A.4 creating a cursor on the view v_persons (defined above in 2.3)
A.6 Better style cursor, bulk collect
Appendix A: Cursors and Logging Examples
A.6 Better style cursor, bulk collect
A.7 Better style cursor, bulk collect with Limit and FORALL
Appendix A: Cursors and Logging Examples
A.7 Better style cursor, bulk collect with Limit and FORALL
A.11 cursor exceptions, don’t kill me now, report later
Appendix A: Cursors and Logging Examples
A.11 cursor exceptions, don’t kill me now, report later
B.4.3 Invariants (Rules)
Appendix B: Parallel Processing and Scheduled Jobs
…ared state unless managed carefully (e.g., no “global” cursor without proper partitioning).

D

data warehouse (2)

4 ETL
… In such environments, both the data pipelines and the data warehouse schema were frequently entirely bespoke, requiring deep PL/SQL expertise to ensure accuracy, performance, and …
5.3 Real-World Batch Example
5 Logging Manager and Autonomous Error Logging
…I was the lead developer responsible for a data mart / data warehouse running a large batch of Oracle jobs every evening.

database links (22)

7 Database Links Are My Superpower
7 Database Links Are My Superpower
7.1 Why Database Links Are Controversial (and Why They Shouldn’t Be)
7 Database Links Are My Superpower
7.1 Why Database Links Are Controversial (and Why They Shouldn’t Be)
7.3 Using Analytic Views as the Boundary for Database Links
7 Database Links Are My Superpower
7.3 Using Analytic Views as the Boundary for Database Links
7.3.1 What Is Exposed Across the Link
7 Database Links Are My Superpower
In this pattern, database links are used to access read-only analytic views defined on the source system.
7.3.5 Summary
7 Database Links Are My Superpower
Using analytic views as the boundary for database links establishes a clear, governed interface between systems. This pattern simplifies distributed query behavior, improves perfor…
7.8 Network Tuning: The Hidden Half of Database Link Performance
7 Database Links Are My Superpower
Database links are network-sensitive by design. This sensitivity is not an implementation weakness — it is a direct consequence of performing distributed SQL execution across syste…
7.8.1 Transparency Versus Indirection
7 Database Links Are My Superpower
Database links take the opposite approach. They move data only when a SQL statement demands it, stream only the rows that qualify, and surface network behavior directly through exe…
7.8.2 Why the Network Matters So Much
7 Database Links Are My Superpower
Because database links operate synchronously and transparently, their performance is directly influenced by network characteristics, including:
7.8.3 The Hard Truth: Database Links Reveal Problems Others Hide
7 Database Links Are My Superpower
7.8.3 The Hard Truth: Database Links Reveal Problems Others Hide
7.8.4 Practical Design Implications
7 Database Links Are My Superpower
Successful use of database links requires treating the network as a first-class design concern, not a post-deployment tuning exercise.
7.8.5 Why This Is Difficult to Negotiate
7 Database Links Are My Superpower
Database links sit precisely at this boundary.
7.8.6 The Architectural Payoff
7 Database Links Are My Superpower
When database links perform poorly, the root cause is often outside the database. That is not a weakness of the approach — it is an accurate signal about where architectural attent…
7.12.10 Final Thought
7.12 Database Link Performance Tuning Checklist
Database links are not an architectural shortcut. They are a mature, well-understood integration mechanism that operates at the same level of rigor as the SQL engine itself.
D.1 Why Network Engagement Changes Everything
Appendix D: Working with Network Engineers to Optimize Database Link Performance
Database links operate synchronously and transparently. They do not buffer data, hide latency, or defer failures. As a result, they surface network characteristics immediately and …
D.3.6 Encryption Cost Awareness: Performance, Placement, and Capacity
Appendix D: Working with Network Engineers to Optimize Database Link Performance
…ms. Data in transit must be protected, especially when database links cross security zones or organizational boundaries.
D.4 Where the Encryption Cost Actually Occurs
Appendix D: Working with Network Engineers to Optimize Database Link Performance
Because database links operate synchronously, these costs accumulate directly into query elapsed time.
D.4.1 Why Database Links Surface Encryption Cost So Clearly
Appendix D: Working with Network Engineers to Optimize Database Link Performance
D.4.1 Why Database Links Surface Encryption Cost So Clearly
D.4.6 Key Takeaway
Appendix D: Working with Network Engineers to Optimize Database Link Performance
Database links make encryption cost explicit, measurable, and therefore manageable. Systems that perform well do not ignore this cost — they design for it.
D.8 Why Database Links Make Cost Visible
Appendix D: Working with Network Engineers to Optimize Database Link Performance
D.8 Why Database Links Make Cost Visible
D.11 Key Takeaway: Deliberate Placement Beats Accidental Cost
Appendix D: Working with Network Engineers to Optimize Database Link Performance
Database links make the cost of encryption explicit and measurable, rather than hidden behind application layers or deferred through asynchronous pipelines. This visibility allows …
D.13 The Architectural Reality
Appendix D: Working with Network Engineers to Optimize Database Link Performance
Database links sit at the exact intersection of:
D.14 Closing Thought
Appendix D: Working with Network Engineers to Optimize Database Link Performance
When database links perform poorly, they are often blamed.

DBMS_SCHEDULER (4)

4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
…rtant method is to use autonomous scheduled jobs using dbms_scheduler and a concept of a parallel manager to run operations in parallel. An example of this is running a table ETL w…
4.2.2 Parallel MERGE with a Parallel Manager Package
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
…ice, though, we rarely want every caller to hand-craft DBMS_SCHEDULER calls and guess how many jobs are “safe” to run. That is where a parallel manager package becomes useful.
4.2.2.1 The Parallel Manager’s Role
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
Callers do not interact with DBMS_SCHEDULER directly. Instead, they call something like:
4.2.2.2 Can the Parallel Manager Auto-Sense Load?
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
…llel manager becomes a lightweight scheduler on top of DBMS_SCHEDULER, enforcing rules such as:

deterministic (10)

6.2 Why Identity Matching Is Harder Than It Looks
6 Vectors Are My Superpower
…at allows Oracle PL/SQL systems to perform millions of deterministic identity comparisons per second, safely, repeatably, and auditable — without AI guesswork and without fragile s…
6.3 The Core Idea: Identity as a Vector, Not a Row
6 Vectors Are My Superpower
is deterministic
6.4.2 Step 2: Numeric Encoding
6 Vectors Are My Superpower
Hash (stable, deterministic)
6.6.2 Person ↔ Weapon (Regulated Systems)
6 Vectors Are My Superpower
Result: deterministic matching with zero guessing — critical in legal contexts.
6.10 Why This Belongs in PL/SQL
6 Vectors Are My Superpower
deterministic logic
6.11.5 Impact on System Design
6.11 Performance Benchmarks (Practical, Not Theoretical)
Because vector matching is fast and deterministic, systems can:
6.11.8 Final Thought
6.11 Performance Benchmarks (Practical, Not Theoretical)
…entity matching from a messy text problem into a fast, deterministic, auditable computation — and PL/SQL is one of the best tools on earth for executing that pattern at scale.
7.11 The Scientific Argument for DB Links
7 Database Links Are My Superpower
Deterministic performance
C.2 Package Spec
Appendix C: Vector Creation Logic
Purpose: Canonicalize input and produce a deterministic numeric vector.
C.3 Package Body (canonicalization + hash → vector)
Appendix C: Vector Creation Logic
Produce a deterministic canonical form for matching.

DML (2)

2 Cursors
…witching). When the cursor is finished the FORALL bulk DML can be used to do mass update/insert or deletes significantly faster than traditional methods used in older cursors and e…
A.11 cursor exceptions, don’t kill me now, report later
Appendix A: Cursors and Logging Examples
…iled record. This pattern allows high-performance bulk DML while still providing detailed diagnostics for invalid data.

E

error handling (2)

C.2 Package Spec
Appendix C: Vector Creation Logic
- Error handling follows a POS + ASSERT pattern (customizable).
C.3 Package Body (canonicalization + hash → vector)
Appendix C: Vector Creation Logic
INTERNAL: POS / ASSERT / ERROR HANDLING

error logging (3)

5 Logging Manager and Autonomous Error Logging
5 Logging Manager and Autonomous Error Logging
5.5 Autonomous Error Logging (DEBUG_PKG)
5 Logging Manager and Autonomous Error Logging
5.5 Autonomous Error Logging (DEBUG_PKG)
A.12.3 LOG_MANAGER Package Specification
Appendix A: Cursors and Logging Examples
… the logging system. It separates runtime checkpoints, error logging, and test assertions into a small set of procedures that any PL/SQL code can call. At runtime you typically use…

exceptions (3)

2 Cursors
Collections exceptions. Oracle is very good at providing an exception process for handling errors. In many cases this just means that certain types of errors will bubble up an exce…
A.10 Collection exceptions across multiple rows
Appendix A: Cursors and Logging Examples
A.10 Collection exceptions across multiple rows
A.11 cursor exceptions, don’t kill me now, report later
Appendix A: Cursors and Logging Examples
A.11 cursor exceptions, don’t kill me now, report later

execution plan (1)

7.7.2 The Reality
7 Database Links Are My Superpower
the execution plan you allow.

F

FORALL (3)

2 Cursors
…ed context switching). When the cursor is finished the FORALL bulk DML can be used to do mass update/insert or deletes significantly faster than traditional methods used in older c…
A.7 Better style cursor, bulk collect with Limit and FORALL
Appendix A: Cursors and Logging Examples
A.7 Better style cursor, bulk collect with Limit and FORALL
A.11 cursor exceptions, don’t kill me now, report later
Appendix A: Cursors and Logging Examples
This example demonstrates how to use FORALL with the SAVE EXCEPTIONS clause to perform a bulk insert while still capturing row-level errors. A PL/SQL collection is populated with s…

functions (7)

PL/SQL is my superpower.
… primary language used to create Oracle procedures and functions. It is the only language which directly affects the Oracle database. Because of this connection it is fast, efficie…
2 Cursors
…nto cursors that hold multiple rows—within procedures, functions, and other Oracle program units. A cursor is essentially a local, in-memory result set: a collection of rows and co…
3 Least Privilege
Using Collections in Stored Procedures, Functions, Packages
5.5 Autonomous Error Logging (DEBUG_PKG)
5 Logging Manager and Autonomous Error Logging
…at can be called from any code path—stored procedures, functions, triggers, or anonymous blocks—without risk.
7.6.3 They Integrate Natively With SQL
7 Database Links Are My Superpower
analytic functions,
7.12.1 SQL & Query Design (Highest Impact)
7.12 Database Link Performance Tuning Checklist
Ensure WHERE clauses reference base columns, not functions or expressions.
A.12.1 EVENT_LOG_OBJ Type
Appendix A: Cursors and Logging Examples
…LOG_OBJ instances, or later use this type in pipelined functions or APIs. Each attribute captures basic metadata about the event: when it occurred, which application/module wrote i…

H

hashing (9)

4.2.1 Parallel MERGE Processing Using Hash-Based Partitioning of CRM Change Data
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
…ing change set is divided into eight logical groups by hashing the primary key (PERSON_ID) into eight buckets. A scheduler job is launched for each bucket, resulting in eight concu…
4.2.1.2.2 Inserts Avoid “Right-Most Index Hot Block” Problems
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
Additionally, hashing ensures that each job inserts into disjoint primary key ranges.
4.2.1.5 Why Hash Partitioning Is Superior to Range Partitioning for MERGE
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
Hashing ensures statistically equal distribution
4.2.2 Parallel MERGE with a Parallel Manager Package
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
… a large CRM change file into eight logical buckets by hashing the person_id and launching eight scheduled jobs, each running a MERGE into the person table. This design solves two …
6.4.1 Step 1: Canonicalization (Stability First)
6 Vectors Are My Superpower
Before hashing anything, we canonicalize:
6.7 Handling Long Strings (Addresses, Free Text)
6 Vectors Are My Superpower
Instead of hashing raw addresses:
C.1 SQL Types (Vector + optional element list)
Appendix C: Vector Creation Logic
This is ideal for identity matching, hashing, indexing, and join performance.
C.2 Package Spec
Appendix C: Vector Creation Logic
- Uses STANDARD_HASH (SHA256) for stable hashing.
C.3 Package Body (canonicalization + hash → vector)
Appendix C: Vector Creation Logic
Deterministic number-to-text conversion for hashing and matching.

I

identity vector (1)

6.4 The Person Identity Vector (Canonical Example)
6 Vectors Are My Superpower
6.4 The Person Identity Vector (Canonical Example)

indexes (5)

6.5.4 Indexing actually works
6 Vectors Are My Superpower
Indexes on numeric hashes are:
6.11.3.3 Index Efficiency
6.11 Performance Benchmarks (Practical, Not Theoretical)
Indexes on numeric hash columns are:
7.7.3.2 Join Location
7 Database Links Are My Superpower
local joins (leverage local indexes)
7.12.2 Join Strategy & Execution Location
7.12 Database Link Performance Tuning Checklist
locally (to leverage local indexes).
7.12.3 Indexing & Data Shape
7.12 Database Link Performance Tuning Checklist
Remote filtering without indexes is the #1 DB link killer.

J

joins (11)

2 Cursors
…laim histories, or operational snapshots often require joins across numerous tables and must satisfy intricate business rules. SQL is uniquely suited to expressing those rules clea…
6.2 Why Identity Matching Is Harder Than It Looks
6 Vectors Are My Superpower
… approach — string comparisons, fuzzy matching, ad hoc joins — collapses under its own weight.
6.4.3 Step 3: Matching Becomes Trivial
6 Vectors Are My Superpower
No string joins.
6.6.1 Person ↔ Prescription (Pharmacy Systems)
6 Vectors Are My Superpower
…itable patient-medication reconciliation without fuzzy joins.
6.11.2 Comparison Summary
6.11 Performance Benchmarks (Practical, Not Theoretical)
Multi-column string joins
7.3.2 Why Views Simplify Database Link Usage
7 Database Links Are My Superpower
…aningful attributes rather than reconstructing complex joins across the link.
7.4 How a Database Link Works (Conceptually)
7 Database Links Are My Superpower
where joins occur
7.6.3 They Integrate Natively With SQL
7 Database Links Are My Superpower
joins across systems,
7.7.3.2 Join Location
7 Database Links Are My Superpower
remote joins (reduce data shipped)
7.12.2 Join Strategy & Execution Location
7.12 Database Link Performance Tuning Checklist
☐ Control where joins execute
7.12.3 Indexing & Data Shape
7.12 Database Link Performance Tuning Checklist
Hash-based joins dramatically reduce payload size.

L

logging (11)

5 Logging Manager and Autonomous Error Logging
5 Logging Manager and Autonomous Error Logging
5.1 Why a Logging Manager?
5 Logging Manager and Autonomous Error Logging
5.1 Why a Logging Manager?
5.2 Logging Manager Concepts
5 Logging Manager and Autonomous Error Logging
5.2 Logging Manager Concepts
5.3 Real-World Batch Example
5 Logging Manager and Autonomous Error Logging
The “air traffic control” for all of this was the logging manager.
5.5 Autonomous Error Logging (DEBUG_PKG)
5 Logging Manager and Autonomous Error Logging
5.5 Autonomous Error Logging (DEBUG_PKG)
7.9.3 Auditing & Traceability
7 Database Links Are My Superpower
APIs with weak logging,
Appendix A: Cursors and Logging Examples
Appendix A: Cursors and Logging Examples
A.12.2 EVENT_LOG Table
Appendix A: Cursors and Logging Examples
The EVENT_LOG table is the central repository for all logging events—normal checkpoints, errors, assertions, and test results. It is intentionally kept in sync with EVENT_LOG_OBJ s…
A.12.3 LOG_MANAGER Package Specification
Appendix A: Cursors and Logging Examples
…R package specification defines the public API for the logging system. It separates runtime checkpoints, error logging, and test assertions into a small set of procedures that any …
A.12.4 LOG_MANAGER Package Body
Appendix A: Cursors and Logging Examples
The package body implements the logging behavior. All writes go through the helper procedure WRITE_EVENT, which runs in an autonomous transaction so logging never interferes with t…
C.3 Package Body (canonicalization + hash → vector)
Appendix C: Vector Creation Logic
/* Replace with your error manager / logging framework */

M

metadata (4)

4.2.2.1 The Parallel Manager’s Role
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
…ge or bucket id to each job, and setting job names and metadata consistently.
A.12.1 EVENT_LOG_OBJ Type
Appendix A: Cursors and Logging Examples
…lined functions or APIs. Each attribute captures basic metadata about the event: when it occurred, which application/module wrote it, what kind of event it was, where in the code i…
B.1.1 Semantics
Appendix B: Parallel Processing and Scheduled Jobs
Insert/record metadata for this run (job name, max_jobs, run_limit).
B.4.2 Job Responsibilities
Appendix B: Parallel Processing and Scheduled Jobs
any other metadata it needs

N

nested tables (1)

3 Least Privilege
…REATE TYPE — if you define SQL-level collection types (nested tables or varrays in SQL scope).

P

parallel execution (3)

6.10 Why This Belongs in PL/SQL
6 Vectors Are My Superpower
parallel execution
6.11.3.2 Predictable Execution Paths
6.11 Performance Benchmarks (Practical, Not Theoretical)
safe parallel execution
B.10 How to describe this in your book
Appendix B: Parallel Processing and Scheduled Jobs
…ses further job creation. In this way, AUTO mode keeps parallel execution within a safe fraction of the system’s capacity instead of blindly launching all configured workers at onc…

parallel processing (3)

Appendices: Cursor and Parallel Processing Examples
Appendices: Cursor and Parallel Processing Examples
Appendix B: Parallel Processing and Scheduled Jobs
Appendix B: Parallel Processing and Scheduled Jobs
D.6.1 Database Host CPU Cost
Appendix D: Working with Network Engineers to Optimize Database Link Performance
parallel processing

pipelined functions (1)

A.12.1 EVENT_LOG_OBJ Type
Appendix A: Cursors and Logging Examples
…nto EVENT_LOG_OBJ instances, or later use this type in pipelined functions or APIs. Each attribute captures basic metadata about the event: when it occurred, which application/modu…

PL/SQL (21)

PL/SQL is my superpower.
PL/SQL is my superpower.
1 Introduction
…e following pages: Oracle relational database, SQL and PL/SQL. My experience is mostly with ETLs and CRM systems and their attendant analytic reporting systems. My primary focus wi…
2 Cursors
The single most important capability of PL/SQL is its ability to execute SQL statements and store the results—either into individual variables or into cursors that hold multiple ro…
3 Least Privilege
…st privilege” philosophy for its databases and because PL/SQL is part of the database this philosophy has spilled over to how it is managed in most organizations. So many times, I …
4 ETL
A common and practical application of PL/SQL is in the processing of incoming data files—whether from operational systems such as CRM platforms or during data migration from legacy…
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
PL/SQL allows several different ways of doing parallel operations. One important method is to use autonomous scheduled jobs using dbms_scheduler and a concept of a parallel manager…
4.2.2.1 The Parallel Manager’s Role
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
The parallel manager is a PL/SQL package that centralizes all the logic for launching and controlling parallel jobs. Conceptually, it does three things:
5.4 The check_point Pattern
5 Logging Manager and Autonomous Error Logging
In PL/SQL, there is no built-in, reliable way to say “I’m at line X” in a way that will still be correct after edits, refactoring, or code formatting changes. Line numbers in error…
5.5 Autonomous Error Logging (DEBUG_PKG)
5 Logging Manager and Autonomous Error Logging
…imple, reliable way to log diagnostic information from PL/SQL code without ever impacting your main transaction.
6.1 Ultra-High-Speed Record Matching with PL/SQL
6 Vectors Are My Superpower
6.1 Ultra-High-Speed Record Matching with PL/SQL
6.2 Why Identity Matching Is Harder Than It Looks
6 Vectors Are My Superpower
…tor-based identity matching pattern that allows Oracle PL/SQL systems to perform millions of deterministic identity comparisons per second, safely, repeatably, and auditable — with…
6.10 Why This Belongs in PL/SQL
6 Vectors Are My Superpower
6.10 Why This Belongs in PL/SQL
6.11.7 Key Takeaway
6.11 Performance Benchmarks (Practical, Not Theoretical)
In high-volume PL/SQL systems, that combination is more valuable than raw speed alone.
6.11.8 Final Thought
6.11 Performance Benchmarks (Practical, Not Theoretical)
…nto a fast, deterministic, auditable computation — and PL/SQL is one of the best tools on earth for executing that pattern at scale.
7.7.3.3 Bulk Operations
7 Database Links Are My Superpower
This aligns perfectly with good PL/SQL practice.
7.12 Database Link Performance Tuning Checklist
PL/SQL or SQL-driven integration
7.12.6 PL/SQL Usage Patterns
7.12 Database Link Performance Tuning Checklist
7.12.6 PL/SQL Usage Patterns
A.7 Better style cursor, bulk collect with Limit and FORALL
Appendix A: Cursors and Logging Examples
…in batches of up to 100 from the v_persons view into a PL/SQL collection. For each batch, the code computes two derived values: birth_year and tax_birth_year. Both are calculated i…
A.9 How to monitor memory consumption using PGA memory size
Appendix A: Cursors and Logging Examples
…a (PGA), which is the private memory allocated to your PL/SQL process. Large collections can consume significant PGA, especially when rows contain many columns or when multiple col…
A.11 cursor exceptions, don’t kill me now, report later
Appendix A: Cursors and Logging Examples
… bulk insert while still capturing row-level errors. A PL/SQL collection is populated with several test records, some of which intentionally violate table constraints—for example, …
A.12.3 LOG_MANAGER Package Specification
Appendix A: Cursors and Logging Examples
…est assertions into a small set of procedures that any PL/SQL code can call. At runtime you typically use CHECKPOINT_START, CHECKPOINT_END, and LOG_ERROR, while ASSERT_CHECKPOINT, …

primary key (5)

4.2.1 Parallel MERGE Processing Using Hash-Based Partitioning of CRM Change Data
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
…et is divided into eight logical groups by hashing the primary key (PERSON_ID) into eight buckets. A scheduler job is launched for each bucket, resulting in eight concurrent MERGE …
4.2.1.2.1 Updates Are Targeting Different Rows
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
… the rows are distributed across table blocks by their primary key order.
4.2.1.2.2 Inserts Avoid “Right-Most Index Hot Block” Problems
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
…y, hashing ensures that each job inserts into disjoint primary key ranges.
A.11 cursor exceptions, don’t kill me now, report later
Appendix A: Cursors and Logging Examples
…ple, a NULL value in a NOT NULL column and a duplicate primary key. The FORALL statement attempts to insert all rows in one bulk operation, and when individual rows fail, Oracle re…
B.4.2 Job Responsibilities
Appendix B: Parallel Processing and Scheduled Jobs
-- Example: hash on primary key using worker_id and max_jobs

procedures (7)

PL/SQL is my superpower.
…. PL/SQL is the primary language used to create Oracle procedures and functions. It is the only language which directly affects the Oracle database. Because of this connection it i…
2 Cursors
…riables or into cursors that hold multiple rows—within procedures, functions, and other Oracle program units. A cursor is essentially a local, in-memory result set: a collection of…
3 Least Privilege
Using Collections in Stored Procedures, Functions, Packages
5.1 Why a Logging Manager?
5 Logging Manager and Autonomous Error Logging
… a logging manager offers a small, standardized set of procedures that any component in the application can call to report activity, status, or problems. These calls write to one o…
5.5 Autonomous Error Logging (DEBUG_PKG)
5 Logging Manager and Autonomous Error Logging
…t” logger that can be called from any code path—stored procedures, functions, triggers, or anonymous blocks—without risk.
A.12.3 LOG_MANAGER Package Specification
Appendix A: Cursors and Logging Examples
…error logging, and test assertions into a small set of procedures that any PL/SQL code can call. At runtime you typically use CHECKPOINT_START, CHECKPOINT_END, and LOG_ERROR, while…
A.12.4 LOG_MANAGER Package Body
Appendix A: Cursors and Logging Examples
…an record start/end markers and errors, while the test procedures record assertions as structured events in EVENT_LOG.

Q

query optimization (1)

7.4 How a Database Link Works (Conceptually)
7 Database Links Are My Superpower
It is distributed query optimization.

R

records (4)

6.2 Why Identity Matching Is Harder Than It Looks
6 Vectors Are My Superpower
“Are these two records the same thing?”
6.11.1 Baseline Scenario
6.11 Performance Benchmarks (Practical, Not Theoretical)
5–20 million incoming records per synchronization cycle
A.11 cursor exceptions, don’t kill me now, report later
Appendix A: Cursors and Logging Examples
…rs. A PL/SQL collection is populated with several test records, some of which intentionally violate table constraints—for example, a NULL value in a NOT NULL column and a duplicate…
B.5 How This Fits the Earlier Story
Appendix B: Parallel Processing and Scheduled Jobs
Records worker_id / max_jobs mapping

rollback (1)

A.12.4 LOG_MANAGER Package Body
Appendix A: Cursors and Logging Examples
…o logging never interferes with the caller’s commit or rollback. The global variables remember the current application, module, and last checkpoint. Runtime code can record start/e…

row-level (5)

4.2.1.2.3 Direct-Path INSERT Removes Further Contention
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
traditional row-level locking issues
4.2.1.4 How this Strategy Avoids the Classic Oracle Contention Patterns
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
Row-level locking conflicts
7.9.2 Authorization & Least Privilege
7 Database Links Are My Superpower
Fine-grained access control, VPD, and row-level security still apply.
7.9.3 Auditing & Traceability
7 Database Links Are My Superpower
message queues without row-level traceability,
A.11 cursor exceptions, don’t kill me now, report later
Appendix A: Cursors and Logging Examples
… clause to perform a bulk insert while still capturing row-level errors. A PL/SQL collection is populated with several test records, some of which intentionally violate table const…

S

scheduler (4)

4.2.1 Parallel MERGE Processing Using Hash-Based Partitioning of CRM Change Data
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
…hing the primary key (PERSON_ID) into eight buckets. A scheduler job is launched for each bucket, resulting in eight concurrent MERGE operations running in parallel.
4.2.2.2 Can the Parallel Manager Auto-Sense Load?
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
How many scheduler jobs are already running?
B.2 “AUTO” Concurrency: How the Manager Chooses a Limit
Appendix B: Parallel Processing and Scheduled Jobs
Read job_queue_processes (upper bound on concurrent scheduler jobs):
B.4.1 Job Entry Point
Appendix B: Parallel Processing and Scheduled Jobs
The scheduler job associated with p_job_name = 'PERSON_MERGE' calls this run procedure.

SQL (40)

PL/SQL is my superpower.
PL/SQL is my superpower.
1 Introduction
…er in the following pages: Oracle relational database, SQL and PL/SQL. My experience is mostly with ETLs and CRM systems and their attendant analytic reporting systems. My primary …
2 Cursors
The single most important capability of PL/SQL is its ability to execute SQL statements and store the results—either into individual variables or into cursors that hold multiple ro…
3 Least Privilege
…privilege” philosophy for its databases and because PL/SQL is part of the database this philosophy has spilled over to how it is managed in most organizations. So many times, I hav…
4 ETL
A common and practical application of PL/SQL is in the processing of incoming data files—whether from operational systems such as CRM platforms or during data migration from legacy…
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
PL/SQL allows several different ways of doing parallel operations. One important method is to use autonomous scheduled jobs using dbms_scheduler and a concept of a parallel manager…
4.2.2.1 The Parallel Manager’s Role
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
The parallel manager is a PL/SQL package that centralizes all the logic for launching and controlling parallel jobs. Conceptually, it does three things:
5.4 The check_point Pattern
5 Logging Manager and Autonomous Error Logging
In PL/SQL, there is no built-in, reliable way to say “I’m at line X” in a way that will still be correct after edits, refactoring, or code formatting changes. Line numbers in error…
5.5 Autonomous Error Logging (DEBUG_PKG)
5 Logging Manager and Autonomous Error Logging
…le, reliable way to log diagnostic information from PL/SQL code without ever impacting your main transaction.
6.1 Ultra-High-Speed Record Matching with PL/SQL
6 Vectors Are My Superpower
6.1 Ultra-High-Speed Record Matching with PL/SQL
6.2 Why Identity Matching Is Harder Than It Looks
6 Vectors Are My Superpower
…-based identity matching pattern that allows Oracle PL/SQL systems to perform millions of deterministic identity comparisons per second, safely, repeatably, and auditable — without…
6.10 Why This Belongs in PL/SQL
6 Vectors Are My Superpower
6.10 Why This Belongs in PL/SQL
6.11.7 Key Takeaway
6.11 Performance Benchmarks (Practical, Not Theoretical)
In high-volume PL/SQL systems, that combination is more valuable than raw speed alone.
6.11.8 Final Thought
6.11 Performance Benchmarks (Practical, Not Theoretical)
… a fast, deterministic, auditable computation — and PL/SQL is one of the best tools on earth for executing that pattern at scale.
7.2 What a Database Link Actually Is
7 Database Links Are My Superpower
… core, a database link is a controlled, authenticated, SQL-level network connection between two Oracle databases.
7.4 How a Database Link Works (Conceptually)
7 Database Links Are My Superpower
SQL is decomposed into:
7.6.2 They Avoid Unnecessary Data Movement
7 Database Links Are My Superpower
stream results directly into SQL operations
7.6.3 They Integrate Natively With SQL
7 Database Links Are My Superpower
7.6.3 They Integrate Natively With SQL
7.7.2 The Reality
7 Database Links Are My Superpower
the SQL you write,
7.7.3.3 Bulk Operations
7 Database Links Are My Superpower
set-based SQL,
7.8 Network Tuning: The Hidden Half of Database Link Performance
7 Database Links Are My Superpower
…— it is a direct consequence of performing distributed SQL execution across system boundaries.
7.8.1 Transparency Versus Indirection
7 Database Links Are My Superpower
…take the opposite approach. They move data only when a SQL statement demands it, stream only the rows that qualify, and surface network behavior directly through execution plans an…
7.8.4 Practical Design Implications
7 Database Links Are My Superpower
…t alone is insufficient; latency dominates distributed SQL behavior.
7.8.5 Why This Is Difficult to Negotiate
7 Database Links Are My Superpower
Database teams control SQL, schemas, and execution plans.
7.12 Database Link Performance Tuning Checklist
PL/SQL or SQL-driven integration
7.12.1 SQL & Query Design (Highest Impact)
7.12 Database Link Performance Tuning Checklist
7.12.1 SQL & Query Design (Highest Impact)
7.12.6 PL/SQL Usage Patterns
7.12 Database Link Performance Tuning Checklist
7.12.6 PL/SQL Usage Patterns
7.12.7 Monitoring & Diagnostics
7.12 Database Link Performance Tuning Checklist
Look for SQL*Net message from dblink events.
7.12.9 Final Rule of Thumb
7.12 Database Link Performance Tuning Checklist
DB links don’t create bad SQL.
7.12.10 Final Thought
7.12 Database Link Performance Tuning Checklist
…hanism that operates at the same level of rigor as the SQL engine itself.
A.2 One possible SQL to query this table would be below
Appendix A: Cursors and Logging Examples
A.2 One possible SQL to query this table would be below
A.7 Better style cursor, bulk collect with Limit and FORALL
Appendix A: Cursors and Logging Examples
…batches of up to 100 from the v_persons view into a PL/SQL collection. For each batch, the code computes two derived values: birth_year and tax_birth_year. Both are calculated in m…
A.9 How to monitor memory consumption using PGA memory size
Appendix A: Cursors and Logging Examples
…PGA), which is the private memory allocated to your PL/SQL process. Large collections can consume significant PGA, especially when rows contain many columns or when multiple collec…
A.11 cursor exceptions, don’t kill me now, report later
Appendix A: Cursors and Logging Examples
…lk insert while still capturing row-level errors. A PL/SQL collection is populated with several test records, some of which intentionally violate table constraints—for example, a N…
A.12.3 LOG_MANAGER Package Specification
Appendix A: Cursors and Logging Examples
… assertions into a small set of procedures that any PL/SQL code can call. At runtime you typically use CHECKPOINT_START, CHECKPOINT_END, and LOG_ERROR, while ASSERT_CHECKPOINT, TES…
C.1 SQL Types (Vector + optional element list)
Appendix C: Vector Creation Logic
C.1 SQL Types (Vector + optional element list)
D.3.1.1 What changes
Appendix D: Working with Network Engineers to Optimize Database Link Performance
Distributed SQL execution is dominated by round-trip latency, not bandwidth. Even small reductions compound quickly.
D.3.3.1 What changes
Appendix D: Working with Network Engineers to Optimize Database Link Performance
…ases retransmissions — both disastrous for synchronous SQL execution.
D.4 Where the Encryption Cost Actually Occurs
Appendix D: Working with Network Engineers to Optimize Database Link Performance
…ifies the effect of round-trip latency for synchronous SQL execution.
D.13 The Architectural Reality
Appendix D: Working with Network Engineers to Optimize Database Link Performance
SQL execution

SQLERRM (2)

2 Cursors
…ode (like 1 for ORA-00001, or 1400 for ORA-01400). Use SQLERRM(...) to get the message.
C.3 Package Body (canonicalization + hash → vector)
Appendix C: Vector Creation Logic
raise_err('canonicalize_text failed: ' || SQLERRM);

STANDARD_HASH (2)

C.2 Package Spec
Appendix C: Vector Creation Logic
- Uses STANDARD_HASH (SHA256) for stable hashing.
C.3 Package Body (canonicalization + hash → vector)
Appendix C: Vector Creation Logic
- STANDARD_HASH(..., 'SHA256') returns hex string

stored procedures (2)

3 Least Privilege
Using Collections in Stored Procedures, Functions, Packages
5.5 Autonomous Error Logging (DEBUG_PKG)
5 Logging Manager and Autonomous Error Logging
…d-forget” logger that can be called from any code path—stored procedures, functions, triggers, or anonymous blocks—without risk.

T

triggers (1)

5.5 Autonomous Error Logging (DEBUG_PKG)
5 Logging Manager and Autonomous Error Logging
…alled from any code path—stored procedures, functions, triggers, or anonymous blocks—without risk.

tuning (11)

4.2.2.2 Can the Parallel Manager Auto-Sense Load?
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
For more advanced tuning you can look at:
6.11.6 Benchmarking Guidance for Your Own System
6.11 Performance Benchmarks (Practical, Not Theoretical)
The difference will show up quickly — usually before tuning even begins.
7.8 Network Tuning: The Hidden Half of Database Link Performance
7 Database Links Are My Superpower
7.8 Network Tuning: The Hidden Half of Database Link Performance
7.8.4 Practical Design Implications
7 Database Links Are My Superpower
…as a first-class design concern, not a post-deployment tuning exercise.
7.8.5 Why This Is Difficult to Negotiate
7 Database Links Are My Superpower
Network tuning is challenging not because it is technically complex, but because responsibility is distributed.
7.8.6 The Architectural Payoff
7 Database Links Are My Superpower
tuning becomes evidence-based
7.12 Database Link Performance Tuning Checklist
7.12 Database Link Performance Tuning Checklist
7.12.8 Anti-Patterns to Avoid (Red Flags)
7.12 Database Link Performance Tuning Checklist
If you see these, stop tuning — rewrite the design.
A.9 How to monitor memory consumption using PGA memory size
Appendix A: Cursors and Logging Examples
When tuning the LIMIT value, you can use a simple query against PGA statistics to see how much memory your session is consuming during bulk operations. For example, a query on V$PG…
B.9 Caveats you probably want to mention in the book
Appendix B: Parallel Processing and Scheduled Jobs
Over-tuning can hurt throughput.
Appendix D: Working with Network Engineers to Optimize Database Link Performance
…tabase link performance is often treated as a database tuning problem. In practice, the most dramatic and durable improvements frequently occur outside the database, once network e…

V

validation (1)

4 ETL
…phase of the ETL process. For reasons of stability and validation, transformation is often performed after the data has been successfully loaded and verified in the target environm…

VARCHAR2 (8)

5.4 The check_point Pattern
5 Logging Manager and Autonomous Error Logging
check_point is usually VARCHAR2(200)
A.7 Better style cursor, bulk collect with Limit and FORALL
Appendix A: Cursors and Logging Examples
column tax_birth_year VARCHAR2(4) added for demonstration.
B.1 New Interface: parallel_manager.runjob
Appendix B: Parallel Processing and Scheduled Jobs
p_job_name IN VARCHAR2,
B.2 “AUTO” Concurrency: How the Manager Chooses a Limit
Appendix B: Parallel Processing and Scheduled Jobs
p_job_name IN VARCHAR2,
B.3 Letting the Job Ask: “What Parallel Slice Am I?”
Appendix B: Parallel Processing and Scheduled Jobs
job_name VARCHAR2(100),
B.4.1 Job Entry Point
Appendix B: Parallel Processing and Scheduled Jobs
PROCEDURE run_parallel_job(p_job_name IN VARCHAR2);
C.2 Package Spec
Appendix C: Vector Creation Logic
- to_vector(p_value VARCHAR2) RETURN t_vector6_obj
C.3 Package Body (canonicalization + hash → vector)
Appendix C: Vector Creation Logic
g_pos VARCHAR2(4000);

vectors (12)

6 Vectors Are My Superpower
6 Vectors Are My Superpower
6.3 The Core Idea: Identity as a Vector, Not a Row
6 Vectors Are My Superpower
Once vectors exist, identity matching becomes math, not text processing.
6.5.3 Precomputation eliminates rework
6 Vectors Are My Superpower
Vectors are computed:
6.6.1 Person ↔ Prescription (Pharmacy Systems)
6 Vectors Are My Superpower
Drug vectors represent medication identity
6.6.2 Person ↔ Weapon (Regulated Systems)
6 Vectors Are My Superpower
Event vectors capture transfers, prohibitions, recoveries
6.6.3 Person ↔ Customer (CRM Systems)
6 Vectors Are My Superpower
Account or interaction vectors track behavior
6.7 Handling Long Strings (Addresses, Free Text)
6 Vectors Are My Superpower
You don’t compare addresses — you compare address vectors.
6.8 Privacy-Sensitive Attributes (SSN, National IDs)
6 Vectors Are My Superpower
Vectors shine brightest when privacy matters.
6.9 Near-Match Detection Without Guessing
6 Vectors Are My Superpower
Vectors do exact matching.
6.11.2 Comparison Summary
6.11 Performance Benchmarks (Practical, Not Theoretical)
Canonicalized + hashed vectors
6.11.3.4 Elimination of Rework
6.11 Performance Benchmarks (Practical, Not Theoretical)
Vectors are:
6.11.8 Final Thought
6.11 Performance Benchmarks (Practical, Not Theoretical)
Vectors let us turn identity matching from a messy text problem into a fast, deterministic, auditable computation — and PL/SQL is one of the best tools on earth for executing that …

Table of contents