Keep It Simple. Developer's tips.
Oracle's SQLcl Project - the only CI/CD tool for APEX you will ever need.

Oracle's SQLcl Project - the only CI/CD tool for APEX you will ever need.

Rafal Grzegorczyk's photo
Rafal Grzegorczyk
·

3 min read

Table of contents

Introduction

In my previous article, I covered the basics of Oracle’s SQLcl Project, including versioning and releasing Oracle database changes. Please read it before reading further.

Now, I’ll cover how to organize the CI/CD process for Oracle APEX applications using the SQLcl project.

Prerequisites

  • MacOS M1 with Oracle’s SQLcl (ver. 24.3.2.0) installed (installation guide here)

  • 2x Oracle Database:

    • Development (DEV) environment (also called HR_DEV_OCI)

    • Production (PROD) environment (also called HR_PROD_OCI)

    • What I have on DEV&PROD:

      • Schema HR (no DB objects)

      • APEX installed

      • Workspace HR created

      • No APEX apps yet

  • Visual Studio Code - all commands will be executed using my terminal there

  • SQL Developer for making changes in database objects

  • A new locally created folder called “SQLCL_PROJECT_DB_APEX“desde

  • I assume that you understand basic commands of GIT

Configure SQLcl Project

Connect to DEV (HR schema).

Initialize project

project init -name SQLCL_PROJECT_APEX_DB -schemas HR

Create a GIT repository & commit project configuration into main branch

--this will create branch main
!git init --initial-branch=main
--add all files to GIT stage
!git add .
--commit changes with commit message
!git commit -m "Initialize project structure for SQLCL_PROJECT_DB_APEX"

Make changes in Development (DB objects & APEX)

  • I have created:

    • Some DB objects on DEV (I used SQL Developer & Oracle’s sample HR schema)

    • 2 x sample APEX applications 111 and 222 in my workspace HR.

Prepare release script with APEX & database changes made on DEV

Export APEX&DB changes from DEV

  • Once ready to deploy to PROD, create branch DEV and switch to it.
--connected to HR_DEV_OCI
!git checkout -b dev

  • Connect to DEV HR schema and export all objects and APEX applications from DEV
--connected to HR_DEV_OCI
--still on GIT DEV branch
project export

My APEX apps 111 and 222 were exported along with my DB objects.

Customizing APEX export

By changing the existing .dbtools/project.config.json file, you can set some settings to control how you would like your APEX apps to be exported.

If you want more customization, check out this article by Hamza Eraoui.

Commit changes to the DEV branch

Having my files exported, I committed it to the GIT branch DEV.

--connected to HR_DEV_OCI
--still on GIT DEV branch
!git add .
!git commit -m "Files exported from DEV"

Stage project

The “Project stage” command compares the GIT “main” (PROD) branch with the “dev” branch.

--connected to HR_DEV_OCI
--still on GIT DEV branch
project stage

Create release 1.0

I’m ready to prepare a release 1.0.

--connected to HR_DEV_OCI
--still on GIT DEV branch
project release -version 1.0 -verbose

Generate artifact (script to be deployed on Production)

My release is ready so that I can generate a deployment package (artifact) with code for version 1.0.

--connected to HR_DEV_OCI
--still on GIT DEV branch
project gen-artifact -version 1.0

There’s no need to unzip this file, and it’s also ignored with GIT. However, looking inside…all my DB changes and APEX apps are inside. Everything was done automatically by SQLcl Project.

Now, I’m ready to deploy to PROD, and I will commit my changes to the DEV branch.

Deploy version 1.0 (APEX&DB changes) to the Production environment.

Connect to PROD environment using SQLcl and deploy version 1.0

--connected to HR_PROD_OCI
project deploy -file  artifact/SQLCL_PROJECT_DB_APEX-1.0.zip -verbose

All my DB changes & APEX applications were installed into the Production environment.

Cheers.

PS: Are you Wondering about the photo? It's sunrise at Miami Beach, taken a few days after the Kscope 24 conference.

orclapexcicdsqlcl