RECREATION (create + insert as select + rename + drop)
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.
- 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.
/*# 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).
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.
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.
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 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.
CREATE /*# OR REPLACE*/ TABLE my_test_table( N NUMBER, S VARCHAR2(10), D DATE );
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) );
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) );
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) );
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 );
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)
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.
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.
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