Dynamic Object Transformation with SQLcl Liquibase and DBMS

3.6 Dynamic Object Transformation with SQLcl Liquibase and `DBMS_METADATA` Oracle Database Package

Oracle supplies many packages, which are automatically installed with Oracle Database, that extend database functionality. One of these packages, DBMS_METADATA, provides a way to retrieve metadata from the database dictionary as XML or SQL Data Definition Language (DDL) statements, and to submit the XML to recreate the object.

Oracle SQLcl Liquibase uses the DBMS_METADATA package to generate and execute specialized changelogs that transform the state of the database objects dynamically. This is a feature not available in the Liquibase open-source client. By using SQLcl-exclusive Liquibase commands such as generate-schema, a complete Oracle database schema can be updated. This includes altering tables and other objects already present according to the changelog’s specification. In open-source Liquibase, if the update command is run for a changelog mentioning a table that is pre-existing, it will fail. This is because open-source Liquibase is incapable of altering pre-exising objects.

SQLcl Liquibase’s specialized changelogs generated from commands such as generate-schema and generate-object use the SXML data format from the DBMS_METADATA package to execute these dynamic updates. SXML is an XML representation of object metadata that looks like a direct translation of SQL DDL into XML.

This section demonstrates this concept using a few examples. For these example, a Windows-based operating system is used.

Example 1

Connect to an Oracle database with SQLcl release 22.3. The one featured in this example includes a few sample tables. The tables that are going to be used for this example are employees and departments.

SQL> select table_name from user_tables;

TABLE_NAME
_____________
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
HIRE_DATE
PERSON_COLLECTION
COUNTRIES

9 rows selected.

Generate a schema of the database state using SQLcl Liquibase.
```
SQL> lb generate-schema
```
Switching over to another command-line window, use the open-source Liquibase client to generate a changelog of the database state. Because generate-schema is a SQLcl-exclusive command that uses extended functionality, the open-source Liquibase client must use the generate-changelog command.

Note:
You can download the open-source Liquibase client from the Liquibase website.
```
>liquibase --changelog-file=sql_test.xml generate-changelog
```
The database state is now captured in both SQLcl Liquibase and the open-source Liquibase client.

In the SQLcl command-line window, make a few changes to the database.

SQL>alter table employees add height number;
Table EMPLOYEES altered.

SQL>alter table employees add mood varchar2(50);
Table EMPLOYEES altered.

SQL>alter table departments add happiness varchar2(50);
Table DEPARTMENTS altered.

View these column additions in the database.

SQL> select * from departments;
SQL> select * from employees;

You need to use the Liquibase update command to alter the database to the state of the changelogs. This is the database state where employees and departments tables do not have the extra columns added.

To do that, in the command-line window not connected to SQLcl and that was used for running the open-source Liquibase client commands, enter the following command:
```
>liquibase --changelog-file=sql_test.xml update
```
The update command fails because Liquibase encounters objects already existing in the database such as the tables. This standard version of Liquibase does not handle these objects dynamically.
In the SQLcl command-line window, the same step is repeated with SQLcl Liquibase.

First, examine the SQL that is used to complete the schema update using the update-sql command.
```
SQL>lb update-sql -changelog-file controller.xml
```
Examining the output, you see that departments and employees tables are recognized and that you need to drop the extra columns that you created to return to the state in the changelog.

In actual use cases that are more complicated, there can often be risks involved with the SQL drop command. Examining the SQL code using the update-sql command is beneficial for review. Any manual changes can then be made to the changesets and checked as needed.

When you run the update command, the schema is successfully updated and the columns are dropped.
```
SQL> lb update -changelog-file controller.xml
--Starting Liquibase at 68:23:55 (version xml 4.15.0 #0 built at 2022-08-19 14:45+0000)

-- Loaded 38 change(s)
```

Example 2

This example also explains the same concept, this time adding columns and a table.

For this example, in the database, a table Activities has been previously created along with columns Head_Count and Retention in the departments table and Awards in the employees table.

Changelogs are generated and the Activities table and Head_count, Retention, and columns are deleted so as to showcase using Liquibase to create them.

Generate the schema in SQLcl.

Note:
Move the changelogs from the previous example to a separate folder to avoid issues.
```
SQL> lb generate-schema
--Starting Liquibase at 09:33:52 (version 4.15.0 #0 built at 2022-08-19 14:45+0000)
```
Switching over to another command-line window to execute commands in the open-source Liquibase client, here also generate a changelog:
```
>liquibase --changelog-file=sql_test2.xml generate-changelog
```

In the SQLcl command-line window, delete the Activities table and Head_count, Retention, and Awards columns, so that their creation can be demonstrated with the update command.

SQL> drop table activities;
Table ACTIVITIES dropped.

SQL> alter table employees drop column awards;
Table EMPLOYEES altered.

SQL> alter table departments drop column head_count;
Table DEPARTMENTS altered.

SQL> alter table departments drop column retention;
Table DEPARTMENTS altered.

In the open-source Liquibase command-line window, run the update command.
```
> liquibase --changelog-file=sql_test2.xml update
```
The update fails when Liquibase encounters pre-existing objects.
In the SQLcl command-line window, generate the SQL to examine, then run the update with SQLcl Liquibase .
```
SQL>lb update-sql -changelog-file controller.xml
```
Looking at the SQL output, you can see sections involving the creation of the Activities table and SQLcl Liquibase generating the SQL statements to alter the Departments and Employees table to add the columns.

Run the update to see that the table and columns are added.

SQL> lb update -changelog-file controller.xml

SQL>select table_name from user_tables;
SQL>select * from employees;
SQL>select * from departments;

SQL> select table_name from user_tables;

TABLE_NAME
_____________
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
DATABASECHANGELOG_ACTIONS
DATABASECHANGELOG
ACTIVITIES
COUNTRIES

11 rows selected.