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.
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.
You need Git to use SQLcl Projects. No other version control software is supported. Then, you need to decide on a Branching Strategy.
Here are some differences to help you decide on an approach before you use SQLcl Project.
| Feature-based | Sprint/release-based | |
| Context | An issue/ticket | Any change in that sprint |
| Code Reviews | Frequent upon PR | Infrequent |
| Deployments to Test | Frequent | Infrequent |
| Chance of Release Error | Lower | Higher |
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.
For a Git repository:
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.
| Command | Purpose | DB | Git Branch | Commit & Push After | Example |
| init | Creates folder structure | Development | main | ✔️ | project init -name PROJECT_NAME -schemas HR |
| export | Exports DB objects & APEX apps from schema(s) | Development | feature | ✔️ | project export |
| export – o object_name | Exports DB objects of that name | Development | feature | ✔️ | project export -o EMP |
| stage | Creates releases/next | Development | feature | ✔️ | project stage |
| stage add-custom | Adds file to releases/next e.g. for DML | Development | feature | ✔️ | project stage add-custom -file-name dml.sql |
| verify | Checks the releases/next | Development | feature | ❌ | project verify -verbose |
| release | Creates releases/x.x & empties releases/next | Development | main | ✔️ | project release -version 1.0 -verbose |
| gen-artifact | Creates artifact/release.zip | Development | main | ❌ | project gen-artifact -version 1.0 |
| deploy | Deploys release using Liquibase | Test / Pre-Prod / Prod | main (provided you created the artifact) | ❌ | project deploy -file artifact/PROJECT_NAME-1.0.zip -verbose |
In Summary:
Your Repository Admins…
Your Team…
Everyone…
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.
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
| Object | How to create it | How to export it to file | How to modify it |
| Soft i.e CREATE OR REPLACE | On the DB | export -o object_name | On DB 😦 or Compile from File ✔️ |
| Hard i.e CREATE INDEX | On the DB | export -o object_name | On the DB |
| Script i.e INSERT INTO | stage add-custom -file-name dml.sql | Not Applicable | Edit the file created e.g. dml.sql |
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.
Project stage should generate drops, but you have to remove the objects from your feature branch for this to work
Change the order of files created using stage add-custom -file-name. Not recommended to change the order of anything else.
Did you commit before running the project export?
Yes?
No?
I did something terribly wrong. Please tell me that Git has a magic time machine!
Follow these steps (or just perform them in VSCode).
project export -o object_name
Alternatively, run the export command to capture everything.
project export
project stage
!git add --all !git status !git commit -m "feature-1 summary text"
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:
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).
I have already initialized my project. However, I need to add a new schema.
Two options:
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"] | +-----------------------------+
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.
Which user should I use to export with several project schemas assigned?
-- 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:
Or
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.
Need to delete a release? See here.
Want to change the artifact from Liquibase XML to SQL?
Use this.
project config set -name stage.generatedFormat -value sql -type string
Process completed successfully
project config -list -name stage.generatedFormat
+===============================+ | SETTING NAME | VALUE | +===============================+ | stage.generatedFormat | sql | +-------------------------------+
project stage
project config set -name stage.generatedFormat -value liquibase -type string
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?
Was artifact deployment unsuccessful?
There are two viable options:
Or
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.
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?
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”.
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)
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?
Init on main without any exported objects
Init on main without any exported objects
First, establish a code freeze and then only one developer should complete the following steps:
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
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
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.
Check those great blog posts related to SQlcl Project:
