generateChangeLog Oracle19 - procedure object not working

Liquibase Development
1

Hi, I am relatively new to liquibase.
I started testing Liquibase Open Source (4.8.0) if all the supported objects can be actually generated using an Oracle 19 DB.
I got stuck with procedures and would like to know if I am doing something wrong or if actually something is wrong.

So my failing test case is that I created a table and a procedure that adds data to this table when called.
When I generate the changelog the procedure is always missing and I am out of ideas on what to try and where to look for examples or help.

test setup:

  • liquibase 4.8.0 portable on Win10
  • Oracle Virtual Lab Oracle 19 database in VirtualBox (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0)

Somewhere I read that someone solved a similar issue by using a trial-pro-key.
Here also similar info is given: 4783
But in the official documentation ‘procedure’ is not marked as PRO or listed among pro features but community features. change-types

  • SQL:
CREATE TABLE T_CICD_USER2 (
USER_ID VARCHAR2(20 BYTE) NOT NULL, 
USER_NAME VARCHAR2(20 BYTE), 
CHNG_DATE TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL, 
CHNG_ID VARCHAR2(20 BYTE) NOT NULL);

create or replace PROCEDURE P_FILL_T_CICD_USER2 AS 
BEGIN
  insert into t_cicd_user2 (chng_date, chng_id, user_name,user_id) values 
(
current_timestamp,
dbms_random.string('U', 20),
dbms_random.string('U', 20),
dbms_random.string('U', 20)
) ;
END P_FILL_T_CICD_USER2;

Liquibase commands I tried without success:

  • liquibase generateChangeLog --defaultsFile=liquibase_local_dev.properties --changeLogFile=changelog.oracle.xml --overwriteOutputFile=true
  • liquibase generateChangeLog --defaultsFile=liquibase_local_dev.properties --changeLogFile=changelog.oracle.sql --diffTypes=storedprocedure,tables,columns --overwriteOutputFile=true
  • liquibase snapshot --defaultsFile=liquibase_local_dev.properties --outputFile=snapshot.yaml --snapshotFormat=yaml

example output of generateChangeLog (only table shows up, storedprocedure is missing):

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.6.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.6.xsd">
    <changeSet author="xyz (generated)" id="1646210952546-1">
        <createTable tableName="T_CICD_USER2">
            <column name="USER_ID" type="VARCHAR2(20 BYTE)">
                <constraints nullable="false"/>
            </column>
            <column name="USER_NAME" type="VARCHAR2(20 BYTE)"/>
            <column defaultValueComputed="CURRENT_TIMESTAMP" name="CHNG_DATE" type="TIMESTAMP(6)">
                <constraints nullable="false"/>
            </column>
            <column name="CHNG_ID" type="VARCHAR2(20 BYTE)">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>
</databaseChangeLog>
2

I was limited in my first text to only give two links…
so here is one more…

But in the official documentation ‘procedure’ is not marked as PRO or listed among pro features but community features. change-types, diff

4

It is mentioned on the generateChangeLog command page, but only in the Pro section:

5

Hi daryldoak, thanks for your hint! I wonder how I was able to overlook it before…
Anyways, it’s still totally confusing to me to get a clear picture of the functional difference between Liquibase Open Source and pro edition.
The documentation also just says that special folders will be created using PRO but it is not that clear that general support of the change types is also included.

In the documentation also ‘view’ is listed under PRO features for generateChangeLog. But when I checked again just now it actually seems to be included in Liquibase Open Source. Can you help me to understand where I am wrong?
I also noticed when using the PRO key that no folder for views is created in the objects folder structure.

citation from docu:

Additional functionality with Liquibase Pro

While Liquibase Open Source stores all changesets in a changelog, Liquibase Pro creates a directory called Objects and places the directory at the same level as your changelog. The Objects directory contains a subdirectory for each of the following stored logic types:

    package
    packagebody
    function
    stored procedure
    trigger
    view

generated code with Liquibase Open Source Version 4.8.0:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.6.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.6.xsd">
    <changeSet author="xyz (generated)" id="1646381538593-1">
        <createTable tableName="T_CICD_USER2">
            <column name="USER_ID" type="VARCHAR2(20 BYTE)">
                <constraints nullable="false"/>
            </column>
            <column name="USER_NAME" type="VARCHAR2(20 BYTE)"/>
            <column defaultValueComputed="CURRENT_TIMESTAMP" name="CHNG_DATE" type="TIMESTAMP(6)">
                <constraints nullable="false"/>
            </column>
            <column name="CHNG_ID" type="VARCHAR2(20 BYTE)">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet author="xyz (generated)" id="1646381538593-2">
        <createView fullDefinition="true" viewName="V_CICD_USER2">CREATE OR REPLACE FORCE VIEW "V_CICD_USER2" ("USER_ID", "USER_NAME", "CHNG_DATE", "CHNG_ID") AS SELECT 
    "USER_ID","USER_NAME","CHNG_DATE","CHNG_ID"
FROM 
    
T_CICD_USER2</createView>
    </changeSet>
</databaseChangeLog>
6

hi, just referenced my older post before deleting it because I can’t edit it. I forgot to change one internal user name and seems that I can’t edit the post anymore. So here is the cleaned reference instead.

1 Like