DIFF

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.
`-objects`	String (List)	No	Comma separated DDL object names or patterns to export. Only matching objects are exported.
`-exclude-objects`	String (List)	No	Comma separated DDL object names or patterns to exclude from export.
`-types`	String (List)	No	Comma separated DDL object types or patterns to export. All objects matching the types are exported.
`-exclude-types`	String (List)	No	Comma separated DDL object types or patterns to exclude from export.
`-dml`	String (List)	No	Comma separated table names whose data is exported and compared. Only these tables receive DML comparison.
`-exclude-dml`	String (List)	No	Comma separated table names whose data must not be exported or compared. All other table data is included.
`-objects-file`	String (Path to a directory)	No	File containing comma or newline separated DDL object names or patterns to export. Only matching objects are exported.
`-exclude-objects-file`	String (Path to a directory)	No	File containing comma or newline separated DDL object names or patterns to exclude from export.
`-types-file`	String (Path to a directory)	No	File containing comma or newline separated DDL object types to export. All objects matching the types are exported.
`-exclude-types-file`	String (Path to a directory)	No	File containing comma or newline separated DDL object types to exclude from export.
`-dml-file`	String (Path to a directory)	No	File containing comma or newline separated table names whose data is exported and compared. Only these tables receive DML comparison.
`-exclude-dml-file`	String (Path to a directory)	No	File containing comma or newline separated table names whose data must not be exported or compared.

Filtering Processed Objects

Use filters to control which objects and table data diff exports before comparing the source and target schemas. Filtering can reduce the size of the generated artifact and limit it to the changes that you want to review or deploy.

diff provides two types of filters:

DDL filters control which database object definitions, such as tables, views, and materialized views, are exported and compared.
DML filters control which table data is exported and compared.

DDL Filters

Use -objects to include only specific named objects:
```
diff -source dev_conn -target test_conn -objects EMPLOYEES,DEPARTMENTS
```
This command exports and compares only objects named EMPLOYEES or DEPARTMENTS.
Use -types to include only specific object types:
```
diff -source dev_conn -target test_conn -types TABLE,VIEW
```
This command exports and compares only tables and views.
When you specify both -objects and -types, an object must match both filters:
```
diff -source dev_conn -target test_conn 
-objects EMPLOYEES,DEPARTMENTS -types TABLE 
```
This command exports EMPLOYEES and DEPARTMENTS only if they are tables. For example, a view named EMPLOYEES is not exported.
When an option contains multiple values, an object can match any of the specified values. For example:
```
-objects EMPLOYEES,DEPARTMENTS
```
This option includes objects named EMPLOYEES or DEPARTMENTS.
Use -exclude-objects to exclude matching objects from the selected objects:
```
diff -source dev_conn -target test_conn -types TABLE,VIEW 
-exclude-objects AUDIT_%
```
This command exports tables and views except objects with names that begin with AUDIT_.

DML Filters

DML filters apply only to table data. They do not affect which object definitions are exported by the DDL filters.

Use -dml to include data from specific tables:
```
diff -source dev_conn -target test_conn -dml EMPLOYEES,DEPARTMENTS
```
This command exports and compares data only from the EMPLOYEES and DEPARTMENTS tables.
Use -exclude-dml to exclude data from specific tables:
```
diff -source dev_conn -target test_conn -exclude-dml AUDIT_LOG
```
This command exports and compares data from all eligible tables except AUDIT_LOG.

Combining DDL and DML Filters

DDL and DML filters operate independently.

For example:

diff -source dev_conn -target test_conn -types TABLE -dml EMPLOYEES

This command exports the DDL for all tables but exports table data only for EMPLOYEES. Therefore, changes to the EMPLOYEES table definition and its data can both appear in the generated artifact.

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
```

14.11 DIFF