PLSQL (Oracle) incorrect splitting of Spec and Body with variables containing regular expressions

General Discussion
1

I stumbled across a curious behaviour (bug?) when deploying a PLSQL package with a variable defined in the spec which contains a string with a regular expression.

This is a minimal example to reproduce this bahaviour:

Test_Mail_PAC.sql:

-- liquibase formatted sql
-- changeset test:Test_Mail_PAC runOnChange:true stripComments:false endDelimiter:/
-- comment Initial Creation
CREATE OR REPLACE PACKAGE Test_Mail_PAC AS

  mail_regex_c  CONSTANT varchar2(80):= '^[a-zA-Z0-9!#$%''\*\+-/=\?^_`\{|\}~]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}$';

  FUNCTION checkTestMail(mail_in IN VARCHAR2) RETURN NUMBER;
END Test_Mail_PAC;
/
CREATE OR REPLACE PACKAGE BODY Test_Mail_PAC AS

  FUNCTION checkTestMail(mail_in IN VARCHAR2) RETURN NUMBER IS
    test_c    CONSTANT varchar2(30):='Test Constant';
  BEGIN
    IF REGEXP_LIKE(mail_in,mail_regex_c) THEN
       return 1;
    END IF;
    
    return 0;
  END checkTestMail;
  
END Test_Mail_PAC;
/

update.yml

databaseChangeLog:
    - include:
        file: path/to/Test_Mail_PAC.sql

The result is that the whole file is compiled as invalid spec. No body is compiled.

The problem can be fixed if either the regex string is removed or the constant in the function is not set directly.
Unfortunately that is not an option for us!

Via sqlplus deployment of this package makes no problems.

Environment:
Oracle 19
Liquibase 4.25.1

Is there a good solution for this problem or is it a real bug to be fixed?

2

If your code uses a / you will need to choose a different end-delimiter.

Try using a double-character delimiter, like // or $$.

3

Thank you for the reply!

We use single / so that we are as near as possible to regular SQL files so that we still are able to compile these packages with our default tools (e.g. Toad). I fear // would break this.

But maybe as workaround for some files where this problem occurs could be a valid option…