1 Uncool Thing about the COMPARE_SYNC Package

It only works in version 12 :( That’s right, I didn’t test in previous versions and there was a bug. Please refer to the COMPARE_SYNC post for the new, improved version. Here is what I changed: The name is now COMPARE_SYNC. I don’t know what got into me to put those four meaningless letters in front. The […]

Comparing NULLable Values

If a column or expression may be NULL, it is a pain to compare it to something else. Why? Because comparisons involving a NULL do not result in TRUE or FALSE: they result in NULL. I use the DECODE function to work around this problem: here's why - and how.

10 Cool things about the COMPARE_SYNC package

@thatjeffsmith recently recommended an article about making your blog more popular. The article said "lists of 10 things" were great ways to get more readers. Hey, if that's all it takes... STEW_COMPARE_SYNC is a package that generates SQL to compare data or synchronize tables. Here are 10 good reasons to use it.

Way Too Invisible Columns

Oracle Database 12c introduced "invisible columns": they are only visible when you name them explicitly in the SELECT list. Unfortunately, they seem to be even more invisible when you access them through a database link! Here are some surprising results from SELECT and MERGE statements.

MERGE and invisible columns = invisible documentation?

Oracle 12c introduced "invisible columns" to help us add columns to tables without breaking existing applications. The documentation explains how they work with SELECT and INSERT, but not MERGE. Here's what happened when I tried MERGE.

STEW_COMPARE_SYNC: Introducing the package

After many blog posts about comparing and synchronizing tables, I have united all the techniques I presented in one place. The STEW_COMPARE_SYNC package generates SQL for
  • Comparing tables, views and queries, both local and remote.
  • Synchronizing, or applying changes to target tables from either source tables or "Change Data Capture" input.

Compare and Sync without Primary Keys

I have written a lot about comparing and synchronizing tables. My examples always had both primary keys and non-key columns, so I could do updates along with inserts and deletes. What about the other tables? Here's a technique that works for them.

Sync tables: generate MERGE using Unique constraint

In my post “Compare and sync tables: Generating the code“, I use the primary key constraint on the target table. A reader called “Bal” asked how to use a unique constraint instead. Test data I’m going to create a target table with a unique constraint, but without any NOT NULL constraints on the columns. UK1 […]

MERGE using Change Data Capture (CDC)

Use MERGE to apply "Change Data Capture" input to a target table with one SQL statement.