Hi everyone,
I am currently testing the new project command in SQLcl v24.3.2 and came up with some issues during staging/deployment.
Export of database codes:
When having the ORDS schema export enabled, the deployment fails with the following error (ORA-06550):
An error has occurred:
liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset ....
Reason: liquibase.exception.DatabaseException: ORA-06550: line 8, column 24:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
:= . ( @ % ; not null range default character
https://docs.oracle.com/error-help/db/ora-06550/ [Failed SQL: (6550) -- sqlcl_snapshot src/database/ords_schema/ords_schema.sql:null:b4dab76f0627d817dd45f4ac1faedb8713f442c5:create
-- Generated by ORDS REST Data Services 24.1.1.r1201228
-- Schema: .... Date: Tue Jan 07 11:07:02 2025
--
DECLARE
l_roles OWA.VC_ARR]
ORA-06550: line 8, column 24:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
:= . ( @ % ; not null range default character
The LB attribute “splitStatements” is not declared in the ORDS schema export file (and any other PL/SQL code file). Is this a bug? I know that the attribute is “true” by default in standalone Liquibase. Is this also still the case for the LB extension in SQLcl?
For previous SQLcl releases, it was needed to disable splitStatements for any kind of database object codes (types, triggers, packages etc).
I have tried to rerun the deployment having splitStatements set to “false”. After that change, the ORDS module definition could be deployed successfully.
Trigger DDL:
The DDL of Triggers also includes an “alter trigger … enable;” statement that cannot be run by Liquibase in a single file together with object code. I haven't found any option to disable it, or to move it in a separate SQL file, via SQLcl/.dbtools settings.
What I have done so far is to remove the statement from the export source file myself.
create or replace editionable trigger trg_persons before
insert on persons
for each row
begin
if :new.person_id is null then
:new.person_id := seq_persons.nextval;
end if;
end;
/
As described in this post (https://forums.oracle.com/ords/apexds/post/export-differences-between-triggers-via-sqlcl-7808) exporting the schema by “liquibase generate-schema” does not include the alter trigger statement anymore, but the new “project export” does.
Thanks for your help!
Kind regards,
Maurice