SQLcl Projects is a feature in Oracle’s SQLcl tool that helps manage and automate database changes and deployments for Oracle Database and APEX applications. It provides a structured approach to version control and simplifies the process of moving database objects and APEX apps through development, testing, and production environments.

I learned everything by starting with Rafal’s article here, reading this, watching this video, and then reading the quick start examples. There is a lot of information, so I’ve summarized it in this piece. You can also ask SQLcl questions on the SQLcl forums.

Get SQLcl

First, you need SQLcl. You can download it here & follow this great installation guide to get everything in order. Once you are connected to your Database, be sure to save your connection as this gets baked into your project initialization.

Note: I had more success with SQLcl 24.3.2 than SQLcl 24.4.1. So unless there is a higher version by now, I’d recommend 24.3.2.

Git Approach

You need Git to use SQLcl Projects. No other version control software is supported. Then, you need to decide on a Branching Strategy.

Branching Strategy

Here are some differences to help you decide on an approach before you use SQLcl Project.

Bear in mind that a branching strategy should suit the team’s current process, project nature, and budget. 

In the remainder of this article, I interchange the branch names of feature-x and sprint-x for whatever best suits your needs.

Read this article by Dan McGhan to help you decide which is the best for you.

Repository

For a Git repository:

• Don’t have a repository? Just create one in GitHub and clone it locally. I would personally always clone rather than create locally and then add remotes.
• Already have a repository but want to start using SQLcl Projects? Use this approach

SQLcl Projects Command Guide

This table is listed in a logical flow of how the project commands are issued. They also indicate from which environment and branch you should run the command from – including if you should ideally git commit after running that command.

In Summary:

Your Repository Admins…

• Perform the Initial Setup (init) from main
• Create Releases (release) from main
• Create Artifacts (gen-artifact) from main
  • It’s fine for developers to do this for testing purposes, too. It all depends on the process and how much automation there is
• Can protect the main branch from the Team in GitHub

Your Team…

• Performs all SQLcl Project commands exclusively in feature branches, committing after each command
• Makes a Pull Request for their feature when complete (or merges the feature branch into main, depending if main is a protected branch or not)

Everyone…

• Must be on the same SQLcl version as displayed in the version tag within the file .dbtools\project.config.json, which was created by project init

Developing

This section covers how to start and finish your work, including how to create and edit different types of objects and which tools you are permitted to use.

Starting some work and need a new Feature Branch?

Switch to Main and check out a new Feature Branch:

!git checkout -b feature-1

The -b switch creates a new branch. If the branch already exists, it will error with a Fatal: A branch named ‘feature-1’ already exists message.

Note that Bang commands (i.e. exclamation marks), such as the above, are used as shortcuts for the host command i.e !<cmd>. 

Already have a Branch for this work?

Switch to that branch using this:

!git checkout feature-1

Need to create or modify something?

Compiling directly on the DB? Or compiling from Files?

Both are supported, although by compiling directly on the DB, you do risk code loss (psssst – don’t do it).

If you are working from file, do not modify any SXML/JSON markup that SQLcl Projects creates. I would recommend SQL Developer for VSCode for compiling from the file.

Need to drop something?

Project stage should generate drops, but you have to remove the objects from your feature branch for this to work

1. Drop the object from the DB
2. Remove the objects from your feature branch
3. Remember to git commit after your stage command.
4. Project stage should now generate drops (be aware that drop scripts currently [24.4.1] contain the schema name prefix even though export.setTransform.emitSchema is set to false)

Need to reorder something?

Change the order of files created using stage add-custom -file-name. Not recommended to change the order of anything else.

Accidentally exported work that’s not your own?

Did you commit before running the project export?

Yes?

• Discard your changes and re-export objects individually using project export -o object_name

No?

• It’s tricky. Consider running !git status to view the changes and discard anything you don’t recognize. This will have to be a case-by-case approach

I did something terribly wrong. Please tell me that Git has a magic time machine!

• Try here

Finished your work?

Follow these steps (or just perform them in VSCode).

• Run the export -o command in your feature branch to ensure you have captured your work

project export -o object_name

Alternatively, run the export command to capture everything.

project export

• Run the Stage command in your feature branch

project stage

• Commit to the Feature Branch

!git add --all
 !git status
 !git commit -m "feature-1 summary text"

• All done?

Main is not 🔓 protected? Merge to main

!git checkout main
  !git merge "feature-1"

Main is 🔒 protected? Create a Pull Request (PR). Note: This is a GitHub feature rather than a Git feature. There are two approaches:

• GitHub UI: Navigate to the repo > Pull Requests > New Pull Request. Select base: main and compare: feature. Add a title/description and click Create Pull Request
• GitHub CLI:

gh pr create --base main --head feature-1 --title "New feature" --body "Description"

PR accepted?

The admin will then merge (unless auto-merge is enabled).

PR rejected?

Revise and resubmit the same PR (usually).

Tips

Init

I have already initialized my project. However, I need to add a new schema. 

Two options:

• Option 1: Add all schemas like this

PROJECT CONFIG set -name schemas -value HR,HR2 -type ARRAY

Then, confirm the setting using PROJECT CONFIG -name schemas -list

SQL> PROJECT CONFIG -name schemas -list
  +=============================+
  | SETTING NAME | VALUE        |
  +=============================+
  | schemas      | ["HR","HR2"] |
  +-----------------------------+

• Option 2: Just edit .dbtools\project.config.json and make the changes manually

Accidentally ran project init on root / and you cannot delete it?

SQL> project config delete -n "MY_PROJECT"
The config parameter you supplied MY_PROJECT can not be found please verify your input

Just delete the hidden .dbtools folder.

I initialized my project, but I want to understand what the .dbtools\project.config.json settings do. 

The documentation is light in this area, but you can run this command:

project config -list -verbose

Which gives you this:

+===================================================================================+
  | SETTING NAME         | VALUE         | DEFAULT VALUE | DESCRIPTION                |
  +===================================================================================+
  | project              | My App        |               | the project name           |
  +-----------------------------------------------------------------------------------+
  | sqlcl.connectionName | MY_CONNECTION |               | Connection name            |
  +-----------------------------------------------------------------------------------+
  | sqlcl.version        | 24.4.1.0      |               | SQLcl version              |
  +-----------------------------------------------------------------------------------+
  | schemas              | ["MY_SCHEMA"] |               | Array of schemas to includ |
  |                      |               |               | e in this project          |
  +-----------------------------------------------------------------------------------+
  + more rows

The final column description gives you the description of the setting.

Exporting

Which user should I use to export with several project schemas assigned?

• Option 1: Log in with each schema and export individually
• Option 2: Grant the SELECT_CATALOG_ROLE role to the “main” application schema that you connect as. That will allow you to export objects from the other schemas without having to reconnect as those schemas.
• Option 3: “Admin Style” approach – example below:

-- Create Developer accounts
 CREATE USER dev1 IDENTIFIED BY dev1_password;
 CREATE USER dev2 IDENTIFIED BY dev2_password;
 GRANT CREATE SESSION TO dev1, dev2;

 -- Alter all schemas to allow connect
 ALTER USER apex_workspace_parsing_schema GRANT CONNECT THROUGH dev1;
 ALTER USER apex_workspace_parsing_schema GRANT CONNECT THROUGH dev2;
 ALTER USER another_schema GRANT CONNECT THROUGH dev1;
 ALTER USER another_schema GRANT CONNECT THROUGH dev2;

 -- Connecting
 CONNECT dev1[apex_workspace_parsing_schema]/dev1_password@database

 -- Developers should prefix objects with the schema name when creating/modifying them e.g.
 CREATE TABLE apex_workspace_parsing_schema.employees (
     employee_id NUMBER,
     employee_name VARCHAR2(100)
 );

 -- Export as dev1[apex_workspace_parsing_schema]
 project export

Need to add exclusion filters for the export command?

See here. This can be useful to exclude all Development objects beginning with TEST_ for example.

Need to export only specific APEX applications?

Use:

• project export -o apex.xyz where xyz is the application ID (source)

Or

• Add this entry to the filters file application_id in (xyz) where xyz is the application ID (source)

Need to export REST Modules, Workspace Files, or the Workspace itself?

See here.

How to isolate feature-based APEX changes?

Use APEX Working Copies (source).

My Indexes seem to have duplicated, once in src/database/<schema>/index and again in src/database/<schema>/table. How do I resolve this?

You need to either upgrade SQLcl to the fixed release or manually delete the duplicated indexes.

Exporting re-formats my lovingly crafted code. How can I stop this from happening?

Run the following:

project config set -name export.format.enable -value false -type boolean

Process completed successfully

Check it:

project config -list -name export.format.enable

+==============================+
 | SETTING NAME         | VALUE |
 +==============================+
 | export.format.enable | false |
 +------------------------------+

If you want to set it back to formatting, just type project config set -name export.format.enable -value true -type boolean.

Creating a Release

Need to delete a release? See here.

1. Manually delete the release folder and artifact
2. Delete any tagged release by listing them !git tag and then deleting them e.g. !git tag -d release-1.1.0
3. Remove the release lines from /dist/releases/main.changelog.xml file e.g. remove this line for release 1.1. <include file=”1.1.0/release.changelog.xml” relativeToChangelogFile=”true”/> (source)
4. Update the lastReleaseVersion property in .dbtools\project.config.json

Generating an Artifact

Want to change the artifact from Liquibase XML to SQL?

Use this.

• Set the format:

project config set -name stage.generatedFormat -value sql -type string

Process completed successfully

• Verify it:

project config -list -name stage.generatedFormat

+===============================+
 | SETTING NAME          | VALUE |
 +===============================+
 | stage.generatedFormat | sql   |
 +-------------------------------+

• Rerun the stage command:

project stage

• The artifacts will now be in SQL format. If you want to change the setting back to XML, then run this

project config set -name stage.generatedFormat -value liquibase -type string

Deploying

Deploying the most recent artifact will take the environment up to the latest version without needing to incrementally run each missed release, a bit like how APEX Patches work.

Need to run some PL/SQL pre-release or post-release? 

See here. However, pre/post scripts should be very generic, not related to any specific objects.

Something about APEX differs in the Target?

• Exporting from one APEX ID but deploying it to another ID? See here.
• Deploying into a different schema? – Use the target_schema method (source)
• Deploying into a different workspace? Stay tuned.

Was artifact deployment unsuccessful?

There are two viable options:

• Flashback Database

Or

• Roll-forward. This is a strategy used to address issues encountered during or after a deployment by applying additional changes to fix the problem, rather than reverting to a previous stable version

Can you deploy to a different server? 

If you can create an SQLcl connection to it, then yes.

In a CICD pipeline, how do you catch the error? 

By reading the terminal output. Although a feature enhancement has been logged.

Working with Files

Need to export some lookup data from a table in Dev to a custom-file as inserts?

Use SQLcl set sqlformat insert.

Created a custom file but…you don’t need it anymore? Or want to rename it?

• Delete the file or edit the filename in the _custom directory of your change
• Edit the stage.changelog.xml and edit or remove the pointer to the file

Switchover

Switching over to SQLcl Projects, but already using Liquibase? 

If yes, then prior to deploying an artifact to a non-development environment, you’ll need to run the changelog-sync command on the target DB (read more about this here).

Is the APEX Application Lifecycle Paper v.3 still valid?

It doesn’t include SQLcl Projects. However, Oracle is working on updating the Lifecycle paper “in the coming months”.

Upgrading SQLcl

A new version of SQLcl is out. When can the team upgrade SQLcl? 

At the end of a sprint. This involves several steps to get the git repository ready. Read how to perform these steps here.

I have file issues after upgrading SQLcl e.g. duplicates, naming issues, etc. What do I do?

Ensure you have followed these steps here (especially step 3)

Other

Can SQLcl Projects be used on apex.oracle.com? 

No (source).

Is it possible to include SQLcl Projects into APEX Supporting Object scripts? 

“Typically No” (source).

How can we manage several developers working in a shared environment who are touching the same objects? 

Stay tuned.

Practices

Starting a new DB Application

Init on main without any exported objects

1. Branch off of main to create a sprint-1 branch, export/stage there, then merge back to main.
2. Future branches can be labeled sprint-2.

Starting SQLcl Projects with an existing DB Application

Init on main without any exported objects

1. Branch off of main to create a sprint-0 branch, export/stage there, then merge back to main.
2. Future branches can be labeled sprint-1.
3. Prior to deploying an artifact to a non-dev environment, run the changelog-sync command on the target DB (read more about this here).

Backing up an existing DB Application

First, establish a code freeze and then only one developer should complete the following steps:

1. Init on main without any exported objects
2. Branch off of main to create a sprint-0 branch, export/stage there, then merge back to main.
3. Future branches can be labeled sprint-1.

Example Files & Folders

This diagram explains which commands produce which files:

🔑 Key
============================
🟠 project init
🟡 project stage add-custom
⚪ project gen-artifact
🔵 project export
🟢 project stage
🟣 project release

🟠.
🟠├── .dbtools
🟠│   ├── filters
🟠│   │   └── project.filters
🟠│   ├── project.config.json
🟠│   └── project.sqlformat.xml
🟠├── README.md
⚪├── artifact
⚪│   └── demo_project-1.0.0.zip
🟠└── src                                                                                                                                                                                                                                        
🟠│   ├── README.md                                                                                                                                                                                                                              
🟠│   └── database                                                                                                                                                                                                                                
🟠│       ├── README.md                                                                                                                                                                                                                          
🟠│       └── demo           
🔵│           ├── apex_apps                                                                                                                                                                                                                       
🔵│           │   └── f110   
🔵│           │       ├── readable        
🔵│           │       └── f110.sql                                                                                                                                                                                                                
🔵│           ├── indexes                                                                                                                                                                                                                        
🔵│           │   └── idx_emp_deptno.sql                                                                                                                                                                                                          
🔵│           ├── ref_constraints                                                                                                                                                                                                                
🔵│           │   └── fk_deptno.sql                                                                                                                                                                                                              
🔵│           └── tables                                                                                                                                                                                                                          
🔵│               ├── dept.sql                                                                                                                                                                                                                    
🔵│               └── emp.sql
🟠└── dist
🟠    ├── README.md
🟠    ├── install.sql
🟢    └── releases
🟣        ├── 1.0.0
🟣        │   ├── changes
🟣        │   │   ├── main
🟣        │   │   │   ├── code
🟣        │   │   │   │   └── _custom
🟣        │   │   │   └── stage.changelog.xml
🟣        │   │   ├── ticket-1
🟣        │   │   │   ├── code
🟡        │   │   │   │   └── _custom
🟡        │   │   │   │       ├── dept-data.sql
🟡        │   │   │   │       └── emp-data.sql
🟣        │   │   │   ├── demo
🟣        │   │   │   │   ├── index
🟣        │   │   │   │   │   └── idx_emp_deptno.sql
🟣        │   │   │   │   ├── ref_constraint
🟣        │   │   │   │   │   └── fk_deptno.sql
🟣        │   │   │   │   └── table
🟣        │   │   │   │       ├── dept.sql
🟣        │   │   │   │       └── emp.sql
🟣        │   │   │   └── stage.changelog.xml
🟣        │   │   └── ticket-2
🟣        │   │       ├── code
🟣        │   │       │   └── _custom
🟣        │   │       ├── demo
🟣        │   │       │   └── table
🟣        │   │       │       └── emp.sql
🟣        │   │       └── stage.changelog.xml
🟣        │   ├── code
🟣        │   │   ├── code.changelog.xml
🟣        │   │   └── demo
🟣        │   │       └── function
🟣        │   │           └── get_display_name.sql
🟣        │   └── release.changelog.xml
🟣        ├── main.changelog.xml
🟢        └── next
🟢           └── release.changelog.xml

Project Filters

The filters file can be found here .dbtools\filters\project.filters. It can be edited to include or exclude certain DB Objects, including APEX Applications.

Here is what the default file looks like after being created by project init:

/* Comma separated list of predicates to be fine-tuned for individual project. 
   Run 
       project export -debug 
   to double check the dictionary queries 
   with internal and custom filters applied. 
*/


-- Uncomment the line below if don't want to export grants:
-- export_type not in ('ALL_TAB_PRIVS','USER_SYS_PRIVS'),
/** Reference list of export_types from internal.fixed.filters 
                'ALL_OBJECTS',
                'APEX_APPLICATION', 
                'ALL_COL_COMMENTS',
                'ALL_TAB_COMMENTS',
                'ALL_TAB_PRIVS',
                --'USER_SYS_PRIVS',
                'ALL_DEPENDENCIES',
                'ALL_MVIEW_LOGS',
                'USER', 
                'ORDS_SCHEMA'
*/    

-- Liquibase Tables
object_type != 'TABLE' or object_name not in ('DATABASECHANGELOG',
                                              'DATABASECHANGELOGLOCK',
                                              'DATABASECHANGELOG_ACTIONS'
                                              ),
not (object_type = 'VIEW'    and object_name ='DATABASECHANGELOG_DETAILS'),
not (object_type = 'TRIGGER' and object_name ='DATABASECHANGELOG_ACTIONS_TRG'),


-- DM generated tables
--not (object_type = 'TABLE' and object_name like 'DM$%' ),
--not (object_type = 'VIEW' and object_name like 'DM$V%' ),
object_name not like 'DM$%',      -- covers tables, views, indexes 

export_type not in ('USER','ORDS_SCHEMA'),

-- Export APEX application only:
-- export_type = 'APEX_APPLICATION',

-- Exclude certain database object types:
-- object_type not in ('CONTEXT', 'SCHEDULE'),   -- trailing comma is optional

Conclusion

And that’s about it. If you need any help, start by reading Rafal’s article and then read this, watch this video, and read the quick-start examples. If you need to ask a question to the Community/SQLcl Team, use the SQLcl Forum. You can also ask for help on the apex.world SQLcl Slack channel. And you can always follow Neil, Jeff, Rafal, Dan, Martin, Barry, Hamza, Plamen, or me on X.

Credits

Check those great blog posts related to SQlcl Project:

1. Part 5: SQLcl Project (It will forever change your Database & APEX deployments) by Rafal Grzegorczyk
2. Oracle’s SQLcl Project – the only CI/CD tool for APEX you will ever need by Rafal Grzegorczyk
3. An Overview of the Oracle SQLcl Projects Development Process by Dan McGhan
4. Admin vs. App User Installation: Choosing the Right Path for Secure, Efficient Deployments by Dan McGhan
5. Migrate your existing Oracle Database/APEX application to SQLcl Projects by Hamza Eraoui
6. APEX: Quick Tip when using SQLcl 24.3 Projects for Database Application CI/CD by Sydney Nurse
7. Oracle APEX Office Hours Video → SQLcl Projects: CI/CD Made Easy for APEX
8. Upgrading SQLcl When Using SQLcl Projects by Dan McGhan
9. Feature Branches vs. End-of-Release Branches: Which Approach Works Best? by Dan McGhan