I have two environments:
- HR123 – I started a bare bones schema with just two tables, and no comments, indexes, constraints, etc
- HR456 – I had Codex help me generate what a real HR system should look like
What I want
I would like to point my Oracle tools to the two environments (schemas), and spit out a list of differences. These ‘diffs’ would be expressed as SQL scripts that would allow me to update HR123, to match what I have in HR456.
How to get there
Simply startup SQLcl (download | docs), no connection necessary. However, you’ll need two defined connections, one for the target and one for the destination schema.
These can be on the same or different databases. In my instance it’s two different schemas in the same database.
Run the DIFF command. By default the output will come back as a ZIP file in the current working directory.
Once it’s done, unzip that archive, cd to the root level, and launch VS Code or your favorite IDE.
-- startup SQlcl
Macmini:~ thatjeffsmith$ /opt/sqlcl/26.1.2/sqlcl/bin/sql /nolog
SQLcl: Release 26.1 Production on Fri Jun 05 11:37:25 2026
Copyright (c) 1982, 2026, Oracle. All rights reserved.
SP2-0640: Not connected
-- do the diff
SQL> diff -source HR123 -target HR456 -artifact-out /Users/thatjeffsmith/diffs/HRs
[diff - info] Preparing clean project directory: /var/folders/80/hvhx7jbd05lf0fsd8wzt089w0000gn/T/diff_project_9517047203889056995
[diff - info] Initializing project with schemas: HR123,HRREST
[diff - info] Generating diffs via project stage against: base_HR123
[diff - info] Generating artifact (SQLcl default location)
[diff - info] Artifact copied from "/var/folders/80/hvhx7jbd05lf0fsd8wzt089w0000gn/T/diff_project_9517047203889056995/artifact/diff_HR123_vs_HRREST.zip" to "/Users/thatjeffsmith/diffs/HRs/diff_HR123_HRREST_artifact.zip"
[diff - info] Success. Diffs under dist/releases/next. Logs at: null
[diff - info] Artifact saved to: /Users/thatjeffsmith/diffs/HRs
SQL> cd /Users/thatjeffsmith/diffs/HRs
SQL> !unzip diff_HR123_HRREST_artifact.zip
Archive: diff_HR123_HRREST_artifact.zip
inflating: releases/next/changes/target_hrrest/hrrest/procedures/secure_dml.sql
inflating: releases/next/changes/target_hrrest/hrrest/procedures/add_job_history.sql
inflating: releases/next/changes/target_hrrest/hrrest/comments/jobs.sql
inflating: releases/next/changes/target_hrrest/hrrest/comments/departments.sql
inflating: releases/next/changes/target_hrrest/hrrest/comments/regions.sql
inflating: releases/next/changes/target_hrrest/hrrest/comments/countries.sql
inflating: releases/next/changes/target_hrrest/hrrest/comments/employees.sql
inflating: releases/next/changes/target_hrrest/hrrest/comments/job_history.sql
inflating: releases/next/changes/target_hrrest/hrrest/comments/locations.sql
inflating: releases/next/changes/target_hrrest/hrrest/tables/jobs.sql
inflating: releases/next/changes/target_hrrest/hrrest/tables/departments.sql
inflating: releases/next/changes/target_hrrest/hrrest/tables/regions.sql
inflating: releases/next/changes/target_hrrest/hrrest/tables/countries.sql
inflating: releases/next/changes/target_hrrest/hrrest/tables/employees.sql
inflating: releases/next/changes/target_hrrest/hrrest/tables/job_history.sql
inflating: releases/next/changes/target_hrrest/hrrest/tables/locations.sql
inflating: releases/next/changes/target_hrrest/hrrest/ref_constraints/dept_loc_fk.sql
inflating: releases/next/changes/target_hrrest/hrrest/ref_constraints/emp_job_fk.sql
inflating: releases/next/changes/target_hrrest/hrrest/ref_constraints/jhist_job_fk.sql
inflating: releases/next/changes/target_hrrest/hrrest/ref_constraints/jhist_dept_fk.sql
inflating: releases/next/changes/target_hrrest/hrrest/ref_constraints/loc_c_id_fk.sql
inflating: releases/next/changes/target_hrrest/hrrest/ref_constraints/countr_reg_fk.sql
inflating: releases/next/changes/target_hrrest/hrrest/ref_constraints/emp_dept_fk.sql
inflating: releases/next/changes/target_hrrest/hrrest/ref_constraints/jhist_emp_fk.sql
inflating: releases/next/changes/target_hrrest/hrrest/ref_constraints/dept_mgr_fk.sql
inflating: releases/next/changes/target_hrrest/hrrest/ref_constraints/emp_manager_fk.sql
inflating: releases/next/changes/target_hrrest/hrrest/triggers/update_job_history.sql
inflating: releases/next/changes/target_hrrest/hrrest/triggers/secure_employees.sql
inflating: releases/next/changes/target_hrrest/hrrest/sequences/departments_seq.sql
inflating: releases/next/changes/target_hrrest/hrrest/sequences/locations_seq.sql
inflating: releases/next/changes/target_hrrest/hrrest/sequences/employees_seq.sql
inflating: releases/next/changes/target_hrrest/hrrest/views/emp_details_view.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/emp_name_ix.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/jhist_department_ix.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/emp_manager_ix.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/emp_department_ix.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/loc_country_ix.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/jhist_employee_ix.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/jhist_job_ix.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/loc_city_ix.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/dept_location_ix.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/emp_emp_id_pk.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/emp_job_ix.sql
inflating: releases/next/changes/target_hrrest/hrrest/indexes/loc_state_province_ix.sql
inflating: releases/next/changes/target_hrrest/stage.changelog.sql
inflating: releases/next/release.changelog.sql
inflating: releases/main.changelog.sql
inflating: releases/ords/hrrest/ords.sql
inflating: releases/ords/ords.changelog.sql
inflating: utils/recompile.sql
inflating: utils/prechecks.sql
inflating: env/defaultProperties.sql
inflating: env/undefineDefaultProperties.sql
inflating: install.sql
-- open VS Code
SQL> !code .In VS Code, if I navigate to the ‘next’ release, I’ll see the upgrade scripts our ‘installer’ artifact would run if we were to actually apply changes to the Source schema. At the end, it would match our Target schema.
Under my tables folder, I can see several table SQL files, and if I inspect employees.sql, I see:
/* This script might contain a rename case instead of a drop/add, please review and edit this file to avoid data loss */
alter table hr123.employees add (
hobbies json
)
/
/* Uncomment drop statement after ensuring it is performing the correct actions
ALTER TABLE "HR123"."EMPLOYEES" DROP ("COMMISSION_PCT")
/
*/
alter table hr123.employees modify constraint emp_last_name_nn
disable novalidate
/
alter table hr123.employees modify constraint emp_email_nn
disable novalidate
/
alter table hr123.employees modify constraint emp_hire_date_nn
disable novalidate
/
alter table hr123.employees modify constraint emp_job_nn
disable novalidate
/
alter table hr123.employees add constraint emp_emp_id_pk primary key ( employee_id ) disable
/
alter table hr123.employees add constraint emp_email_uk unique ( email ) disable
/
alter table hr123.employees add constraint emp_salary_min check ( salary > 0 ) disable
/A few notes:
- In my target or ‘v2’ schema, I had an extra column ‘HOBBIES,’ and a missing (from v1/destination) column ‘COMMISSION_PCT.’
- We don’t assume you are cool with DROPs, so you have to ‘opt-in’ by uncommenting out that DDL if you want to actually remove the column and risk data loss.
Let’s look at a side by side compare of our DEPARTMENTS table, and then the resulting diff/upgrade script generated.
And our departments.sql file –
What about the missing comments?
Change from the tables directory to the comments directory, you’ll see another departments.sql file there, with the aforementioned column and table level comments.
What about a DIFF summary report?
We didn’t have one of those in the old SQL Developer DIFF wizard, and we don’t have one here, YET. We’re working on that!
However, you don’t need to wait on us. Any decent AI assistant could traverse the upgrade scripts and summarize the differences for you, like Cline did here via gpt-5.1-codex llm running in Windsurf.
How do we ‘AI’ this Diff?
Simply! Just ask your agent to use the ‘run-sqlcl’ tool to run the SQLcl DIFF command for you. It already has access to a list of connections. You probably will want to use the ‘asynch’ tool option as these operations tend to take awhile, especially for larger schemas.
More on run-sqlcl MCP Tool
More on asynch support for MCP Tools
4 Comments
Hi Jeff,
I’m a DBA and it would great to have the possibility to provide the schema name to be compared. I do not have the schema password, but I connect as sys/system.
If you’re sys you have whatever you need, right? If you don’t know the password, you can simply change it. We’re not in production, are we?
Hi Jeff,
thanks for the clear example, but I’d dare to say that in my experience it’s more common having to compare subset of all the objects in a schema instead of the whole schema.
In SQL Developer Classic we could use the Cart (which is very useful to easily separate objects by domain/customer/tenant when working in a multi-tenant scenario), while I fail to see the advantage of this approach when I am interested only in a fixed and limited number of objects, knowing that the other schema objects may have also ongoing changes which I’d like to ignore. So I am wondering, what could be a replacement to the shopping cart for SQLcl/SQLDev for VSCode?
Thanks,
PM
First we walk, then we run.
Filtering and gui via sqldev are all on roadmap.