Revolutionizing Apex Deployments with SQLcl: Lessons from a Multischema Setup
Starting a new ERP project is always a mix of excitement and complexity. Our team recently embarked on a similar journey, emphasizing continuous delivery and incremental changes post-production. The challenge? Adopting robust practices for handling alter scripts alongside our existing custom scripts for exporting Oracle APEX and DDL into Git.
As the ERP grows, the need for scalable, reliable deployment pipelines becomes critical. Enter SQLcl, a tool that promises to transform how we approach Oracle database and APEX deployments. Here’s our journey, complete with challenges, solutions, and best practices.
Project Initialization: Multischema Setup
We began by initializing the project with SQLcl, specifying all schemas involved:
project init -name bigerp -makeroot -schemas adm_base,erp_base,erp_bl,erp,adm,aop,denso,dds,analytics
The result?
Your project has been successfully created
Next, we set up Git to track changes:
!git init --initial-branch=main
!git add .
!git commit -m "chore: initializing repository with default project BIGERP files"
!git checkout -b base-release
With Git in place, we turned our attention to exporting database objects.
Exporting the Multischema Application
To manage the complexities of a multischema environment, we used a SYSTEM connection for exports:
conn -name DEV_SYSTEM
project export
The current connection jdbc:oracle:thin: SYSTEM will be used for all operations
*** FUNCTIONS ***
*** INDEXES ***
*** PACKAGES ***
*** PACKAGE BODIES ***
*** PROCEDURES ***
*** SCHEDULES ***
*** SEQUENCES ***
*** SYNONYMS ***
*** TABLES ***
*** Failed to run select dbms_metadata.get_ddl('null','XYZ_ABC','ERP_BASE'), dbms_metadata.get_sxml('null','XYZ_ABC','ERP_BASE')
from dual
*** ORA-31600: Ung³ltiger Eingabewert null f³r Parameter OBJECT_TYPE in Funktion GET_DDL
ORA-31600: Ung³ltiger Eingabewert null f³r Parameter OBJECT_TYPE in Funktion GET_DDL
Exception in thread "DbmsMetadata# 3" java.lang.NullPointerException
at java.base/java.util.concurrent.ConcurrentHashMap.putVal(ConcurrentHashMap.java:1011)
at java.base/java.util.concurrent.ConcurrentHashMap.put(ConcurrentHashMap.java:1006)
at oracle.dbtools.extension.project.commands.export.DbmsMetadata.processException(DbmsMetadata.java:950)
at oracle.dbtools.extension.project.commands.export.DbmsMetadata.getMetadata(DbmsMetadata.java:684)
at oracle.dbtools.extension.project.commands.export.DbmsMetadata$6.run(DbmsMetadata.java:528)
*** TRIGGERS ***
*** TYPES ***
*** VIEWS ***
*** COMMENTS ***
*** REF_CONSTRAINTS ***
*** Failed to run select dbms_metadata.get_ddl('null','XYZ_DFG_R01','ERP_BASE'), dbms_metadata.get_sxml('null','XYZ_DFG_R01','ERP_BASE') from dual
*** ORA-31600: Ung³ltiger Eingabewert null f³r Parameter OBJECT_TYPE in Funktion GET_DDL
ORA-31600: Ung³ltiger Eingabewert null f³r Parameter OBJECT_TYPE in Funktion GET_DDL
Exception in thread "DbmsMetadata# 1" java.lang.NullPointerException
at java.base/java.util.concurrent.ConcurrentHashMap.putVal(ConcurrentHashMap.java:1011)
at java.base/java.util.concurrent.ConcurrentHashMap.put(ConcurrentHashMap.java:1006)
at oracle.dbtools.extension.project.commands.export.DbmsMetadata.processException(DbmsMetadata.java:950)
at oracle.dbtools.extension.project.commands.export.DbmsMetadata.getMetadata(DbmsMetadata.java:684)
at oracle.dbtools.extension.project.commands.export.DbmsMetadata$6.run(DbmsMetadata.java:528)
*** GRANTS ***
*** APEX_APPLICATION ***
Exporting Workspace application 101:test
STD_ARTIKEL_ATTRIBUTE_R03 not finished (timeout = 60s)
Failed to format:
PACKAGE_BODY : P_MIG
----------------------------------
PACKAGE_BODY 100
PROCEDURE 13
APEX_APPLICATION 12
TRIGGER 68
FUNCTION 39
VIEW 1940
COMMENT 20
SYNONYM 73
TYPE_SPEC 6
TABLE 235
GRANT 5802
REF_CONSTRAINT 292
PACKAGE_SPEC 103
SEQUENCE 19
INDEX 332
SCHEDULE 2
----------------------------------
Exported 9.056 objects
Elapsed 804 sec
SQL>
Results: A Mixed Bag
The export process worked well for most objects, completing in an impressive 804 seconds for over 9,000 objects. However, we encountered a few issues:
TABLES
Errors like:*** TABLES *** *** Failed to run select dbms_metadata.get_ddl('null','XYZ_ABC','ERP_BASE'), dbms_metadata.get_sxml('null','XYZ_ABC','ERP_BASE') from dual *** ORA-31600: Ung³ltiger Eingabewert null f³r Parameter OBJECT_TYPE in Funktion GET_DDL ORA-31600: Ung³ltiger Eingabewert null f³r Parameter OBJECT_TYPE in Funktion GET_DDL Exception in thread "DbmsMetadata# 3" java.lang.NullPointerException at java.base/java.util.concurrent.ConcurrentHashMap.putVal(ConcurrentHashMap.java:1011) at java.base/java.util.concurrent.ConcurrentHashMap.put(ConcurrentHashMap.java:1006) at oracle.dbtools.extension.project.commands.export.DbmsMetadata.processException(DbmsMetadata.java:950) at oracle.dbtools.extension.project.commands.export.DbmsMetadata.getMetadata(DbmsMetadata.java:684) at oracle.dbtools.extension.project.commands.export.DbmsMetadata$6.run(DbmsMetadata.java:528)REF_CONSTRAINTS
Similar errors persisted for reference constraints.*** REF_CONSTRAINTS *** *** Failed to run select dbms_metadata.get_ddl('null','XYZ_DFG_R01','ERP_BASE'), dbms_metadata.get_sxml('null','XYZ_DFG_R01','ERP_BASE') from dual *** ORA-31600: Ung³ltiger Eingabewert null f³r Parameter OBJECT_TYPE in Funktion GET_DDL ORA-31600: Ung³ltiger Eingabewert null f³r Parameter OBJECT_TYPE in Funktion GET_DDL Exception in thread "DbmsMetadata# 1" java.lang.NullPointerException at java.base/java.util.concurrent.ConcurrentHashMap.putVal(ConcurrentHashMap.java:1011) at java.base/java.util.concurrent.ConcurrentHashMap.put(ConcurrentHashMap.java:1006) at oracle.dbtools.extension.project.commands.export.DbmsMetadata.processException(DbmsMetadata.java:950) at oracle.dbtools.extension.project.commands.export.DbmsMetadata.getMetadata(DbmsMetadata.java:684) at oracle.dbtools.extension.project.commands.export.DbmsMetadata$6.run(DbmsMetadata.java:528)Timeouts
Certain objects, likeSTD_ARTIKEL_ATTRIBUTE_R03, timed out.STD_ARTIKEL_ATTRIBUTE_R03 not finished (timeout = 60s)Failed to format
Failed to format: PACKAGE_BODY : P_MIG
Debugging the Export Issues
The root cause? Memory?
I tried again.. and Sometimes it seems to be a invalid Object in the Database as root-cause.. but it wasn’t
Ok make it more granular:
Schema-Specific Exports
As a workaround, we tried to split the export by schema:
project export -schemas erp_base -debug
but it still fails…
Infos why?
SQL> project verify -verbose
-------- Results Summary ----------
Errors: 2
Warnings: 0
Info: 5
---------------------------------
Level Group Name Test Name Message
----- ---------- ----------- -------------------------
INFO settings verifynonpublicsettings No unsupported internal settings found
INFO init verifyprojectname The final project name will be "zoderp"
INFO examples exampletest a message
INFO stage stagechangelogcomplete Stage Changelog validation found no issues.
INFO project sqlclversion SQLcl Version check passed
ERROR snapshot verifysnapshot LOAD_SNAPSHOT_FAILED
Unrecognized character escape 's' (code 115)
at [Source: REDACTED (`StreamReadFeature.INCLUDE_SOURCE_IN_LOCATION` disabled); line: 1, column: 3314] (through reference chain: oracle.dbtools.extension.project.commands.stage.objectclasses.SrcSnapShot["sxml"])
FILE: path=src\database\erp_base\tables\std_belieferungsplan.sql
ERROR snapshot verifysnapshot NO_SNAPSHOT
FILE: path=src\database\erp_base\triggers\ct_art_stamm.sql
Okay talking with
I have Project filters which are ignored in grant generation..
for example i tried to filter out (SYS_FBA% Objects (Flashback Archive), TEST% and _TEST% Objects)
but if i look into with the -verbose commando
project export -schemas ZODIS_ADM_BASE,ZODIS_ERP_BASE,ZODIS_ERP_BL,ZODIS_ERP,ZODIS_ADM,ZODIS_AOP,ZODIS_DENSO,ZODIS_
DDS_PIM,ZODIS_KASSA,ZODIS_ANALYTICS -debug -verbose
.
.
grant SELECT on ZODIS_ERP_BASE.SYS_FBA_HIST_90073 to ZODIS_ERP_BL;
grant SELECT on ZODIS_ERP_BASE.SYS_FBA_HIST_90073 to ZODIS_ERP;
.
.
still here
Fixing
Unrecognized character escape 's' (code 115)
at [Source: REDACTED (`StreamReadFeature.INCLUDE_SOURCE_IN_LOCATION` disabled); line: 1, column: 3314] (through reference chain: oracle.dbtools.extension.project.commands.stage.objectclasses.SrcSnapShot["sxml"])
FILE: path=src\database\erp_base\tables\std_belieferungsplan.sql
ERROR snapshot verifysnapshot NO_SNAPSHOT
FILE: path=src\database\erp_base\triggers\ct_art_stamm.sql
in ct_art_stamm is a code line like that
g_cr constant char(1) := '
';
And in std_belieferungsplan.sql is a constraint like that
alter table "ZODIS_ERP_BASE"."STD_BELIEFERUNGSPLAN" add constraint "STD_BELIEFERUNGSPLAN_C03" check ( regexp_like ("INFO_AN_SPEDITION_UM",'^([0-1][0-9]|2[0-3]):[0-5][0-9]$')) enable;
thats the reason why it crashes , i think it is a Problem with escaping in sqlcl procject.
Hamza Eraoui | LinkedIn is informed
Looking Ahead: The SQLcl Advantage
Incorporating SQLcl into our workflow has been a game-changer. Despite initial hiccups, its flexibility and powerful features make it an essential tool for ERP projects, particularly in multischema environments.
For teams embarking on similar journeys, our advice is simple: embrace SQLcl, iterate on your processes, and share your learnings. With SQLcl at the helm, database deployments will never be the same.