Examples Using Liquibase

3.12 Examples Using Liquibase

Some examples of using the Liquibase functionality for database change management tasks:

3.12.1 Review SQL

To review SQL before running maintenance commands:

Optionally, set up to save SQL updates.

 cd <lb-changes-directory>
spool update.sql

Connect to HR and capture the object.

connect <db-connect1-string>
lb update-sql
spool off

3.12.2 Capture and Deploy an Object

To deploy the EMPLOYEES table from HR to HR2:

Set default output path.
```
cd <output-files-path>
```

Connect to HR and capture the object.

connect <db-connect1-string>
lb generate-object -object-type table -object-name employees

Connect to HR2 and ensure the object does not exist.
```
connect <db-connect2-string>
drop table employees
```

Create the object in HR2 and verify that it was created.

lb update -changelog-file employees_table.xml
desc employees

3.12.3 Capture and Deploy a Schema

To capture HR schema and reproduce it in HR2 schema:

Set default output path.
```
cd <output-files-path>
```

Connect to HR and capture the schema.

connect <db-connect1-string>
lb generate-schema

Setup the HR2 user.

connect <db-connect-dba-string>
drop user hr2 cascade;
create user hr2 identified by hr2;
grant connect,resource, create view to hr2;
alter user hr2 quota unlimited on users;
alter user hr2 quota unlimited on sysaux;

Create the schema objects deployed from HR in HR2 and verify.
```
lb update -changelog-file controller.xml
tables
```

3.12.4 Execute Custom SQL with RunOracleScript

Create a RunOracleScript changeset to create a table and use PL/SQL variables in the script.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
   <changeSet id="runScriptString" author="jdoe">
<n0:runOracleScript objectName="myScript" ownerName="JDOE"
sourceType="STRING">
<n0:source><![CDATA[
DEFINE table_name = RUNNERSTRING;
create table &&table_name (id number);
]]></n0:source>
    </n0:runOracleScript>
  </changeSet>
</databaseChangeLog>