"Non supported SQL92 token" error raised with certain Q quote operator usage

[dmcghan]

Environment

Liquibase Version: 4.17.0

Liquibase Extension(s) & Version: Oracle's SQLcl 22.4.0.0

Database Vendor & Version: Oracle Database 19c

Operating System Type & Version: macOS 11.7

Infrastructure Type/Provider: Oracle Cloud

Description

We got the following error recently while running SQLcl's 'lb update' command using a liquibase formatted sql changeset: Non supported SQL92 token at position 123.

I could track it down to code (function, procedure, package, etc.) using the Q Quote operator with matching characters in the string being escaped.

Steps To Reproduce

Given the following controller (main.controller.xml):

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
  <include file="test.sql" relativeToChangelogFile="true"/>
</databaseChangeLog>

And the following changeset (test.sql):

--liquibase formatted sql
--changeset user:123 runOnChange:true
create or replace function qq_test
  return varchar2
as
begin
  return q'| foo || bar |';
end;

Running this command would result in the error:

lb update -changelog-file main.controller.xml

Here’s another example changeset that would throw the same error, only using * instead of |:

--liquibase formatted sql
--changeset dan:123 runOnChange:true
create or replace function qq_test
  return varchar2
as
begin
  return q'* foo ** bar *';
end;

While the Q Quote operator is Oracle-specific, I wouldn't think that would matter here as the code should simply be passed through to be executed, no?

Here's an example of a changeset that uses the Q Quote operator without any issues...

--liquibase formatted sql
--changeset dan:123 runOnChange:true
create or replace function qq_test
  return varchar2
as
begin
  return q'* foo bar *';
end;

It seems that either Liquibase or JDBC is trying to parse the SQL and doesn't recognize the syntax, though it's completely valid in Oracle. I'm not a Java developer so I can't easily test at that level. Any ideas as to what the source of the problem could be?

[siddharth7777]

I am also facing the same issue and fixed the same using the '~' sign
Here's an example of a changeset that uses the Q Quote operator without any issues...

create or replace function qq_test
  return varchar2
as
begin
  return q'~ foo || bar ~';
end;