This is a continuation of my previous article – an introduction to Liquibase SQLcl – in which I’ve shown you how to capture all objects from the DEV schema and move them to UAT (be sure to read that first). Now, we’ll focus on tracking changes in the DEV environment and moving these changes to UAT, too. Like last time, we’ll do this without thinking about what should be moved and in what order – SQLcl Liquibase will do this for us.

Make some changes to the DEV environment

First, let’s make some changes to our DEV environment, so we have something to track and export. They’re as follows:

create table locations
       (location_id    number(4),
         street_address varchar2(40),
         postal_code    varchar2(12),
         city       varchar2(30)
      constraint     loc_city_nn  not null,
         state_province varchar2(25),
         country_id     char(2)
       );
   create unique index loc_id_pk
   on locations (location_id);
   alter table locations
   add (constraint loc_id_pk
                 primary key (location_id),
         constraint loc_c_id_fk
                 foreign key (country_id)
                references countries(country_id)
       );

create or replace procedure secure_dml
is
begin
   if TO_CHAR(SYSDATE, 'HH24:MI') not between '08:00' and '18:00'
      or TO_CHAR(SYSDATE, 'DY') in ('SAT', 'SUN')
   then
      RAISE_APPLICATION_ERROR(-20205,
                              'This is a change for this blog.You may only make changes during normal office hours');
   end if;
end secure_dml;

Export changed DEV database schema

Now, how to export these changes? There are two ways to do it (I am logged into DEV schema using SQLcl and located in the database folder).

Export all objects from the DEV schema

This way of exporting changes is simpler because SQLcl Liquibase will automatically handle all objects and add what is needed to the controller.xml file. However, generating a full schema can take time, especially with bigger databases.

Step-by-step instructions

1. First, remove everything from the database folder (all files generated using the generate-schema command from part 1 of this blog series):

2. Generate DEV schema again by executing this command (SQLcl doesn’t replace existing files, that’s why I had to remove them first):

  liquibase generate-schema --split

3. If you are tracking your changes with GIT, you will notice that there are 3 of them: 

• The secure_dml procedure
• A new locations table
• A new line added to the controller.xml file

Export only new/changed objects

You can also export only new/changed objects. For obvious reasons, this way of exporting is much faster, and it allows you to add only your changes to version control tools like GIT. However, you’ll need to remember to add new lines to the controller.xml file in the correct order.

Step-by-step instructions

1. First, go into the /database/procedure/ folder

2. Remove the secure_dml_procedure.xml file

3. Export a new version of the secure_dml procedure

liquibase generate-object --object-type procedure --object-name secure_dml

4. Now, go into /database/table/ and export your newly created table

liquibase generate-object --object-type table --object-name locations

5. Then, manually add a line with a new table to the controller.xml file

🗄️ Need database experts? Pretius has a team of over 200 experienced developers, software architects and other IT specialists. Contact us at hello@pretius.com and tell us your needs.

Update UAT with new or changed objects

Since I’ve exported the actual version of the DEV schema, all that remains is moving changes to my UAT. 

Step-by-step instructions

1. Log into UAT using SQLcl (if you wonder how I set up a connection that way, check this SQLcl’s new feature)

2. Check what changes will be moved from DEV to UAT by executing the update-sql command (this command doesn’t change anything in a database).

liquibase update-sql --changelog-file controller.xml --output-file preview.sql

3. Review generated file. Notice that besides the things that Liquibase logs into %DATABASECHANGELOG% objects, there are two changes:

• A new locations table
• Updated version of the SECURE_DML procedure

4. Since everything looks good, we can move these changes to UAT using the update command

liquibase update --changelog-file controller.xml

You can check new rows with changesets in the DATABASECHANGELOG table or the exact SQL that was executed for every changeset.

Conclusion

And that’s it – my DEV and UAT environments are now equal, with the exact same number and versions of objects. If you followed this guide to the letter, you should have similar results. If you have any questions, reach out to me at rgrzegorczyk@pretius.com. Also, check out my other articles on the Pretius blog:

1. What is Liquibase and how to start using it? Automate your database scripts deployment with this Liquibase tutorial
2. Boost the management of your Oracle Database version control changes with Liquibase
3. Oracle data masking: hide information from users with an easy-to-use VPD
4. Use Liquibase to track DB changes without direct access – A quick guide
5. Use SQLcl Liquibase to move all database objects from DEV to the UAT environment

Want to use Liquibase in your software projects? Pretius has extensive knowledge regarding this technology, and world-class expert who know it better than most. Reach out to us at hello@pretius.com – maybe we can help you out!