I have some SQLcl Projects based on schemas with tables that use identity columns with the NOCACHE option.
Later found out that in other databases where we had deployments using SQLcl Projects, that the NOCACHE setting was lost (or technically was never implemented).
TL;DR : The DDL being generated by SQLcl Projects does not include an identity columns' CACHE setting.
Software details:
- Oracle SQLDeveloper Command-Line (SQLcl) version: 25.4.0.0 build: 25.4.0.336.1328
- Oracle Database: 23.26.0.0.0
REPRODUCIBLE TEST CASE
Step 1: Setup users and two simple tables that includes an identity column:
connect system
drop user if exists u1 cascade;
drop user if exists u2 cascade;
grant connect,resource to u1 identified by u1;
grant unlimited tablespace to u1;
-- Create a table using NOCACHE on the identity column
create table u1.t1 (col1 number generated by default on null as identity nocache);
-- Create a table with a non-default (i.e. not 20) cache on the identity column
create table u1.t2 (col1 number generated by default on null as identity cache 5);
grant connect,resource to u2 identified by u2;
grant unlimited tablespace to u2;
Step 2: Basic SQLcl Project usage (just the standard steps here – skip ahead if you already have these steps automated):
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 the second separate schema - this will create the tables with the identity columns:
connect u2/u2
project deploy -file artifact/test_project-1.zip
Step 4: Connect as a DBA user and view the differences between the original u1.t table and the SQLcl Projects deployment created u2.t table:
connect system
SELECT owner||'.'||table_name||'.'||column_name AS table_name, identity_options FROM all_tab_identity_cols WHERE table_name IN ('T1','T2');
The output should be:
SQL> SELECT owner||'.'||table_name||'.'||column_name AS table_name, identity_options FROM all_tab_identity_cols WHERE table_name IN ('T1','T2');
TABLE_NAME IDENTITY_OPTIONS
_____________ _____________________________________________________________________________________________________________________________________________________________________________________________________
U1.T1.COL1 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 0, ORDER_FLAG: N, SCALE_FLAG: N, EXTEND_FLAG: N, SESSION_FLAG: N, KEEP_VALUE: N
U1.T2.COL1 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 5, ORDER_FLAG: N, SCALE_FLAG: N, EXTEND_FLAG: N, SESSION_FLAG: N, KEEP_VALUE: N
U2.T1.COL1 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N, SCALE_FLAG: N, EXTEND_FLAG: N, SESSION_FLAG: N, KEEP_VALUE: N
U2.T2.COL1 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N, SCALE_FLAG: N, EXTEND_FLAG: N, SESSION_FLAG: N, KEEP_VALUE: N
SQL>
And as you can see in the output:
- The original table U1.T1 had
CACHE_SIZE: 0 but the SQLcl Projects created schema U2.T1 table has CACHE_SIZE: 20 (default).
- The original table U1.T2 had
CACHE_SIZE: 5 but the SQLcl Projects created schema U2.T2 table has CACHE_SIZE: 20 (default).
So it's pretty evident, that SQLcl projects isn't capturing the **CACHE_SIZE** and consequently, the default value of **20** is being used on deployments.
This is trivially easy to confirm by just inspecting the DDL files in the src and dist directories:
$ grep -r col1 src
src/database/u1/tables/t1.sql: col1 number generated by default on null as identity not null enable
src/database/u1/tables/t2.sql: col1 number generated by default on null as identity not null enable
$ grep -r col1 dist
dist/releases/1/changes/version1/u1/tables/t1.sql: col1 number generated by default on null as identity not null enable
dist/releases/1/changes/version1/u1/tables/t2.sql: col1 number generated by default on null as identity not null enable
So it looks to me like this is probably a DDL generation bug.