To revert individual CREATE statements, including structure changes in tables.

To revert entire builds.

CORT is integrated into Oracle database server and written on pure PL/SQL language.

Why is CORT?

There are lots of deployment and CI tools on the market – including open source projects but most of them offer some solution outside of the database working as a client. They utilise existing database behaviour – incremental DDL changes. In most cases they are limited with classic CREATE new table or ALTER existing table.

CORT acts differently. It offers new approach for database schema upgrades – through aggregation changes. It performs table recreation in the background doing all hard work for you:

Comparing structure of creating table with existing one

Applying changes using most efficient way

Logging changes

With CORT you make all changes in table definition by modifying original create statement same as we change procedural code. Of course table changes are not the same as procedure changes because of the data. But CORT will handle it – it provides special hints to manipulate with data during table recreation. So you can populate newly added not null column with some values using any SQL expression without limits as in using DEFAULT, or you can modify data in existing column when you change its data type.

You can see all changes before their execution using standard output or plan_table.

You can control what is executed by using CORT parameters.

How it works

CORT is server-side component developed on PL/SQL language. It is embedded into SQL engine and triggered by using cort-hints – prefixed by # symbol comments placed straight after CREATE keyword. It works by analogy to Oracle optimizer hints – instructions which are not part of the command but they control its behaviour. CORT intersects default execution of command and perform number of checks, comparison and operations to determine and apply necessary changes in the most optimal way.

When CORT is triggered it captures current object definition from the database and compares it to what is going to be created with the current statement. If no changes have been found then CORT exists without any errors. For tables CORT tries to apply changes using ALTER table if it’s possible and efficient. If not then CORT recreates table, restore data and depending objects.

For recreatable objects (packages, views and etc) CORT is triggered only in current session with cort-build is started (see cort-build). It captures and logs object’s source code to be able to revert it to previous state

CORT has number of parameters which could be modified permanently, on statement level or on session level. To change parameter values permanently or for session level use PL/SQL API, for specifying it only for particular statement cort-hints are used.

To control data modification during table recreation CORT support hint assigned individually to every column – cort-values. Cort-value is a comment prefixed with # value and started with = or == symbol following by any SQL expression. It needs to be defined after column name which it references to and before next column/constraint definition. Cort-value could optionally have release which it assigns with specified between # and =symbols.

CORT has 2 sets of predefined default values: for development and for build modes. Development mode is default, build mode get activated when cort-build is started. In development mode when table is recreated CORT tries to restore all dependent objects and reference keys. But when build is started then CORT optimize it work assuming that all dependent objects and references will be recreated later in the build (see deployment approach).

As any tool doing some job in the background it need to provide monitoring and control functionality to let developer know what’s going on. CORT gives functionality of echoing change without execution. This is called test mode and controlled by TEST cort-hint or TEST cort session-parameter. Just add TEST hint or set TEST session parameter to TRUE and then execute CORT statement – you will see DDL commands in output but object remains unchanged.

Alternatively you can use CORT plan_table. This feature was designed for IDE and works in Toad and Oracle SQL Developer tools. Just modify settings in you IDE to use plan_table from CORT schema.

Configure TOAD to support CORT in explain plan

Use explain plan feature

Select your CORT statement and press button for “explain plan” (ctlr+E in Toad or F10 in SQL Developer) and you will see DDL commands in explain plan window.

Sample use cases

Let’s consider several practical use cases for table modification with CORT. We have table SIMPLE_TABLE with some data:

CREATE TABLE simple_table(
n number,
s varchar2(10),
d date
)
/

1. Adding new column:

To add new nullable column simply define it at any position as it would be first table creation. Bear in mind if you place order at the end of list then CORT will user fast ALTER TABLE operation. Otherwise table will be recreated.

1. Adding new column:

To add new nullable column simply define it at any position as it would be first table creation. Bear in mind if you place order at the end of list then CORT will user fast ALTER TABLE operation. Otherwise table will be recreated.

3. Renaming column:

CORT support explicit and implicit column renames. Explicit rename is changing column name and specifying old name in cort-value. Implicit rename is when cort-value is missing. It is working only if renaming column is defined on the same physical position and with the same data type. It is useful in development mode when you need to quick rename column without any other changes. It is strongly recommended to use explicit rename in your code as it always has same outcome.

4. Change with modifying data:

CORT support explicit and implicit column renames. Explicit rename To add prepopulated column and migrate data in change existing column simply edit it’s definition it CREATE TABLE statement. In most cases it will lead to table recreation: