Ranges with NULLs 04: Pack, Merge

In this post, I'm going to treat two closely related but distinct requirements:
  1. "Pack": in a SELECT, return contiguous or overlapping ranges as one range.
  2. "Merge": modify a table to reduce contiguous or overlapping ranges to one range.
These operations should take into account other data associated with each range: ranges are packed or merged only when the other data is the same.

Ranges with NULLs 03: Gaps

When I wrote "Gaps in Date Ranges: when are you free?", I handled NULLs using "magic" values. This time I'll try not to cheat.

Ranges with NULLs 2: test cases

When I write SQL there is a bit of trial and error (laughter), so I need as complete a set of test cases as possible. Here's what I came up with.

Ranges with NULLs 1: starting over

I have written a lot about ranges, mostly based on dates, and I have tried my best to avoid NULLs in "from" and "to" columns. I give up: NULLs are a fact of life and must be dealt with. This means revisiting all my previous work!

Optimistic Locking 9: proof of concept

To wind up this series about optimistic locking using the SCN, here is a sample API for querying and updating the DEPT table, using JSON as the format for exchanging data with the client application.

Optimistic Locking 8: double-checking with flashback

Some optimistic locking methods, including the SCN method, can detect intervening updates even when column values have not really changed. With the SCN method, we can use flashback queries to confirm that real changes occurred.

Optimistic Locking 7: Restartability

When we UPDATE, Oracle may find a discrepancy between the read-consistent and "current" versions of the data. If so, Oracle "restarts" the process. Our optimistic locking solution must make that happen when appropriate.

CSV from CLOB with field enclosures

After my post about extracting CSV without enclosures from a CLOB, here is my solution for CSV with enclosures. It wasn't easy...

Extract from CLOB with JSON objects

On AskTOM, Kim Berg Hansen recently used JSON_OBJECT to parse CLOB data in "name=value" format. I added a variant based on my work with CSV data.