12.10 DIFF

Use this command to compare two Oracle Database schemas and generate an artifact file containing the necessary changes to synchronize them. This is especially useful for CI/CD workflows, and the final output can be deployed anywhere as needed.

The diff command connects to both schemas, exports their objects, compares them, and generates the SQL changes required to make the source schema match the target schema. It packages these changes into a deployable ZIP artifact that can be deployed by using the PROJECT DEPLOY command. The artifact can be deployed directly or extracted to review its contents before deployment.

The -source and -target options accept either a saved SQLcl connection name or a connection string, such as an Easy Connect string or a JDBC URL.

Prerequisites

Before using the diff command, ensure that:

  • SQLcl can connect to both the source and target schemas.
  • The source schema is available through either a saved SQLcl connection or the -source connection string.
  • The target schema is specified with the -target option, or you are already connected to it in SQLcl.
  • The users have the required privileges to read and export the schema objects being compared.
  • You have write access to the artifact output directory.
  • Git is installed and available on the system path, because the command uses temporary project worktrees internally.
  • The SQLcl Project extension is installed and available to deploy the generated artifact.

Syntax

diff|di -source <source> [-target <target>] [-artifact-out <directory>] [-generate-rollback] [-project-dir <directory>] [-parallel-exports] [-log] [-verbose] [-debug]

Options

Option Type Required Description
-source String (connection name or connection string) Yes The source connection to compare. Specify a saved SQLcl connection name, an Easy Connect string, or a JDBC URL. The generated forward artifact contains the changes required to make the source schema match the target schema.
-target String (connection name or connection string) No The target connection to compare against. Specify a saved SQLcl connection name, an Easy Connect string, or a JDBC URL. If you omit this option, SQLcl uses the current connection as the target.
-artifact-out String (path to a directory)   The directory where SQLcl writes the generated ZIP artifact. If you omit this option, SQLcl writes the artifact to the current working directory.
-generate-rollback Flag No Generate a rollback artifact to move the target schema back toward the source schema.
-project-dir String (path to a directory) No Reuse an existing SQLcl project instead of creating a temporary project.
-parallel-exports Flag No Export the source and target schemas at the same time. This can reduce runtime.
-log Flag No Write command output to diff.log in the artifact output directory.
-verbose Flag No Print additional progress messages.
-debug Flag No Print exception details and stack traces for troubleshooting.
Using the Artifact

After the artifact is generated, SQLcl creates a deployable ZIP file in the specified output directory (or the current working directory if -artifact-out is omitted). Deploy the artifact using the SQLcl Project extension: 

project deploy -file /path/to/diff_SOURCE_TARGET_artifact.zip

The ZIP file does not need to be extracted before deployment.

Deploying the artifact applies the changes required to make the source schema match the target schema.

Reviewing the Artifact

Each artifact generated by the diff command is structured so that you can easily audit, review and understand the changes it will apply. This review might be important in minimizing the risk of unexpected changes in production environments.

Artifact Structure

├── env
│   ├── defaultProperties.sql
│   └── undefineDefaultProperties.sql
├── install.sql
├── releases
│   ├── main.changelog.sql
│   └── next
│       ├── changes
│       │   └── target_<schema_name>
│       │       └── stage.changelog.sql
│       │       └── <object_type>
│       │           └── <object_name> 
│       └── release.changelog.sql
└── utils
    ├── prechecks.sql
    └── recompile.sql

The artifact contains the following key files and directories:

  • env/defaultProperties.sql and env/undefineDefaultProperties.sql define and clear environment properties used during deployment. These files are included by default as part of the SQLcl Project structure but are not used by the diff command.
  • install.sql is the main entry point for deploying the artifact. By default, it invokes the main changelog and includes optional precheck and recompilation scripts. These optional scripts are commented out. Uncomment them if you want to run the prechecks or recompilation during deployment.
  • releases contains the project structure that represents the schema differences.
  • main.changelog.sql is the main script that invokes the object-level change scripts required to synchronize the source schema with the target schema.
  • releases/next/changes/target_<schema_name> contains the generated object-level changes. The changes are organized by object type, with one SQL file per object. For example:
    • /table/EMPLOYEE.sql
    • /view/EMPLOYEE_VIEW.sql

These files contain the SQL statements that implement the schema differences and form the core of the generated artifact.

To review the artifact and the changes:
  • Find the artifact ZIP file generated by the diff extension in the specified output directory.
  • If you want to browse the files directly, extract the contents of the ZIP file to a review directory.
  • Navigate to the /releases/next/changes/target_<schema_name> directory.
  • Browse the folders categorised by object type (TABLE, VIEW, PROCEDURE).
  • Examine each object-level change file, such as TABLE/EMPLOYEES.sql.
  • For each change file, compare the SQL statements with the current definitions in your source and target schemas as needed to verify or understand the differences.

Examples

  • Compare the dev_conn and test_conn schemas and generate a deployment artifact in the /tmp/schema-diff directory.
    diff -source dev_conn -target test_conn -artifact-out /tmp/schema-diff
  • Compare the dev_conn and test_conn schemas and generate both a forward deployment artifact and a rollback artifact in the /tmp/schema-diff directory.
    diff -source dev_conn -target test_conn -artifact-out /tmp/schema-diff -generate-rollback
  • Compare the dev_conn and test_conn schemas using the SQLcl project located at /path/to/project and generate the deployment artifact in the /tmp/schema-diff directory.
    diff -source dev_conn -target test_conn -project-dir /path/to/project -artifact-out /tmp/schema-diff