SQLcl

Using: Oracle SQLDeveloper Command-Line (SQLcl) version: 24.3.2.0 build: 24.3.2.330.1718

After doing an project export, I am trying to verify the exported files, but I get this error

SQL> project verify -v
-------- Results Summary ----------
Errors:     1
Warnings:   0
Info:       6
---------------------------------

Level     Group Name     Test Name                     Message
-----     ----------     -----------                   -------------------------
INFO      settings       verifynonpublicsettings       No unsupported internal settings found
INFO      init           verifyprojectname             The final project name will be "WHOIS2"
INFO      examples       exampletest                   a message
INFO      stage          stagechangelogcomplete        Stage Changelog validation found no issues.
INFO      project        sqlclversion                  SQLcl Version check passed
ERROR     snapshot       verifysnapshot                NO_SNAPSHOT
  FILE: path=src/database/whois2/triggers/parameter_ins_upd_rules_trg.sql
INFO      snapshot       verifysnapshot                Snapshot validation found no issues.

The failing file looks like this:

  CREATE OR REPLACE EDITIONABLE TRIGGER "PARAMETER_INS_UPD_RULES_TRG"
 BEFORE INSERT OR UPDATE
 ON PARAMETER
 FOR EACH ROW
DECLARE
  sqlstr  VARCHAR2(10000);
BEGIN
  /* Upper/lower case felter */
  :new.system    := nls_upper(:new.system);
  :new.parameter := nls_lower(:new.parameter);
  :new.datatype  := nls_upper(:new.datatype);

  /* Opbyg dynamisk en PL/SQL blok, der kan teste om datatype og value stemmer overens */
  sqlstr := 'DECLARE
               var   '||:new.datatype||';

             BEGIN
               ';

  IF :new.datatype = 'DATE' THEN
    sqlstr := sqlstr || 'var := to_date(:val,''DD-MM-YYYY HH24:MI:SS'');';
  ELSE
    sqlstr := sqlstr || 'var := :val;';
  END IF;

  sqlstr := sqlstr ||
            '
             EXCEPTION
             WHEN OTHERS THEN
               RAISE_APPLICATION_ERROR(-20001,'''||:new.value||' kan ikke konverteres til datatypen '||:new.datatype||'!'');
             END;';
  EXECUTE IMMEDIATE sqlstr USING :new.value;
END;

/
ALTER TRIGGER "PARAMETER_INS_UPD_RULES_TRG" ENABLE;


-- sqlcl_snapshot {"hash":"c5b488447db4299fe5c4bf1100ce69634281ab5e","type":"TRIGGER","name":"PARAMETER_INS_UPD_RULES_TRG","schemaName":"WHOIS2","sxml":"<TRIGGER  xmlns  =\"http://xmlns.oracle.com/ku\"  version  =\"1.0\"><SCHEMA>WHOIS2</SCHEMA><NAME>PARAMETER_INS_UPD_RULES_TRG</NAME><TRIGGER_TYPE>BEFORE</TRIGGER_TYPE><DML_EVENT><EVENT_LIST><EVENT_LIST_ITEM><EVENT>INSERT</EVENT></EVENT_LIST_ITEM><EVENT_LIST_ITEM><EVENT>UPDATE</EVENT></EVENT_LIST_ITEM></EVENT_LIST><SCHEMA>WHOIS2</SCHEMA><NAME>PARAMETER</NAME><REFERENCING><FOR_EACH_ROW></FOR_EACH_ROW></REFERENCING></DML_EVENT><PLSQL_BLOCK>DECLARE  sqlstr  VARCHAR2  (10000  );BEGIN  :new  .system  :=nls_upper  (:new  .system  );:new  .parameter  :=nls_lower  (:new  .parameter  );:new  .datatype  :=nls_upper  (:new  .datatype  );sqlstr  :='DECLARE
               var   '  ||:new  .datatype  ||';

             BEGIN
               '  ;IF  :new  .datatype  ='DATE'  THEN  sqlstr  :=sqlstr  ||'var := to_date(:val,''DD-MM-YYYY HH24:MI:SS'');'  ;ELSE  sqlstr  :=sqlstr  ||'var := :val;'  ;END  IF  ;sqlstr  :=sqlstr  ||'
             EXCEPTION
             WHEN OTHERS THEN
               RAISE_APPLICATION_ERROR(-20001,'''  ||:new  .value  ||' kan ikke konverteres til datatypen '  ||:new  .datatype  ||'!'');
             END;'  ;EXECUTE  IMMEDIATE  sqlstr  USING  :new  .value  ;END  ;</PLSQL_BLOCK></TRIGGER>"}

The trigger code contains a string with embedded linebreaks. These linebreaks are repeated in the xml part of the file and breaks the validation as it seems that the xml is supposed to one long line.

If I join the lines in the xml part manually into one long line, the validation error is solved, but this is not fun work as it has to be done everytime the trigger is dumped and we have other triggers with the same issue… :-(