top of page

Migrating to SQLcl project (and no-nonsense scripts to do it for you!)

  • Writer: Clark Pearson
    Clark Pearson
  • 3 days ago
  • 7 min read

Why?


Just as git has established itself as the de facto standard for version control, I really think that SQLcl project will in time become the de facto standard for Oracle devs – it has for Oracle Corporation devs, so embrace it, I feel this is just the beginning!


Use Case


You already have a db application, and you want to migrate your codebase to align with how the project command works, and integrates with git and Liquibase. This is a good demo/overview, you can skip to 9m 30s in.


Assumptions / pre-requisites


  • SQLcl 26.1.2 or higher install, released early-mid May 2026, which fixes a bug I call out later (although this may not be a worry in your environment)


Some level of familiarity with:-


  • SQLcl (or at least, sqlplus and a sql command line)

  • Linux shell

  • git

  • Liquibase is not a pre-req, just know that project works with it, and it documents and audits your database deployments via a few databasechangelog* tables/views.


TL;DR

Jump to the no-nonsense quick-start! Then take a look at the scripts you are or have run – they just put the steps outlined below into a couple of .sql scripts to exec some SQLcl commands, or shell out to run linux/git commands.


Considerations before commencing your baseline


You need to decide how best to integrate with your CI/CD tools, with security concerns also a consideration. Do you:


  1. Connect as the schema owner who has all necessary privileges?

    • This exposes security vulnerabilities, since a hacker with the owner credentials can wipe out the schema.

    • It also means the Liquibase & SQLcl-project databasechange* objects are placed into this schema, which you may consider unwanted pollution in your application schema.


– OR –


  1. Connect as, for example, the Apex admin user, or other user with the app-owner-schema privileges granted, then don't grant object-creation privileges to the app owner. Schema-level grants were introduced in Oracle 23ai: see here. So you can grant another user create/etc privs just on the app owner-schema.

    • This means the schema-owner can't create – or more crucially – drop their own tables, etc,

    • ..and that the databasechange* objects don't pollute your app schema.

    • TBC! What happens if we have two separate apps, both managed by, say, admin, and we have both sets of Liquibase changesets going into the same databasechange* tables?!


Steps


You need to make sure your prod and lower environments are all aligned before you start on a real migration, since the baseline will be exported from dev to be Peer Reviewed, then pushed up the environments to live. But while you're just seeing what it's all about, jump in – you can undo it all really quickly after your experiment.


Note in the steps below that you can:


  • add -v or -verbose to every project command to get more feedback about what it is doing;

  • learn the project shortened keyword syntax for each sub-command in tandem, see my SQLcl `project` cheat-sheet.


This assumes you are in the SQLcl cmd line, in an appropriate directory to create/clone a new repo into.


  1. Create a new git repo locally, and cd to the new repo/directory (you can clone a fresh one, or init locally and push remotely later). The project command creates/expects a certain filepath structure, so it is unlikely to align with an existing repo:

    • !git [ clone | init ] <your-new-migration-repo>

    • cd <your-new-migration-repo> (no need to !shriek this, it's a SQLcl native cmd) 


  1. Now start your SQLcl project: this tells the project what schema/s it will be using in the db connection, and sets up the folder structure for exported db objects.

    • project init -name <your-proj-name> -schemas <schema_to_migrate>

    • Potentially: <your-proj-name> = <your-new-migration-repo>


  1. Configure the project before you do anything else; configuring after the event may generate unwanted export differences.

    • Amend the ./.dbtools/project.sqlformat.xml as per your company standards. I haven't found any online docco for this; whilst many are intuitive, some are not, and if you get just one that project doesn't like, it ignores the whole thing:

    • spaceAroundBrackets: if you don't want these, remove the line is best I can suggest, you still get spaces around some brackets, but hopefully not the ones you actually care about. Maybe there are other options I haven't found.

    • .lower or .UPPER on certain options, eg. kwCase: observe case! Lowercase 'lower', uppercase 'UPPER', which is a different model to .Before & .After with an initial capital.

    • maxCharLineSize might be one you to want to increase from the default, 128.


A for example with project.sqlformat.xml:


Black screen showing code text: `<kwCase>oracle.dbtools.app.Format.Case.UPPER</kwCase>` in green and white, with a technical feel

  • Don't forget any .gitignore files, eg. login.sql, *.code-workspace maybe? – but note that project itself appends its own ignorable file masks.


If your environments use different schema names such as dev_app, prod_app, you will need to amend ./.dbtools/project.config.json file and change it to not prepend schema names to objects:


Neon green terminal text on black reads export → setTransform → emitSchema : false, with a cursor-like line at the end

Note that prior to SQLcl 26.1.2, columns using DEFAULT <sequence>.NEXTVAL do not honour this setting: the sequence has the schema prepended.


  1. Commit the project files and then export the schema. (If you get a warning about your sqlcl.connectionName not being set, you can safely ignore it.)


  • !git add .

  • !git commit -m 'Add sqlcl project files'

  • project export

    • This may include some stuff you weren't expecting: connected as admin, I got some ORDS scripts for admin that I didn't ask for.

    • Caveat! I have already said to make sure dev matches live! If your dev workspace has copies of the application for dev's experiments, you will get an export of all the experimantal apps with this parsing schema, so check the apex export folder and remove the false positives, then also remove them from the changelog.xml too.


  1. Now commit that into a new git branch:


  • !git checkout -b sqlcl-project-migration

  • !git add .

  • !git commit -m 'Baseline export'


  1. Optionally, if required: that is, if you are not connected as the app-owner, and you don't have schema names prepended to objects: Add a custom file to set the target schema upon deployment.

    • project stage add-custom -file-name set_schema.sql

The custom file is created with only the Liquibase change stuff, ready for you to put your commands into it:

  • !echo 'alter session ttt current_session = <schema_to_migrate>;' >> ./dist/releases/next/changes/sqlcl-project-migration/_custom/set_schema.sql

And fix ttt to set (set causes the above command to operate incorrectly from SQLcl, for some reason):

  • !sed -i '' -e 's/ttt/set/' ./dist/releases/next/changes/sqlcl-project-migration/_custom/set_schema.sql


  1. Use stage to create the dist files, which is using Liquibase under the hood, then add them to git.


  • project stage If you get an "Invalid id" error on ADB here, try either:

    • switch your connection to the _LOW version; or

    • alter session disable parallel dml;

    • The proj-mig-init script from my sqlutil repo runs the latter step regardless to be on the safe side. 


  1. We all have lookup tables right? – so do something similar to project stage in point 6, and paste, or cat another file, into the skeleton Liquibase changeset sql file (you cannot import another file directly). Eg.


  • project stage add-custom -file-name pop_lovs.sql

  • !cat path/to/original/pop_lovs/file >> ./dist/releases/next/changes/sqlcl-project-migration/_custom/pop_lovs.sql

    Note SQLcl's

    • set sqlformat insert might be of use here, which formats the result of a select * from <table> as insert statements so you can copy/paste or spool into the skeleton. Enter

    • help set sqlformat

      for a whole host more options.


  1. As this is a new codebase migration baseline, we need to tweak the Liquibase (lb) install script which thinks we're building from scratch. Amend the file ./dist/install.sql and commit to git:

  • lb update near the bottom, at the beginning of the line, becomes

  • lb changelog-sync (just change 'update' to 'changelog-sync', leave the rest of the line as is)


  1. Commit these changes to git, then we're ready to go through Peer Review and ultimately merge everything to main:

    • !git add .

    • !git commit -m 'Add stage files'


Do the PR etc!

The sqlutil ..-init script from the TL;DR section stops here. The ..-rel one now continues:

  • !git checkout main

  • !git merge sqlcl-project-migration


  1. Optional: check for potential problems:

  • SQL> project verify -verbose

    If you hit a SQLcl bug and went to a lower version, this may report a problem with the project.config.json parameters. Just delete any it doesn't recognise, as they have probably been added by the higher version.


  1. Now make the baseline project release, add to git and commit:

  • project release -version 1.0

    !git add .

    !git commit -m 'Release 1.0'


  1. Now we're ready to create the final deployment zip bundle (artifact) for Liquibase:

  • project gen-artifact

    Which generates a file in ./artifact – you may want to browse the zip contents:

  • <your-proj-name>-1.0.zip


  1. In SQLcl, connect to the db you want to install this baseline to – this could be the current connection as it will only generate the Liquibase databasechange* tables and not deploy anything else – this is what we told it to do in point 9, when we changed update to changelog-sync:

  • project deploy -file ./artifact/<your-proj-name>-1.0.zip


  1. For me, after deploying from the admin schema, something in this setup has lowercased and double-quoted "admin" when Liquibase tries to create the view: "admin"."DATABASECHANGELOG_DETAILS" ..but I'm not sure if it's a Liquibase or SQLcl bug (this view, I believe is added by Oracle rather than it being a Liquibase object), or the project.config.json settings. You might want to manually crank the code to get that view into the admin schema. You can find the view-code in the error log that is generated.


You may now want to have a poke around the databasechange* tables, but otherwise, you're done! Your database is now baselined to version 1.0, and audited as to who baselined it.


Undoing all that

Assuming you are still in <your-new-migration-repo> within SQLcl:


  1. drop view databasechangelog_details;

  2. drop table databasechangeloglock purge; (which may need appropriate schema-prefix)

  3. drop table databasechangelog purge; [ditto]

  4. drop table databasechangelog_actions; [ditto]

  5. cd ..

  6. !rm -fdr <your-new-migration-repo>


And that's it, you're back where you started, no git repo, no Liquibase audit tables, or the additional view added by SQLcl-project.


The no-nonsense quick-start!


This demonstration creates a local git repo. In practice for a real migration, you would probably create a bare one in github/wherever, clone that locally, and populate that before pushing it back. I leave it to the reader to modify the scripts appropriately for such real-world usage.


git clone my sqlcl-project-migrate-util repo; here:


git@github.com:RADAPEX/sqlcl-project-migrate-util.git


From the directory into which you cloned sqlcl-project-migrate-util (ie. its parent directory), start SQLcl – or cd to that directory if already in SQLcl; now:


  1. @sqlcl-project-migrate-util/proj-mig-init <your-new-migration-repo> <schema_to_migrate>

    • Your migration is ready for Peer Review! Check out the files in <your-new-migration-repo>.

  2. Now in SQLcl, cd <your-new-migration-repo>, for git (if you've just done part 1, you're already in the right place):

    • @../sqlcl-project-migrate-util/proj-mig-rel

Your migration artifact (Liquibase zip bundle) is ready to deploy! We skipped Peer Review for this exercise. PR comes after part 1, before part 2.


  1. On the target db, which, for this exercise, can be the same dev db you did 1-2 on, from SQLcl again:

    • project deploy -file path/to/your/artifact.zip


And check out the table `databasechangelog` – this may be in the same schema you exported, or an admin-type schema: at this stage, you really do need to read the section on Considerations before commencing your baseline, I'm afraid.


You're done! Unless you want to follow the Undoing all that steps. Oh, ok! Or run this from within <your-new-migration-repo>:


  1. @../sqlcl-project-migrate-util/proj-mig-undo <your-new-migration-repo> <dbchangelog_schema>


That removes <your-new-migration-repo>, the databasechangelog* objects, and leaves you in the parent directory.

 
 
bottom of page