Oracle Extensions

Overview

Summary

Following tags for Oracle which are not supported by Liquibase Core were added:
1. AddCheck
2. AddDeferredPrimaryKey
3. RenameTrigger
4. CreateMaterializedView
5. CreateTrigger
6. DisableConstraint
7. DropCheck
8. DropTrigger
9. EnableConstraint
10. EncapsulateTableWithView
11. LongUpdate
12. Merge
13. Truncate
14. DropMaterizedView
15. EnableTrigger
16. DisableTrigger

Current Version

3.0

Author

Artur Kopacz, Damian Pezda, Łukasz Rejkowicz, Tomasz Wicherski, Nathan Voxland

Git Repository

https://github.com/liquibase/liquibase-oracle

Supported Database

Oracle

Usage

To use, simply include the oracle-extentions.jar file in your classpath. And add the ora namespace to your xml root node:

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ora="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

Available Commands/Tags

Add Check

Adds a check constraint to an existing table.

Sample
<ora:addCheck tableName="person"
	condition="id between 0 and 5"
	disable="true"
	deferrable="true"
	initiallyDeferred="true"
/>
Available Attributes

tableName

Name of the table to add check constraint to [required]

schemaName

Name of the table schema

tablespace

Specify the tablespace in which the check constraint is to be created

condition

True/false expression [required]

constraintName

Name of the check constraint

deferrable

Is check deferrable

initiallyDeferred

Is check initially deferred

disable

Is check disabled

validate

Is check validate

rely

Is check rely

Automatic Rollback Support : YES

Add Deffered Primary Key

Adds creates a deferred primary key out of an existing column or set of columns.

Sample
<ora:addDeferredPrimaryKey
	tableName="AddDeferredPrimaryKeyTest"
	columnNames="id"
	deferrable="true"
	initiallyDeferred="true"
	constraintName="PK_AddDeferredPrimaryKeyTest"
/>
Available Attributes

tableName

Name of the table to create the primary key on [required]

schemaName

Name of the table schema

columnNames

Name of the column(s) (comma separated if multiple) to create the primary key on [required]

constraintName

Name of primary key constraint [required]

deferrable

Set DEFERRABLE (deferrable="true") to indicate that in subsequent transactions you can use the SET CONSTRAINT clause to defer checking of this constraint until after the transaction is committed

initiallyDeferred

Set INITIALLY DEFERRED (initiallyDeferred="true") to indicate that Oracle should check this constraint at the end of subsequent transactions

Automatic Rollback Support : YES

RenameTrigger

Can disable, enable or rename trigger

Sample
<ora:renameTrigger triggerName="myTrigger" newName="RenamedMyTrigger"/>
Available Attributes

triggerName

Name of the trigger [required]

schemaName

Name of the schema

newName

New name of the trigger [required]

Automatic Rollback Support : NO

CreateMaterializedView

Creates new materialized view

Sample
<ora:createMaterializedView subquery="select * from Table1" viewName="myView"/>
Available Attributes

viewName

Name of the view [required]

subquery

Oracle Database executes this subquery and places the results in the materialized view [required]

columnAliases

You can specify a column alias for each column of the materialized view

objectType

The objectType clause lets you explicitly create an object materialized view of type objectType.

reducedPrecision

Specify to authorize the loss of precision that will result if the precision of the table or materialized view columns do not exactly match the precision returned by subquery, or to require that the precision of the table or materialized view columns match exactly the precision returned by subquery, or the create operation will fail

usingIndex

Specify "no" to suppress the creation of the default index

tableSpace

Specify the tablespace in which the materialized view is to be created

forUpdate

Specify FOR UPDATE to allow a subquery, primary key, object, or rowid materialized view to be updated

queryRewrite

Specify "enable" to enable the materialized view for query rewrite or "disable" to indicate that the materialized view is not eligible for use by query rewrite

Automatic Rollback Support : YES

CreateTrigger

Creates new trigger

Sample
<ora:createTrigger
	afterBeforeInsteadOf="before"
	procedure="DECLARE v_username varchar2(10); BEGIN SELECT pierwsza INTO v_username FROM TriggerTest; :new.created_by := v_username; END;"
	triggerName="myTrigger"
	tableName="TriggerTest"
	insert="true"
	forEachRow="true"
/>
Available Attributes

triggerName

Name of the trigger [required]

afterBeforeInsteadOf

Specify "before" to cause the database to fire the trigger before executing the triggering event
Specify "after" to cause the database to fire the trigger after executing the triggering event
Specify "insteadOf" to cause Oracle Database to fire the trigger instead of executing the triggering event
[required]

procedure

Specify the PL/SQL block that Oracle Database executes to fire the trigger or call a stored procedure rather than specifying the trigger code inline as a PL/SQL block [required]

schemaName

Name of the schema

replace

Re-create the trigger if it already exists

delete

Specify DELETE if you want the database to fire the trigger whenever a DELETE statement removes a row from the table or removes an element from a nested table

insert

Specify INSERT if you want the database to fire the trigger whenever an INSERT statement adds a row to a table or adds an element to a nested table.

update

Specify UPDATE if you want the database to fire the trigger whenever an UPDATE statement changes a value in any column of the table or nested table.

updateOf

Specify if you want the database to fire the trigger whenever an UPDATE statement changes a value in one of the columns specified in columnNames.

tableName

Name of the table

columnNames

Name of columns required to updateOf

whenCondition

Specify the trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger

nestedTableColumn

Specify the nested_table_column of a view upon which the trigger is being defined.

viewName

Name of the view

forEachRow

Specify to designate the trigger as a row trigger

Automatic Rollback Support : YES

Disable Constraint

Disables existing constraint.

Sample
<ora:disableConstraint tableName="test" constraintName="tom_check"/>
AvailableAttributes

constraintName

Name of the constraint to disable [required]

tableName

Name of the table to disable constraint to [required]

schemaName

Name of the table schema

tablespace

Specify the tablespace in which the constraint is to be disabled

Automatic Rollback Support : YES

Drop Check

Drops a check constraint from an existing table.

Sample
<ora:dropCheck tableName="test" constraintName="tom_check"/>
AvailableAttributes

constraintName

Name of the constraint to drop [required]

tableName

Name of the table to drop check constraint to [required]

schemaName

Name of the table schema

tablespace

Specify the tablespace in which the check constraint is to be dropped

Automatic Rollback Support : NO

DropTrigger

Removing the specific trigger

Sample
<ora:dropTrigger triggerName="myTrigger"/>

Available Attributes

triggerName

Name of the trigger [required]

schemaName

Name of the schema

Automatic Rollback Support : NO

Enable Constraint

Enables existing constraint.

Sample
<ora:enableConstraint tableName="test" constraintName="tom_check"/>
AvailableAttributes

constraintName

Name of the constraint to enable [required]

tableName

Name of the table to enable constraint to [required]

schemaName

Name of the table schema

tablespace

Specify the tablespace in which the constraint is to be enabled

Automatic Rollback Support : YES

EncapsulateTableWithView

Renames table named 'tableName' to 'TtableName' and creates view named 'tableName'.

Sample
<ora:encapsulateTableWithView tableName="person"/>
Available Attributes

tableName

Name of the table to encapsulate [required]

schemaName

Name of the table schema

Automatic Rollback Support : YES

LongUpdate

Performs long-running update in time intervals

Sample
<ora:longUpdate commitInterval="5" sleepSeconds="1" updateSql="UPDATE LongUpdateTest SET name='checked' where name='test'" />
Available Attributes

commitInterval

Number of updated rows in one commit [required]

sleepSeconds

Time between commits [required]

updateSql

Update procedure [required]

Automatic Rollback Support : NO

Merge

Selects rows from source table or view for update or insertion into a target table or view. You can specify conditions to determine whether to update or insert into the target table or view.
(from Oracle Database SQL Reference)

Example
<ora:merge targetTableName="myTable2 m"
	sourceTableName="myTable d"
	onCondition="m.pid=d.pid"
	insertColumnsValueList="d.pid,d.sales,'OLD'"
	updateList="m.sales=m.sales+d.sales,m.status=d.status"
	deleteCondition="m.status='OBS'"
/>
Available Attributes

sourceTableName

Name of the source table [required]

sourceSchemaName

Name of the source table schema

targetTableName

Name of the target table [required]

targetSchemaName

Name of the target table schema

onCondition

OnCondition clause [required]

updateCondition

UpdateCondition clause for update statement

insertCondition

InsertCondition clause for insert statement

deleteCondition

DeleteCondition clause for delete statement

updateList

'name=value' list separated by ',' for update statement

insertColumnsNameList

A list of columns names for insert statement

insertColumnsValueList

A list of columns values for insert statement

Automatic Rollback Support : NO

Truncate

Truncates all data from an existing table or cluster.

Sample
<ora:truncate tableName="truncatetest" purgeMaterializedViewLog="true" reuseStorage="true"/>
Available Attributes

tableName

Name of the table to truncate

clusterName

Name of the cluster to truncate

schemaName

Name of the table/cluster schema

purgeMaterializedViewLog

Whether a materialized view log defined on the table is to be purged when the table is truncated (from Oracle Database SQL Reference).

reuseStorage

Retain the space from the deleted rows allocated to the table or cluster (from Oracle Database SQL Reference).

Automatic Rollback Support : NO

DropMaterializedView

Removing the specific materialized view

Sample
<ora:dropMaterializedView viewName="myView"/>

Available Attributes

viewName

Name of the view [required]

schemaName

Name of the schema

Automatic Rollback Support : NO

EnableTrigger

Can enable trigger

Sample
<ora:enableTrigger triggerName="myTrigger"/>
Available Attributes

triggerName

Name of the trigger [required]

schemaName

Name of the schema

Automatic Rollback Support : YES

DisableTrigger

Can disable trigger

Sample
<ora:disableTrigger triggerName="myTrigger"/>
Available Attributes

triggerName

Name of the trigger [required]

schemaName

Name of the schema

Automatic Rollback Support : YES

SplitTable

Split table on two tables (with surrogate key only).

Sample
<changeSet author="Tomek" id="0">
   <createTable tableName="TABLE1">
      <column name="one" type="integer"></column>
      <column name="two" type="varchar(10)"></column>
      <column name="three" type="number"></column>
      <column name="four" type="number"></column>
   </createTable>
</changeSet>

<changeSet author="tomek" id="1" context="transition">
   <ora:splitTable primaryKeyColumnName="three_id"
   splitTableName="TABLE1"
   newTableName="TABLE2"
   columnNameList="three,four"/>
</changeSet>
Available Attributes

splitTableName

Name of the split table [required]

splitTableSchemaName

Name of the split table schema

newTableName

Name of the new table [required]

newTableSchemaName

Name of the new table schema

columnNameList

List of column to move to the second table [required]

primaryKeyColumnName

Name of primary key column in new table (only surrogate key) [required]

Automatic Rollback Support : NO
Rolling Upgrade Support : YES

splitTable tag supports rolling upgrades through changeSet contexts. There are 3 available contexts:

  • basic - no context, refeactoring without rolling upgrade
  • transition - move database to transition state (two version of aplication work on the same database)
  • resulting - move databese to resulting (final) state from transition state
WARNING
  • Liquibase 2.0 doesn't support numeric precission(e.g. NUMBER(10,3)).
  • createTrigger extension is needed.
NOTE

"rolling upgrade" - the process of incrementally taking down parts of the system for upgrade, without affecting the overall functionality. (http://en.wikipedia.org/wiki/Downtime)

Files

  File Modified

Java Archive liquibase-oracle-1.2.0.jar

Dec 20, 2010 by Nathan Voxland

Java Archive liquibase-oracle-3.0.0.jar

Sep 25, 2013 by Nathan Voxland

ZIP Archive db.changelog-bak.xml.zip

Jan 22, 2019 by Matteo Turra