Liquibase The filename, directory name, or volume label syntax is incorrect

1. User's Guide
2. Liquibase Support in SQLcl

3.1 About Liquibase in SQLcl

Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes.

For an understanding of the major concepts in Liquibase, see Major Concepts.

Note:

Liquibase is not available in SQLcl that is part of the SQL Developer installation. To use this feature, you need to download the standalone SQLcl offering.

The Liquibase feature in SQLcl enables you to execute commands to generate a changelog for a single object or for a full schema [changeset and changelogs]. You can process these objects manually using SQLcl or through any of the traditional Liquibase interfaces.

With the Liquibase feature in SQLcl, you can:

• Generate and execute single object changelogs
• Generate and execute schema changesets with object dependencies
• Automatically sort a changeset during creation based on object dependencies
• Record all SQL statements for changeset or changelog execution, as it is generated
• Provide full rollback support for changesets and changelogs automatically

3.2 Requirements for Using Liquibase

The two important aspects for using the Liquibase functionality are capturing and deploying objects in an Oracle database.

Capture Objects

To capture an object or a schema, you must have SQLcl 19.2 or later installed.

In this release, you can only capture objects from the schema you are connected to in SQLcl. You also need write permission on the directory in which you save the files.

If you are capturing an entire schema, the user you are connected to must have the privilege to create a table. The DATABASECHANGELOG_EXPORT table is created internally to gather object details and sort them correctly. The created object is automatically excluded from the capture process and destroyed upon capture completion.

Deploy Objects

Liquibase uses the DATABASECHANGELOG table to track the changesets that have been run. The DATABASECHANGELOGLOCK table ensures that only one instance of Liquibase is running at a time. The DATABASECHANGELOG_ACTIONS table tracks the object state and the SQL statements executed during deployment.

• SQLcl

  Deploying changes to any database through SQLcl requires the 19.2 release or later and the privilege to create a table. You must have necessary permissions to create any object type through the change that you are deploying.

• Liquibase

  If you use Liquibase directly to deploy changesets, you need to have:

  • the extension installed in your Liquibase environment. Add the dbtools-liquibase.jar file in liquibase/lib/ext.
  • the privileges to create a table and a package.

3.3 Supported Types

DDL types use create or replace syntax. A snapshot of the object is taken before applying the change so automatic rollback to the last known state is supported.

SXML types support automatic alter generation with automatic rollback support.

DDL types have their own change type.

• CONSTRAINT
• DIMENSION
• DIRECTORY
• FUNCTION
• JOB
• OBJECT_GRANT
• PACKAGE_BODY
• PACKAGE_SPEC
• PROCEDURE
• PUBLIC_SYNONYM
• REF_CONSTRAINT
• SYNONYM
• TRIGGER
• TYPE BODY
• TYPE SPEC

SXML types share the SXML change type.

• AQ_QUEUE
• AQ_QUEUE_TABLE
• AQ_TRANSFORM
• ASSOCIATION
• AUDIT
• AUDIT_OBJ
• CLUSTER
• CONTEXT
• DB_LINK
• DEFAULT_ROLE
• FGA_POLICY
• JOB
• LIBRARY
• MATERIALIZED_VIEW
• MATERIALIZED_VIEW_LOG
• OPERATOR
• PROFILE
• PROXY
• REFRESH_GROUP
• RESOURCE_COST
• RLS_CONTEXT
• RLS_GROUP
• RMGR_CONSUMER_GROUP
• RMGR_INTITIAL_CONSUMER_GROUP
• RMGR_PLAN
• RMGR_PLAN_DIRECTIVE
• ROLE
• ROLLBACK_SEGMENT
• SEQUENCE
• TABLE
• TABLESPACE
• TRIGGER XS_ACL
• TRUSTED_DB_LINK
• USER
• VIEW
• XMLSCHEMA
• XS_ACL_PARAM INDEX
• XS_DATA_SECURITY
• XS_ROLE
• XS_ROLESET
• XS_ROLE_GRANT
• XS_SECURITY_CLASS
• XS_USER

3.4 Supported Liquibase Commands in SQLcl

You can invoke the Liquibase commands in SQLcl with liquibase or lb.

3.4.1 LB GENOBJECT

Generates change logs for APEX, ORDS RESTful Service Modules, or database objects.

APEX

Syntax

Generates a change log for APEX objects.

Named Parameters

Common Generator Parameters

Example

ORDS RESTful Service Modules

Syntax

Generates a change log for an ORDS [Oracle REST Data Services] RESTful service module.

Named Parameters

Common Generator Parameters

Database Object

Syntax

Generates a change log for a specified database object.

Named Parameters

Common Generator Parameters

Database Object Types Supported

Example

3.4.2 LB GENSCHEMA

Generates changelogs and controller file for the connected schema.

Syntax

Captures the entire schema that the user is connected to in SQLcl. An XML file is created in the current working directory for each object in the schema, and a controller.xml file is created. The controller file is a change log that includes all files in the proper order to enable the schema to be deployed correctly.

Named Parameters

Common Generator Parameters

Example

3.4.3 LB GENCONTROLFILE

Generates a blank controller.xml as a sample.

Syntax

Creates an empty changelog, master.xml, with a placeholder to include files in the current working directory. You can use this command when you are creating your own changelog with custom changeset inclusions.

Example

3.4.4 LB UPDATE

Applies the specified change log using the current connection.

Syntax

Enables you to apply database changes that you and other developers have added to the change log file.

Named Parameters

Common Update Parameters

Example

3.4.5 LB UPDATESQL

Generates SQL to update the database to the current version.

Syntax

Generates and renders to the screen the SQL statements that would be applied for a specific change log.

Named Parameters

Common Generator Parameters

Example

3.4.6 LB ROLLBACK

Rolls back the state requested.

Syntax

Rolls back changes starting from the last change applied using the input change log. The count can be higher than the changes in the change log. 999, which is the maximum size, rolls back all the changes.

Named Parameters

Rollback Style Options

Select one of the following options:

Common Update Parameters

Example

3.4.7 LB ROLLBACKSQL

Writes SQL to roll back the database to the state requested.

Syntax

Named Parameters

Rollback Style Options

Select one of the following options:

Common Update Parameters

Example

3.4.8 LB DIFF

Writes a description of differences between two databases to standard output.

Syntax

Displays differences between the current connection and the specified database. When the report is true, the output will be in the form of a text report. When the report is false, which is the default, the output will be in the form of a change log.

The destination URL format is HOST:PORT:SID or HOST:PORT/SERVICE.

Named Parameters

Example

3.4.9 LB DBDOC

Generates Javadoc-like documentation based on the current database and change log.

Syntax

Named Parameters

Common Update Parameters

Example

Figure 3-1 shows the index page that is created by the DBDOC command. It enables you to navigate the various details of the changelog used to produce the document.

3.4.10 LB CHANGELOGSYNC

Writes the SQL statements to mark all changes in the change log as executed in the database to a file.

Syntax

Named Parameters

Example

3.4.11 LB CLEARCHECKSUMS

Removes current checksums from the database. For the next update, changesets that have already been deployed will have their checksums recomputed, and changesets that have not been deployed will be deployed.

Syntax

Named Parameters

Example

3.4.12 LB LISTLOCKS

Lists who currently has locks on the database change log.

Syntax

Named Parameters

Example

3.4.13 LB RELEASELOCKS

Releases all locks on the database change log.

Syntax

Named Parameters

Example

3.4.14 LB STATUS

Outputs a list of change sets that have not been applied.

Syntax

Checks the status of the change log using the current connection. This shows if the change log has been applied and the result of the change log.

Named Parameters

Example

3.4.15 LB VALIDATE

Checks the changelog for errors.

Syntax

Named Parameters

Example

3.4.16 LB VERSION

Display product version information.

Syntax

Example

3.4.17 LB HELP

Lists the available Liquibase commands.

Syntax

Type a command with no options for help on a specific command.

Example

3.5 DATABASECHANGELOG_DETAILS VIEW

DATABASECHANGELOG_DETAILS is a view that consolidates information from the DATABASECHANGELOG and DATABASECHANGELOG_ACTIONS tables for easier reporting and troubleshooting.

This view enables a better understanding of the status and work performed by each change applied to the database. The SQL column shows the actual SQL that was run in the database. The SXML column shows the state of the object prior to the change being applied.

Example

3.6 ChangeSets in Liquibase

The following table lists the changeSets and provides a description for each of them. To learn more about changeSets, see tag.

3.7 Examples Using Liquibase

3.7.1 Capture and Deploy an Object

To deploy the EMPLOYEES table from HR to HR2:

Note:

The syntax for the GENOBJECT command has changed. See LB GENOBJECT

1. Connect to HR.

   SQL> connect hr/hr Connected.

2. Capture the object.

   SQL> lb genobject -type table -name employees Action successfully completed please review created file employees_table1.xml

3. Connect to the other user.

   SQL> connect hr2/hr2 Connected.

4. Ensure the object does not already exist.

   SQL> drop table employees Table EMPLOYEES dropped.

5. Create the object in the current schema.

   Note:

   As the schema name has changed, you must set include schema to false or it will try and create the object in the hr schema.

   SQL> lb update -changelog employees_table.xml ScriptRunner Executed:/Users/jdoe/Documents/lbtest/v2/employees_table.xml::6486f968-93fe-4e1c-ac59-17ef392e1423::Generated

6. Verify the object was created.

   SQL> desc employees ---- ----- ------ EMPLOYEE_ID NOT NULL NUMBER[6] FIRST_NAME VARCHAR2[20] LAST_NAME NOT NULL VARCHAR2[25] EMAIL NOT NULL VARCHAR2[25] PHONE_NUMBER VARCHAR2[20] HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2[10] SALARY NUMBER[8,2] COMMISSION_PCT NUMBER[2,2] MANAGER_ID NUMBER[6] DEPARTMENT_ID NUMBER[4]

3.7.2 Capture and Deploy a Schema

To capture the HR schema and reproduce it in the HR2 schema:

3.7.3 Generate the Master Control File

SQL> lb gencontrolfile Action successfully completed please review created file controller.xml SQL> !more controller.xml

3.7.4 Capture and Deploy a Schema and then Upgrade it and Redeploy

The steps are:

1. Migrate the HR schema to the HR2 schema, then modify the HR schema and update HR2.

   setup the hr2 user -- 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; SQL> drop user hr2 cascade; User HR2 dropped. SQL> create user hr2 identified by hr2; User HR2 created. SQL> grant connect,resource, create view to hr2; Grant succeeded. SQL> alter user hr2 quota unlimited on users; User HR2 altered. SQL> alter user hr2 quota unlimited on sysaux; User HR2 altered. SQL> connect hr2/hr2 Connected. SQL> !mkdir v1 SQL> cd v1 SQL> connect hr/hr Connected. SQL> lb genschema [Type - TYPE_SPEC]: 185 ms [Type - TYPE_BODY]: 84 ms [Type - SEQUENCE]: 41 ms [Type - CLUSTER]: 38 ms [Type - TABLE]: 702 ms [Type - MATERIALIZED_VIEW_LOG]: 20 ms [Type - MATERIALIZED_VIEW]: 10 ms [Type - VIEW]: 85 ms [Type - REF_CONSTRAINT]: 142 ms [Type - DIMENSION]: 21 ms [Type - FUNCTION]: 45 ms [Type - PROCEDURE]: 52 ms [Type - PACKAGE_SPEC]: 30 ms [Type - DB_LINK]: 18 ms [Type - SYNONYM]: 30 ms [Type - INDEX]: 125 ms [Type - TRIGGER]: 31 ms [Type - PACKAGE_BODY]: 39 ms [Type - JOB]: 19 ms [Method loadCaptureTable]: 1717 ms [Method parseCaptureTableRecords]: 6243 ms [Method sortCaptureTable]: 42 ms [Method createExportChangeLogs]: 1 ms Export Flags Used: Export Grants false Export Synonyms false

2. Modify the HR schema.

   SQL> ALTER TABLE DEPARTMENTS ADD [COLUMN1 VARCHAR2[20] ]; Table DEPARTMENTS altered. SQL> ALTER TABLE COUNTRIES ADD [COLUMN1 VARCHAR2[20] ]; Table COUNTRIES altered. SQL> ALTER TABLE LOCATIONS ADD [COLUMN1 VARCHAR2[20] ]; Table LOCATIONS altered. SQL> CREATE OR REPLACE PROCEDURE PROCEDURE2 [PARAM1 IN VARCHAR2,PARAM2 IN VARCHAR2,PARAM3 IN VARCHAR2, PARAM4 IN VARCHAR 2] AS BEGIN NULL; END PROCEDURE2; Procedure PROCEDURE2 compiled

3. Make a change to the v2 directory.

   SQL> cd .. SQL> !mkdir v2 SQL> cd v2

4. Export v2 of the HR schema.

   SQL> lb genschema [Type - TYPE_SPEC]: 102 ms [Type - TYPE_BODY]: 74 ms [Type - SEQUENCE]: 26 ms [Type - CLUSTER]: 25 ms [Type - TABLE]: 534 ms [Type - MATERIALIZED_VIEW_LOG]: 13 ms [Type - MATERIALIZED_VIEW]: 10 ms [Type - VIEW]: 78 ms [Type - REF_CONSTRAINT]: 119 ms [Type - DIMENSION]: 15 ms [Type - FUNCTION]: 44 ms [Type - PROCEDURE]: 46 ms [Type - PACKAGE_SPEC]: 25 ms [Type - DB_LINK]: 14 ms [Type - SYNONYM]: 24 ms [Type - INDEX]: 100 ms [Type - TRIGGER]: 25 ms [Type - PACKAGE_BODY]: 34 ms [Type - JOB]: 14 ms [Method loadCaptureTable]: 1322 ms [Method parseCaptureTableRecords]: 5452 ms [Method sortCaptureTable]: 37 ms [Method createExportChangeLogs]: 0 ms Export Flags Used: Export Grants false Export Synonyms false

   To import v1, change back to the v1 directory and connect as HR2.

   SQL> cd .. SQL> connect hr2/hr2 Connected.

5. Now import the capture into the HR2 schema.

   SQL> lb update -changelog controller.xml ScriptRunner Executed:person_typ_type_spec.xml::f9d8cb37f8030674b1d0060b054254a28f96dd51::Generated ScriptRunner Executed:emp_person_typ_type_spec.xml::cabf96af49def8fc164a6c0aa7be69c1a78d46c4::Generated ScriptRunner Executed:people_typ_type_spec.xml::6a98e2a2ec56a7ca6b716a114804081f8760ec78::Generated ScriptRunner Executed:dept_persons_typ_type_spec.xml::e250a6757e4e023a30c1b9f458149e4dfa0ad6bc::Generated ScriptRunner Executed:employee_type_type_spec.xml::aa2ef7c172e6df08e4082a4f139f4c6edf6e14bc::Generated ScriptRunner Executed:person_typ_type_body.xml::bd29fd1d0827b70ae4b644781eeb9a69f7caf956::Generated ScriptRunner Executed:departments_seq_sequence.xml::ea03838db9dc0613a5128090c9388ff1b8dc8a1a::Generated ScriptRunner Executed:locations_seq_sequence.xml::2aba6107a95a977632161be9ca920bdb836f18fc::Generated ScriptRunner Executed:employees_seq_sequence.xml::2379ab3cbd7f84f52f6596fa16479bb6ccf721ca::Generated ScriptRunner Executed:countries_table.xml::8e87e94fd74b172dc76d950216e057827e2e3f83::Generated ScriptRunner Executed:countries_comments.xml::8797a740e38cf3ef7da36b6ba4aa5497cfd6b457::Generated

6. Check a table to verify.

   SQL> show user USER is "HR2" SQL> desc departments Name Null? Type __________________ ___________ _______________ DEPARTMENT_ID NOT NULL NUMBER[4] DEPARTMENT_NAME NOT NULL VARCHAR2[30] MANAGER_ID NUMBER[6] LOCATION_ID NUMBER[4]

7. Switch to v2 and import the capture.

   SQL> cd v2 SQL> lb update -changelog controller.xml ScriptRunner Executed:countries_table.xml::31a15fa402822ae67ae588a74e2b0d9506f1dc26::Generated ScriptRunner Executed:locations_table.xml::9548f26f7f8b53bc3639869e23b118b4fe09363b::Generated ScriptRunner Executed:departments_table.xml::560727e73ed902cd0ad3e25793987d323580b823::Generated ScriptRunner Executed:emp_email_uk_index.xml::99a0278c50a75912985023c6a075514ec939d233::Generated ScriptRunner Executed:secure_employees_trigger.xml::cfe2f3f3443973ee9d43f4cc77701d211c6023ba::Generated ScriptRunner Executed:update_job_history_trigger.xml::aa8e35e3de04b9bb0ef3c47885f175e20be6c59e::Generated

8. Verify that you are on v2.

   SQL> desc departments Name Null? Type __________________ ___________ _______________ DEPARTMENT_ID NOT NULL NUMBER[4] DEPARTMENT_NAME NOT NULL VARCHAR2[30] MANAGER_ID NUMBER[6] LOCATION_ID NUMBER[4] COLUMN1 NUMBER

3.7.5 Execute Custom SQL with RunOracleScript

Create a RunOracleScript changeset where the source can be a script, a link to a file, or a URL. The changeset in this example shows how to create a table and use PL/SQL variables in the script.

#### SCRIPT - STRING EXAMPLE #### ---------------------------------------------------------------------------------------------------------------------------------------------------- SQL> tables no rows selected SQL> lb update -changelog stringscript.xml ScriptRunner Executed:stringscript.xml::runScriptString::jdoe SQL> tables TABLES ____________________________ DATABASECHANGELOGLOCK DATABASECHANGELOG DATABASECHANGELOG_ACTIONS RUNNERSTRING

#### SCRIPT - FILE EXAMPLE #### filescript.xml ---------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------- scriptFILE.sql ------------------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE RUNNERFILE [id number] ------------------------------------------------------------------------------------------------------------------------------------------- SQL> tables TABLES ____________________________ DATABASECHANGELOGLOCK DATABASECHANGELOG DATABASECHANGELOG_ACTIONS RUNNERSTRING SQL> lb update -changelog filescript.xml ScriptRunner Executed:/Volumes/SSD/work/sqlcl/filescript.xml::RunScriptFile::jdoe SQL> tables TABLES ____________________________ DATABASECHANGELOGLOCK DATABASECHANGELOG DATABASECHANGELOG_ACTIONS RUNNERSTRING RUNNERFILE

#### SCRIPT - URL EXAMPLE #### urlscript.xml ---------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------- scriptURL.sql ------------------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE RUNNERURL [id number] ------------------------------------------------------------------------------------------------------------------------------------------- SQL> tables TABLES ____________________________ DATABASECHANGELOGLOCK DATABASECHANGELOG DATABASECHANGELOG_ACTIONS RUNNERSTRING RUNNERFILE SQL> lb update -changelog urlscript.xml ScriptRunner Executed:/Volumes/SSD/work/sqlcl/urlscript.xml::RunScriptUrl::jdoe SQL> tables TABLES ____________________________ DATABASECHANGELOGLOCK DATABASECHANGELOG DATABASECHANGELOG_ACTIONS RUNNERSTRING RUNNERFILE RUNNERURL