What is CORT?

CORT is database DevOps tools which simplifies development, deployment and continuous integration of Oracle schema objects. It makes objects like tables, indexes, types and etc fully recreatable.

It allows:

  • To CREATE OR REPLACE TABLE without losing any data or depending objects (constraints, privileges, comments and etc.)
  • To make re-runnable creation of non-recreatable objects: TABLES, INDEXES, SEQUENCES, TYPES and etc.
  • To track and log CREATE OR REPLACE statements these and other objects like : PACKAGES, PROCESDURES, FUNCTIONS, VIEWS,TRIGGERS, TYPES, LIBRARIES, JAVA SOURCE, CONTEXT, SYNONYMS.
  • To group CREATE statements into builds.
  • 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.

So what kind of benefits you can get with CORT?

  • Simple and readable table definition using CREATE TABLE statement. You always see and edit actual table definition.
  • 100% native SQL working in any SQL tool and IDE. As result no Vendor lock-in.
  • You can perform any table modifications without additional coding or testing efforts.
  • CORT utilises standard security model – you don’t need to grant additional privileges to your user.
  • All CORT statements are completely rerunnable.
  • You get ability to revert DDL changes without DBA involvement.
  • 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.

Example 1 – Enabling create or replace table
create /*# or replace */ table demo_customers(
 customer_id number not null,
 cust_first_name varchar2(20) not null,
 cust_last_name varchar2(20) not null,
 cust_street_address1 varchar2(60),
 cust_street_address2 varchar2(60),
 cust_city varchar2(30),
 cust_state varchar2(2),
 cust_postal_code varchar2(10),
 cust_postal_code2 varchar2(10),
 phone_number1 varchar2(25),
 phone_number2 varchar2(25),
 credit_limit number(19,4),
 cust_email varchar2(30),
 constraint demo_cust_credit_limit_max
 check (credit_limit <= 5000) enable validate,
 constraint demo_customers_pk
 primary key (customer_id) enable validate
);
Example 2 – Enabling create or replace table
CREATE /*# OR REPLACE */ SEQUENCE DEMO_CUST_SEQ
 START WITH 21
 MAXVALUE 9999999999999999999999999999
 MINVALUE 1
 NOCYCLE
 CACHE 20
 NOORDER;
Example 3 – Enabling create or replace for indexes

For indexes it is triggered by API from cort_pkg package (see details in technical specification):

exec cort_pkg.begin_index_definition('DEMO_CUSTOMERS')
CREATE INDEX DEMO_CUST_NAME_IX ON DEMO_CUSTOMERS(CUST_LAST_NAME, CUST_FIRST_NAME);
CREATE INDEX DEMO_CUST_CL_IX ON DEMO_CUSTOMERS(CREDIT_LIMIT);
exec cort_pkg.end_index_definition
Configure TOAD to support CORT in explain plan

Use plan table from CORT schema

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.

CREATE /*# OR REPLACE */ TABLE simple_table(
  n number,
  s varchar2(10),
  d date,
  new_column varchar2(100)
)
/

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.

CREATE /*# OR REPLACE */ TABLE simple_table(
  n number,
  s varchar2(10),
  d date,
  new_column varchar2(100)
)
/

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.

CREATE /*# OR REPLACE */ TABLE simple_table(
  n number,
  s varchar2(10),
  d date,
  text clob --#= new_column
)
/

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:

CREATE /*# OR REPLACE */ TABLE simple_table(
  n number,
  s varchar2(10),
  d date,
  reg_time date not null, --#=nvl(d,sysdate)
  text XMLType --#=XMLType('<xml>'||text||'</xml>')
)
/

Downloads

Download full source code (Release 16.00)


Download