CORT

What is CORT?

CORT is database deployment and continuous integration tool integrated into database server.

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 and SEQUENCES.
  • To track and log CREATE OR REPLACE statements for : 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 designed for Oracle database 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 utilize existing database behavior – evolutional DDL changes. In most cases they are limited with classic CREATE new table or ALTER existing table. We already covered limitations of ALTER statement.

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 handle it – it provides special hints to manipulate with data during table recreation. So you can initialize 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 utilizes standard security model – you don’t need to grant additional privileges to your user.
  • All CORT statements are completely re-runnable.
  • 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.
  • You can use CORT for CI – you get ability install changes and revert full set of applied changes in case of error.

So you have rerunnable and revertable DDL using native syntax allowing to apply any changes to table without losing any data and without writing any additional bespoke code.

How it works

CORT is server-size component developed on PL/SQL language. It is embedded into CREATE command and controlled by prefixed by # symbol comments – cort-hints. It works by analogy ti Oracle optimizer hints – instructions which are not part of command but they control its behavior.
For tables and sequences new functionality is triggered automatically when #OR REPLACE cort-hint is specified right after “CREATE” keyword.

Example 1 – Enabling create or replace table

CREATE /*# OR REPLACE no_data */ 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 sequence

CREATE /*# OR REPLACE */ SEQUENCE DEMO_CUST_SEQ
  START WITH 21
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

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

Example 3 – Enabling create or replace for indexes

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

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.

Toad options

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.

Toad explain plan

Add your comment

Your email address will not be published. Required fields are marked *