SQLcl: MCP Server & SQL Prompt

I had a SQLcl project deployment fail on what looks to be a bug with the DDL generation on the stage step.

Specifically, if a table column is altered and it has a default value, then when the deployment ran, I got the following error as the DDL is slightly syntactically incorrect:

ORA-01442: column to be modified to NOT NULL is already NOT NULL

Software details:

• Oracle SQLDeveloper Command-Line (SQLcl) version: 25.3.2.0 build: 25.3.2.317.1117
• Oracle Database: 23.26.0.0.0

REPRODUCIBLE TEST CASE

Step 1: Setup users and one simple table:

connect system
drop user if exists u1 cascade;
drop user if exists u2 cascade;

grant create session, create table, create trigger, unlimited tablespace to u1 identified by u1;
create table u1.t (col1 varchar2(1) default 'Y' not null);

grant create session, create table, create trigger, unlimited tablespace to u2 identified by u2;

Step 2: Basic SQLcl Project setup (just the standard steps here):

connect u1/u1
!git init --initial-branch=main
project init -name test_project -schemas u1 -verbose
project config set -name export.setTransform.emitSchema -value false
!git add -A
!git commit -m 'Initial'
!git checkout -b version1

project export
!git add -A
!git commit -m 'Export'
project stage
!git add -A
!git commit -m 'Stage'
project release -version 1
!git add -A
!git commit -m 'Release'
project gen-artifact -version 1
!git checkout main
!git merge --squash version1
!git commit -m 'Version 1'

Step 3: Deploy (into a separate schema) - this step works fine:

connect u2/u2
project deploy -file artifact/test_project-1.zip

Step 4: Make a column modification as the original user (changing it's length from 1 to 2 characters):

connect u1/u1
!git checkout -b version2
alter table t modify (col1 varchar2(2) default 'Y');

Step 5: Build a new deployment artifact (again the standard/normal SQLcl Projects steps):

project export
!git add -A
!git commit -m 'Export'
project stage
!git add -A
!git commit -m 'Stage'
project release -version 2
!git add -A
!git commit -m 'Release'
project gen-artifact -version 2

Step 6: Deploy the new artifact - this is the step that fails:

connect u2/u2
project deploy -file artifact/test_project-2.zip

Resulting error:

Migration failed, error reported:
Error starting at line : 3 File @ /.../install.sql
In command -
alter table t modify (
    col1 varchar2(2 byte) default 'Y' not null enable
)
Error report -
ORA-01442: column to be modified to NOT NULL is already NOT NULL

In this particular and simplified test-case, I am simply expanding the length of the column. In my real deployment I was changing it from BTYE to CHAR semantics.

I was able to workaround this issue by manually editing the files (in my case I had this for eight tables) after the project stage command and fixing the DDL.

(In the test case above, that would be the dist/releases/next/changes/version2/u1/tables/t.sql file.)

Then the rest of the steps and the deployment worked. But manually editing the dist/releases/next/… files is probably pretty undesirable and not recommended.