CORT

The Problem

ALTER TABLE

PROS: CONS:
  • Compact DDL
  • Fast execution (if data population is not required)
  • Does not require extra space
  • Not every change could be done through ALTER
  • Changes are not re-runnable and in most cases are not rollbackable
  • Updates for new columns run slowly

RECREATION (create + insert as select + rename + drop)

PROS: CONS:
  • Rollbackable (but with loosing the latest data changes)
  • Better performance for data population (insert runs faster than update for all table)
  • Easy to modify
  • Requires lots of coding (restoring data and depending objects: references, privileges, synonyms, triggers, indexes and etc)
  • Requires aggregated DDL of current version
  • Every change within release requires full table repopulation

Solution

What if SQL supported CREATE OR REPLACE for tables?

  • It would be re-runnable
  • It would be much less coding
  • It would be aggregated
  • It would be faster and simpler
  • It would be less problems

… and this is possible with new code management tool CORT – just create or replace table

What is CORT?

CORT is Oracle server-side tool allowing to change tables similar to create or replace command.

The main aim of this tool is to simplify development, deployment and continuous integration (CI) processes of Oracle Database Schema objects – tables first of all. With CORT you do not need any more to write lots of code for table recreation, data migration and for restoring dependent objects like constraints and indexes. Simply define table structure as if it would be its first creation, specify mapping values for mandatory added/changed columns and CORT will take care about everything else. Using CORT you always have aggregated and up-to-date table definition which easy to store under version control, modify, support and compare with previous versions. 

CORT allows to rollback table structure and data to previous state with single command. In addition CORT statement is 100% re-runnable – it will not throw exceptions “table already exists”.

CORT is very flexible and customizable tool. It has number of setting parameters which control table change process. With setting parameters you can control of data mapping to individual columns, validation of constraints, logging, echoing, parallel degree and much more.

CORT Benefits

  • Simple table definition – you don’t need to worry about is your change implementable through ALTER TABLE, ALTER TABLE MOVE or CREATE TABLE.
  • 100% valid native SQL – you define table structure using native Oracle SQL syntax. As result no vendor lock-in.
  • No need to call dynamic SQL or PL/SQL – main functionality is embedded into CREATE DDL command and triggered automatically as soon you specify #OR REPLACE hint.
  • Work in any existing SQL tool and IDE – CORT is server-side database component.
  • Utilizing standard security model – CORT executes all statements with privileges of the current user even if they granted through the role.
  • Aggregated schema deployment – you don’t need to support two versions of scripts: delta and aggregated. You do not need to compare schemas, grab current table DDL from production and apply reverse engineering to update your ER-diagram
  • Continuous integration – CORT allows to implement atomic changes in sequence and deploy them independently.
  • Ability to rerun entire SQL without errors – CORT compares executing SQL with current table structure and applies only actual changes. It does not raise exception it there are no changes.
  • Ability to rollback changes – CORT allows to rollback changes in table structure and hence minimizes DBAs involvement in testing process.
  • Ability to see change scripts before execution – you can check all SQL commands executed by CORT in the background. You can call CORT statement without actual execution but with displaying SQL commands.
  • Ability to control recreate behaviour with custom parameters – Developer can use parameters to ignore physical attributes, specify to use MOVE clause, choose to ALTER or RECREATE table and etc.

How it works

CORT is developed on pure PL/SQL language. New functionality is embedded into CREATE DDL command and controlled though special “CORT-hints”. It is standard SQL comments prefixed with specially symbol “#” (hash). It works by analogy to Oracle optimizer hints – instructions which are not part of command but they control its behaviour. To enable CORT functionality for given object it is enough to specify #OR REPLACE hint immediately after keyword CREATE (see example 1). CORT checks if table already exists and compares executable SQL with existing table structure. If there are any changes CORT decides how to implement them: by altering existing table or by replacing table with new copy. Because CORT compares not only table itself but constraints as well it is recommended to declare all constraints and log groups inside table definition and avoid any ALTER TABLE statements at all because they are not handled by CORT.

CORT always tries to implement all changes the most optimal and efficient way. Table recreation is treated as least priority. So decision depends on several factors:

  • Is it possible to apply change with ALTER?
  • If yes, is it possible to rollback it (without loosing existing data)?
  • If not, is it possible to keep data by using exchange partition operation?

If none of this methods is available then CORT moves data using INSERT /*+ APPEND */ INTO … SELECT … and moves all depending objects: constraints, log groups, indexes, references, triggers , privileges, comments and policies. Then it renames old table to CORT-generated name (prefixed “rlbk#”) and renames new table from temporary name (prefixed with ~tmp#) to real one. By default CORT tries to complete this operation by restoring all depending objects so this change could be done without errors as a standalone change. If some constraints or references could not be restored or validated then CORT raises exception and rollbacks this change. However default behaviour is controlled by parameters and developers can change parameter values with following CORT-hints:

Parameter Type Hint for enabling Hint for disabling Default value Purpose
alias VARCHAR2(30) ALIAS = <alias> n/a A Alias for source table. Could be used in cort-hint expressions.
parallel INTEGER PARALLEL = <degree > NO_PARALLEL NULL Parallel degree for recreation SQL and DDL operations. Overrides (but does not change) object and session parallel degree.
debug BOOLEAN DEBUG NO_DEBUG FALSE Debug output (for testing only)
echo BOOLEAN ECHO NO_ECHO FALSE Output of executing SQL commands
log BOOLEAN LOG NO_LOG FALSE Logging executing commands into CORT_LOG table
test BOOLEAN TEST NO_TEST FALSE Just display but do not apply changes
force_recreate BOOLEAN ALTER NO_ALTER FALSE Forcing table recreating
force_move BOOLEAN MOVE NO_MOVE FALSE Forcing to use MOVE option when it is possible when altering
rollback BOOLEAN ROLLBACK NO_ROLLBACK TRUE Use ALTER option only if it is possible to rollback.
physical_attr BOOLEAN PHYSICAL_ATTRIBUTES NO_PHYSICAL_ATTRIBUTES TRUE Compare physical attributes
keep_data BOOLEAN KEEP_DATA NO_DATA TRUE Keep data when recreating
keep_refs BOOLEAN KEEP_REFS NO_REFS TRUE Keep references from another table when recreating
validate_refs BOOLEAN VALIDATE NO_VALIDATE TRUE Validate references when recreating
bad_refs BOOLEAN KEEP_BAD_REFS NO_BAD_REFS TRUE Raise exception if reference could not be restored or ignore it when recreating
keep_privs BOOLEAN KEEP_PRIVS NO_PRIVS TRUE Keep privileges when recreating
keep_indexes BOOLEAN KEEP_INDEXES NO_INDEXES TRUE Keep indexes when recreating
keep_triggers BOOLEAN KEEP_TRIGGERS NO_TRIGGERS TRUE Keep triggers when recreating
keep_comments BOOLEAN KEEP_COMMENTS NO_COMMENTS TRUE Keep comments when recreating
keep_stats BOOLEAN KEEP_STATS NO_STATS TRUE Keep statistic when recreating
keep_partitions BOOLEAN KEEP_PARTITIONS NO_PARTITIONS FALSE Keep existing partitions when recreating
keep_subpartitions BOOLEAN KEEP_SUBPARTITIONS NO_SUBPARTITIONS FASLE Keep existing subpartitions when recreating
keep_temp_table BOOLEAN KEEP_TEMP_TABLE DROP_TEMP_TABLE FALSE Keep temp table (for testing only)

Parameter hints could be specified in between the hint #OR REPLACE  and the following keyword (see example 2). They override session level parameter values for current object only.

Syntax:

/*# OR REPLACE <cort-hint1> ... <cort-hintN> */

To add new or modified existing column CORT provides “cort-values” – special cort-hints started with equal followed by sql-expression. Cort-values are specified for every column individually and contain SQL expression for data transformation in the column. Cort-values could be specified in any place between brackets enclosed column definition. Cort-value belongs to the nearest column placed before its definition (see example 3). For example, to assign cort-value to column ABC it need to be placed somewhere between column name ABC and before next column name (or closing bracket if ABC is the last column). If column has not been changed then cort-value will be ignored. However, to modify data without modifying column definition and force using cort-value it need to be defined with double equal sign followed by # symbol (see example 4).

Syntax:

/*#=<sql_expression> */
/*#==<sql_expression> */

SQL expression is terminated by the end of the comment. Do not include any additional text into the same comment. If SQL expression is not valid then cort-value is ignored and corresponded warning will be displayed through dbms_output pipe. If several cort-values are belong to the same column then the last one will be used.

CORT modifies columns using following algorithm:

  • If column is new then
    • If cort-value (#= or #==) is defined then it will be used
    • If column default value is defined then it will be used
    • Otherwise NULL value will be used.
  • If column is modified
    • If forced cort-value (#==) is defined then it will be used
    • If regular cort-value (#=) is defined and column data type has been changed then cort-value will be used
    • If column type has been changed then default CORT type conversion will be used (see following table)
    • Otherwise original value will be kept

CORT data type transformation matrix

CORT applies default logic to data type conversion. It is presented in CORT data type transformation matrix.

Rollbackable ALTER changes

Rollback ALTER changes means to apply set of another ALTER commands which bring table into initial stage. Rollback also need restore data as of time before change. For example, adding new column to the end could be reverted by drop column statement. However not every ALTER table change could be rolled back. For example, drop column or drop partition – these commands drops data as well along with column or partition. So it could not be restored. In this case CORT by default applies table recreation which is always rollbackable. But developers have option to set parameter rollback to FALSE if they know that ALTER will be faster and they do not need rollback functionality. For example, dropping one partition with outdated data from very big table could be good candidate for using hint no_rollback. Changing of constraints usually could be implemented as rollbackable change (see example 5). Because CORT compares not only table itself but constraints as well it is recommended to declare all constraints inside table definition.
Here are tables of all possible rollbackable and non-rollbackable ALTER TABLE … MODIFY data type changes for better understanding when option no_rollback could be used. All change scenarios are considered for non empty columns. CORT applies ALTER only for non-empty tables. Tables without data, global temporary and  external tables are always recreated.

Column rename

CORT implements columns comparison by their names not by their positions but it handles explicit  and implicit and column renaming. Explicit renaming is changing existing column name without modifying it’s data type and specified cort-value as old column name. (see example 6). Implicit renaming or “smart” renaming happens when existing column is excluded from the new table definition but new column with same data type is added on the same position. So if developer needs to change only one column name he can simply modify name in table definition and execute CREATE /*#OR REPLACE*/ statement.

CORT API

CORT provides package cort_pkg with API for developers.

Using this package developers can read and write cort-parameters for current session (see example 7). Parameter names and values are case insensitive. Boolean values are converted to string and back as

‘TRUE’  <=> TRUE
‘FALSE’ <=> FALSE
‘NULL’  <=> NULL

 

Functions returning current session parameters:

FUNCTION get_params
RETURN gt_params_rec;

FUNCTION get_param_value(
  in_param_name IN VARCHAR2
)
RETURN VARCHAR2;

FUNCTION get_param_bool_value(
  in_param_name IN VARCHAR2
)
RETURN BOOLEAN;

 

Procedures to change session level parameters:

PROCEDURE set_param_value(
  in_param_name  IN VARCHAR2,
  in_param_value IN NUMBER
);

PROCEDURE set_param_value(
  in_param_name  IN VARCHAR2,
  in_param_value IN VARCHAR2
);

PROCEDURE set_param_value(
  in_param_name  IN VARCHAR2,
  in_param_value IN BOOLEAN
);

 

Developers also can rollback changes using following functions (see example 8):

PROCEDURE rollback_table(
  in_table_name IN VARCHAR2,
  in_owner_name IN VARCHAR2,
  in_params     IN gt_params
);

PROCEDURE rollback_table(
  in_table_name IN VARCHAR2,
  in_owner_name IN VARCHAR2 DEFAULT USER,
  in_echo       IN BOOLEAN DEFAULT NULL, /* NULL - take from session param */
  in_test       IN BOOLEAN DEFAULT NULL  /* NULL - take from session param */
);

 

To see all history of the object changes query table CORT_OBJECTS.

Examples

Example 1 (enabling create or replace table):

CREATE /*# OR REPLACE*/ TABLE my_test_table(
  N           NUMBER,
  S           VARCHAR2(10),
  D           DATE
);

 

Example 2 (using parameter hints):

CREATE /*# OR REPLACE ECHO TEST NO_BAD_REFS NO_VALIDATE */ TABLE my_test_table(
  N           NUMBER,
  S           VARCHAR2(10),
  D           DATE,
  CONSTRAINT my_test_table_pk PRIMARY KEY(N)
);

 

Example 3 (using cort-value):

CREATE /*# OR REPLACE ECHO*/ TABLE my_test_table(
  N           NUMBER,
  S           VARCHAR2(8),           /*#= SUBSTR(S,1,8)*/ -- changed column length
  NEW_FILED   VARCHAR2(10) NOT NULL, /*#= UPPER(S) */ -- new not null column
  D           VARCHAR2(10) NOT NULL, /*# NVL(TO_CHAR(D,’YYYY-MM-DD’),’UNKNOWN’) */ -- changed column data type and nullability
  CONSTRAINT my_test_table_pk PRIMARY KEY(N)
);

 

Example 4 (using forced cort-value):

CREATE /*# OR REPLACE*/ TABLE my_test_table(
  N           NUMBER,
  S           VARCHAR2(10),
  NEW_FILED   VARCHAR2(10) NOT NULL, /*#== lower(S) */ -- change column value without type changes
  D           VARCHAR2(10) NOT NULL,
  CONSTRAINT my_test_table_pk PRIMARY KEY(N)
);

 

Example 5 (rollbackable ALTER change):

CREATE /*# OR REPLACE */ TABLE my_test_table(
  N           NUMBER,
  S           VARCHAR2(10),
  NEW_FIELD   VARCHAR2(10) NOT NULL,
  D           DATE,
  CONSTRAINT my_test_table_pk UNIQUE(N) -- primary key is replaced with unique key
);

 

Example 6 (column renaming):

CREATE /*# OR REPLACE*/ TABLE my_test_table(
  N           NUMBER,
  S           VARCHAR2(10),
  C           VARCHAR2(10) NOT NULL, --#=NEW_FILED
  D           DATE,
  CONSTRAINT my_test_table_pk UNIQUE(N)
);

 

Example 7 (set up session level parameter “echo” to FALSE and “test” to TRUE)

exec cort_pkg.set_param_value('echo','false')
exec cort_pkg.set_param_value('TEST',TRUE)

 

Example 8 (Rollback last changes to table MASTER_TABLE in current schema and with echoing executing commands)

exec cort.cort_exec_pkg.rollback_table('MASTER_TABLE', in_echo => true)

 

Supported Oracle versions

CORT supports Oracle 10.1, 10.2, 11.1 and 11.2. Compatibility with Oracle 12 has not been tested yet.

Installation

CORT installed by SQL scripts using SQL*Plus tool with SQLPlus Extensions into dedicated schema (by default CORT). CORT installation requires SYS connection to create schema for CORT. By default installer suggests to use name CORT. Then it asks for password (by default password  is “cort”). SYS also grants all necessary privileges to CORT user.

SYS objects

CORT creates replacement for some system views with issues. (Yes, Oracle has bugs!) During CORT development several issues have been found in following system views:

  • all_constraints - INDEX_OWNER column is not populated
  • all_lob_partitions - CHUNK column is displayed differently in blocks for composite partitions
  • all_lob_templates – missing join condition leading to Cartesian join
  • all_part_lobs – DEF_CHUNK column is displayed in blocks instead of bytes

Another problem is in triggers – if trigger definition does not have double quotes around table name and table renamed to name requires quotes then it is not possible to rename this table. Example:

create table test(n number)
/
create trigger test_trg after insert on test
begin
  null;
end;
/
rename test to "~test"
/
rename "~test" to test
/
ORA-00911: invalid character

These issues have been raised to Oracle Support. Workaround – drop trigger first or recreate trigger using quoted table name.

Plans for next release

  • Support create or replace for indexes
  • Support create or replace for sequences