Wrapped Oracle packages leave endDelimiter in compiled code with createProcedure change type

[tschf]

Environment

Liquibase Version: 4.17.2

Liquibase Integration & Version: CLI

Database Vendor & Version: Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production

Description

When installing a wrapped package using the change set type of createProcedure, end delimiter is not removed from the compiled code.

Steps To Reproduce

Package spec:

create or replace package lb_demo
as
    function get_name(n in varchar2) return varchar2;
end lb_demo;
/

Liquibased components for this test case:

source/plsql/lb_demo_wrapped.sql:

create or replace package body hr.lb_demo wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
f3 e7
C7uNqX4GMBimM/tcik3DZlp+HHIwg3lK7csVfHRGWE6OHAUN9hQa5FOhovwOITXgGEZwHQj5
/aHV8ltILXM69TOTTbd852V91XogQ/kw7Vh1iNblf7fy0S8EWQLbRqQ371PjTdjjhJ11Zuh2
qrg4Kg6U9NSzqz1dYKVaGzFKI1cVpvpmeQVMbwjwUU8IZGwSncf34ik0DR6+Obyux9SDfQGz
V0Q/zqbhcIIz

;

db/liquibase/lb_demo_wrapped.sql.yml

databaseChangeLog:
- changeSet:
    author: trent
    id: 55
    runOnChange: true
    changes:
    - createProcedure:
        dbms: oracle
        encoding: utf8
        endDelimiter: ;
        path: source/plsql/lb_demo_wrapped.sql
    rollback:
    - empty: null

liquibase.properties:

liquibase.hub.mode=off
changeLogFile=db/liquibase/lb_demo_wrapped.sql.yml
url=jdbc:oracle:thin:@connect_string
username=user
password=password
classpath=/home/trent/Oracle/ojdbc11.jar
logLevel=info

Actual Behavior

Liquibase CLI output

$ liquibase update
Starting Liquibase at 14:26:58 (version 4.17.2 #5255 built at 2022-11-01 18:07+0000)
Liquibase Version: 4.17.2
Liquibase Community 4.17.2 by Liquibase
[2022-12-06 14:27:09] INFO [liquibase.lockservice] Successfully acquired change log lock
[2022-12-06 14:27:37] INFO [liquibase.changelog] Reading from ACDC.DATABASECHANGELOG
Running Changeset: db/liquibase/lb_demo_wrapped.sql.yml::55::trent
[2022-12-06 14:27:45] INFO [liquibase.changelog] Stored procedure created
[2022-12-06 14:27:45] INFO [liquibase.changelog] ChangeSet db/liquibase/lb_demo_wrapped.sql.yml::55::trent ran successfully in 766ms
[2022-12-06 14:27:47] INFO [liquibase.lockservice] Successfully released change log lock
Liquibase command 'update' was executed successfully.

Package after this operation

And cannot use my functional package until I recompile having removed the semicolon at the end

Work Arounds

1. In the source file, ommit the end delimiter character (;)
2. In the yml config, specifal the changelog type as sqlFile instead of createProcedure

[FBurguer]

Reproduced on liquibase 4.17.2. Adding it to the list of issues to fix. Thanks!

[MalloD12]

Hi @tschf,

Maybe I'm not understanding the issue here, with the steps and the data you shared in the description I can see there is something wrong with the package, but the first thing to mention here is package is something Liquibase only supports on its PRO version, so to avoid any odd behavior I would to use any change type supported by OSS version.

I tried deploying manually against my Oracle DB the given package:

create or replace package lb_demo
as
    function get_name(n in varchar2) return varchar2;
end lb_demo;

I removed the / because I executed this body directly on DBeaver, and then I used the given changelog and SQL file. If I didn't remove the ; from the SQL file it was highlighted as an error, but even removing it as suggested when I compile the package (after running an update command) package displays its body but it says it's malformed or corrupted:

And when running the same query you try:

SELECT lb_demo.get_name('Trent') FROM dual;

I got an error as well, but I think it's because the package body is corrupted:

Could you please clarify if I'm performing a different step if you don't mind?

Thanks,
Daniel.

[tschf]

Hi @MalloD12

In the snippet above for the body - if you take out the semi-colon does it work? On reflection, I think that a mistake in my bug report including the semi-colon at the end of the reference source file.

Ive tested with end delimiter as / i.e.

create or replace package body hr.lb_demo wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
f3 e7
C7uNqX4GMBimM/tcik3DZlp+HHIwg3lK7csVfHRGWE6OHAUN9hQa5FOhovwOITXgGEZwHQj5
/aHV8ltILXM69TOTTbd852V91XogQ/kw7Vh1iNblf7fy0S8EWQLbRqQ371PjTdjjhJ11Zuh2
qrg4Kg6U9NSzqz1dYKVaGzFKI1cVpvpmeQVMbwjwUU8IZGwSncf34ik0DR6+Obyux9SDfQGz
V0Q/zqbhcIIz
/

And that works fine using SQLcl or SQL*Plus (command line interface).

󱐌 localhost:1521/freepdb1: HR> @src/spec.sql

Package LB_DEMO compiled

󱐌 localhost:1521/freepdb1: HR> @src/body.sql

Package Body HR.LB_DEMO compiled

󱐌 localhost:1521/freepdb1: HR> select lb_demo.get_name('Trent') from dual;

LB_DEMO.GET_NAME('TRENT')    
____________________________ 
Trent 

[tschf]

Just getting up to speed on the latest version, I'm unsure if this is still an issue. Testing with 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"
        xmlns:ora="http://www.oracle.com/xml/ns/dbchangelog-ext"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.17.xsd"
>
  <changeSet id="spec" author="tschf" runOnChange="true">
    <createProcedure
      dbms="oracle"
      encoding="utf8"
      path="src/spec.sql"
    >
    </createProcedure>

  </changeSet>

  <changeSet id="body" author="tschf" runOnChange="true">
    <createProcedure
      dbms="oracle"
      encoding="utf8"
      path="src/body.sql"
    >
    </createProcedure>

  </changeSet>
</databaseChangeLog>

Seems working okay:

Liquibase Version: 4.27.0
Liquibase Open Source 4.27.0 by Liquibase
Running Changeset: lb_demo.xml::spec::tschf
Running Changeset: lb_demo.xml::body::tschf

UPDATE SUMMARY
Run:                          2
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            2

Liquibase: Update has been successful. Rows affected: 2
Liquibase command 'update' was executed successfully.

󱐌 localhost:1521/freepdb1: HR> @status

OBJECT_NAME                 OBJECT_TYPE     STATUS    
___________________________ _______________ _________ 
DATABASECHANGELOG           TABLE           VALID     
DATABASECHANGELOGLOCK       TABLE           VALID     
PK_DATABASECHANGELOGLOCK    INDEX           VALID     
LB_DEMO                     PACKAGE         VALID     
LB_DEMO                     PACKAGE BODY    VALID     

󱐌 localhost:1521/freepdb1: HR> select lb_demo.get_name('Trent') from dual;

LB_DEMO.GET_NAME('TRENT')    
____________________________ 
Trent                        

(this is with a final forward slash in the source files).

For good measure, also testing with yml as per OP, and that too seems working okay

databaseChangeLog:
- changeSet:
    author: tschf
    id: spec
    runOnChange: true
    changes:
    - createProcedure:
        dbms: oracle
        encoding: utf8
        endDelimiter: ;
        path: src/spec.sql
    rollback:
    - empty: null
- changeSet:
    author: tschf
    id: body
    runOnChange: true
    changes:
    - createProcedure:
        dbms: oracle
        encoding: utf8
        endDelimiter: ;
        path: src/body.sql
    rollback:
    - empty: null

Also thought I'll test sqlFile changeset type while I'm here - this throws an error

  <changeSet id="body" author="tschf" runOnChange="true">
    <sqlFile
      path="src/body.sql"
      dbms="oracle"
    />
  </changeSet>

     Reason: liquibase.exception.DatabaseException: ORA-00900: invalid SQL statement
 [Failed SQL: (900) aHV8ltILXM69TOTTbd852V91XogQ/kw7Vh1iNblf7fy0S8EWQLbRqQ371PjTdjjhJ11Zuh2
qrg4Kg6U9NSzqz1dYKVaGzFKI1cVpvpmeQVMbwjwUU8IZGwSncf34ik0DR6+Obyux9SDfQGz
V0Q/zqbhcIIz]