SQLcl Projects Reference

This is a Quick Reference / Cheat Sheet take your pick.

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 learnt everything by starting with Rafal’s blog here and then reading this, then watching this video, then reading the quick start examples. There is a lot of information, so I’ve summarized it in this blog. 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. 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 - source. 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, a branching strategy should suit the team’s current process, the nature of the project and budget.

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

Read this blog 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 then creating locally and then adding remotes.

• Already have a repository but you want to start using SQLcl Projects? Use this approach

SQLcl Projects Command Guide

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

Note: This table below scrolls horizontally.

In Summary:

• Your Repository Admins…

  • Perform the Initial Setup (init) from main

  • Creates Releases (release) from main

  • Creates Artifacts (gen-artifact) from main

    • It’s fine for developers to do this too for testing. It all depends on the process and how much automation there is.

  • Can protect the main branch from the Team in GitHub - guide here.

• 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 all 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. (Source - alluded to here)

Developing

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

Starting some work?

Need a new Feature Branch for this work?

Switch to Main and checkout 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 fatal: A branch named 'feature-1' already exists.

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

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?

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

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 project export?

• Yes?

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

• No?

  • Its 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 git has a magic time machine!?!

  • Try here

Finished your work?

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

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

    project export -o object_name
   

   Alternatively run export command to capture everything.

    project export
   

2. Run the Stage command in your feature branch

    project stage
   

3. Commit to the Feature Branch

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

4. 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? Admin will then merge (unless auto-merge is enabled).

     • PR rejected? Revise and Resubmit the same PR (usually).

Tips

Init

• I 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.

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)

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 (source).

• Need to run some PL/SQL pre-release or post-release? - see here. Although 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

• Artifact deployment was unsuccessful? There are two viable options (source)

  • 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 a SQLcl connection to it, then yes. (source)

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 any more? or you want to rename it?

  • Delete the file/Edit the filename in the _custom directory of your change

  • Edit the stage.changelog.xml and edit/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 run the changelog-sync command on the 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 are working on updating the Lifecycle paper "in the coming months” (source).

Other

• Can SQLcl Projects be used on apex.oracle.com? - No (source)

• Is is possible include SQLcl Projects into APEX Supporting Object scripts? - “Typically No” (source)

• 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.

• How can we manage several developers working on a shared environment who are touching the same objects? - Stay tuned.

Practices

Starting a new DB Application

Init on main without any Exported objects

Branch off of main to create a sprint-1 branch, export/stage there, then merge back to main.

Future branches can be labelled sprint-2.

Starting SQLcl Projects with an existing DB Application

Init on main without any Exported objects

Branch off of main to create a sprint-0 branch, export/stage there, then merge back to main.

Future branches can be labelled sprint-1.

Prior to deploying an artifact to a non-dev environment, run the changelog-sync command on the 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:

Init on main without any Exported objects

Branch off of main to create a sprint-0 branch, export/stage there, then merge back to main.

Future branches can be labelled 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

I need more help

• Start by reading Rafal’s blog here and then reading this, then watching this video, then reading the quick start examples

• Need to ask a question to the Community/SQLcl Team? Use the SQLcl Forum

• Need to ask a question on Slack? Try the apex.world SQLcl Channel

• Who can I follow on x? Neil, Jeff, Rafal, Dan, Martin, Barry, Hamza, Me

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

ENJOY!

What’s the picture? A Crooked Tree near Hookstone Trails. Visit Yorkshire!