Using SQLcl Liquibase Functionality with Open-Source Liquibase

3.5 Using SQLcl Liquibase Functionality with Open-Source Liquibase

SQLcl Liquibase with Oracle Database provides extended functionality to the Liquibase experience compared to the vanilla Liquibase client. This includes dynamically altering tables using Liquibase and SQLcl-exclusive Liquibase commands, such as generating specialized snapshots for:

A comprehensive schema (generate-schema)
Oracle REST Data Service (ORDS) objects (generate-ords-module and generate-ords-schema)
Oracle APEX objects (generate-apex)

By default, the Liquibase client does not include this enhanced functionality or enable you to read the specialized changelogs generated by SQLcl Liquibase.

You can add the functionality to read these specialized changelogs to the Liquibase client by copying certain jar files from SQLcl and updating your Liquibase properties file. The steps to do this are demonstrated using the following example:

Connect to your Oracle Database and start the SQLcl command-line interface. The example database has a few sample tables that you can view.
```
SQL> select table_name from user_tables;

TABLE_NAME
_____________
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
COUNTRIES

7 rows selected.
```

Create a table Fruits. This will serve as an example table to track.

SQL> create table fruits (id number(1,0), type varchar2(50), price number, constraint fruits_pk primary key (id));

Table FRUITS created.

The table is added to the list of sample tables.

SQL> select table_name from user_tables;

TABLE_NAME
_____________
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
FRUITS
COUNTRIES

8 rows selected.

Generate a changelog for the Fruits table.

SQL> lb generate-object -object-type table -object-name fruits;

--Starting Liquibase at 13:43:52 (version 4.15.0 #0 built at 2022-08-19 14:45+000)

Changelog created and written to file fruits_table.xml

Operation completed successfully.

Delete the Fruits table so that you can run some tests generating it with the changelog created.

SQL> drop table fruits;

SQL> select table_name from user_tables; 

TABLE_NAME
_____________
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
COUNTRIES

7 rows selected.

Switch to the vanilla Liquibase open-source command line client, which you can download from Liquibase’s website.
You must provide the credentials of the Oracle Database that you are connected to in SQLcl for the vanilla Liquibase client. Create a liquibase.properties file from a blank text file. In this example, the liquibase.properties file is created in C:\Users\ [username] folder on a Windows system. The following figure shows the properties file used in this example. For more information about providing database credentials for Liquibase, see Specifying Properties in a Connection Profile.
Copy and paste the fruits_table.xml changelog file created earlier from your SQLcl bin folder to the folder location of your liquibase.properties file (in this case, C:\Users\[username]). To keep your changelog files in a different location, specify the path to the file location in the changelog-file field of your liquibase.properties file.
Run the update command.
```
>liquibase --changelog-file=fruits_table.xml update
```
The update fails because the SQLcl specialized changelog is an unsupported format in the vanilla Liquibase client.

The next step is to add the functionality to read the changelogs of your SQLcl Liquibase in your vanilla Liquibase client so that you can run the changelog.
Copy five jar files from the lib and lib/ext folders in your SQLcl folder and add them to the lib folder of your vanilla Liquibase client (liquibase/lib):
- dbtools-liquibase.jar (sqlcl/lib/ext)
- dbtools-apex.jar(sqlcl/lib/ext)
- guava-with-lf.jar (sqlcl/lib)
- xmlparserv2_sans_jaxp_services.jar (sqlcl/lib)
- dbtools-common.jar (sqlcl/lib)
Update your liquibase.properties file by adding the following line:
```
change-exec-listener-class: liquibase.changelog.visitor.OracleActionChangeListener
```
You can now read SQLcl Liquibase changelogs in your vanilla Liquibase client.
Run the SQL Liquibase changelog in the vanilla Liquibase client.
```
>liquibase update --changelog-file fruits_table.xml
```
In another command-line window connected to SQL and your database, you can check if the table has been successfully created.
```
SQL> select table_name from user_tables;

TABLE_NAME
_____________
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
COUNTRIES
DATABASECHANGELOGLOCK
DATABASECHANGELOG
FRUITS
COUNTRIES

10 rows selected.
```
With this functionality added to your vanilla Liquibase client, you can also read changelogs for other changelog types such as Oracle Rest Data Services (ORDS), Oracle APEX, and full database schemas along with Oracle SQLcl scripts.