Using SQL*Plus with Liquibase

If you maintain an Oracle database, you understand the importance of being able to easily apply complex stored procedures. In the same vein, anyone who maintains a CI/CD pipeline with multiple Oracle databases understands the importance of ensuring all stored procedures that are in Staging also end up in Production.

Enter SQL*Plus: An Oracle command-line tool that allows database professionals to apply specialized or parameterized stored procedures to the database. Since many Oracle databases rely on Liquibase to track and manage changes, we now offer a SQL Plus capability with Liquibase. 

SQL Plus integration for Liquibase Pro

Now you can use your existing Oracle PL/SQL to update your database while tracking changes with your Liquibase changelogs (SQL, XML, YML, or JSON). This new Liquibase SQL Plus integration will save you time, reduce weird workarounds, and let you use the SQL you already have. 

To use this new integration, you simply need to add runWith=sqlplus to a changeset and have SQL Plus in your PATH (or configure it in the accompanying liquibase.sqlplus.conf file).

Here’s how to try it out

Note: You must be running Liquibase v3.10+.

1. Get a free 30-day trial of Liquibase Pro. No credit card is required and no strings are attached. You can truly try before you buy.
2. Once you have your license key (which you’ll receive right after signing up), you can place it in your liquibase.properties file. For more details, check out the Docs.
3. While you’re in the liquibase.properties file, make sure that SQL Plus is in your PATH, or configure it in the accompanying liquibase.sqlplus.conf file. For more details, see the Docs.
4. Create a new file and name it test.oracle.sql.
5. Copy the following changeset into this new file:

--liquibase formatted sql
--changeset Liquibase Pro User:1-package runWith:sqlplus    
        --------------------------------------------------------
        --  DDL for Package emp_actions
        --------------------------------------------------------
        CREATE OR REPLACE PACKAGE emp_actions AS -- spec
        TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);
        CURSOR desc_salary RETURN EmpRecTyp;
        PROCEDURE hire_employee (
        ename VARCHAR2,
        job VARCHAR2,
        mgr NUMBER,
        sal NUMBER,
        comm NUMBER,
        deptno NUMBER);
        PROCEDURE fire_employee (emp_id NUMBER);
        END emp_actions;
        /

        --------------------------------------------------------
        --  DDL for Package BODY emp_mgmt
        --------------------------------------------------------
        CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
           tot_emps NUMBER;
           tot_depts NUMBER;
        FUNCTION hire
           (last_name VARCHAR2, job_id VARCHAR2,
            manager_id NUMBER, salary NUMBER,
            commission_pct NUMBER, department_id NUMBER)
           RETURN NUMBER IS new_empno NUMBER;
        BEGIN
           if (emp_id=0)
           then
           emp_id=emp_id +1;
           end if;
           RETURN(new_empno);
        END;
        / 

Now all you need to do is execute a liquibase update using SQL*Plus to deploy the package to the database.

Helpful tips

Here are some helpful tips to remember when you're crating changesets to run with SQL Plus:

• SQL Plus is used for updates and rollbacks for changesets where runWith=sqlplus.
• In XML changelogs, only the sql and sqlFile change types support running with SQLPlus.
• Do not set the endDelimiter property on SQL Plus changesets; SQL Plus handles this natively.
• Do not set the splitStatements=true property on SQL Plus changesets; SQL Plus handles statement splitting.
• Prevent queries from hanging indefinitely by configuring a SQL Plus timeout. In your liquibase.sqlplus.conf file, add liquibase.sqlplus.timeout=nn, where nn is time in seconds to wait before killing the process.
• Save the output of your SQL Plus spool files to your temp directory by adding liquibase.sqlplus.keep.temp=true to your liquibase.sqlplus.config file.

Learn more about using the SQL*Plus integration.

Summing it up

Our goal is to make database schema changes as easy as possible so you can concentrate on creating amazing features for your users. Since complex stored procedures can often be a pain, I hope that this integration saves countless hours and headaches as you migrate Oracle schemas. Let us know how it's working for you and how we can improve it. Contact us directly or reach out to us through social media (links in the footer).