How to compare two Oracle Database schemas with SQLcl

News, , , , , , , , , , , , , , , , ,

I have two environments:

  1. HR123 – I started a simple schema with just two tables, and no comments, indexes, constraints, etc
  2. HR456 – I have a Codex that helps me come up with what a real HR system would look like

What do I want

I would like to point my Oracle tools at two environments (schema), and provide a list of their differences. This ‘difference’ will be expressed as a SQL script that will allow me to update the HR123, to match what I have on the HR456.

How to get there

Just start SQLcl (download | documents), no connection required. However, you need two connections defined, one for the target and one for the destination schema.

These can be in the same or different databases. In my example, these are two different schemas in the same database.

Run the DIFF command. By default, the output will return as a ZIP file in the current working directory.

Once done, unzip the archive, cd to root level, and launch VS Code or your favorite IDE.

Party

In VS Code, if I navigate to the ‘next’ release, I will see the upgrade script that our ‘installer’ artifact will run if we actually apply changes to the Source schema. Ultimately, this will match our Target schema.

Under my tables folder, I can see some table SQL files, and if I check employees.sql, I see:

SQL