Virtual columns

This topic describes the conversion scope of CREATE TABLE DDL operations for defining virtual columns during data migration from an Oracle database to an Oracle tenant of OceanBase Database.

Syntax

virtual_column_definition:
column [ datatype [ COLLATE column_collation_name ] ]
  [ VISIBLE | INVISIBLE ]
  [ GENERATED ALWAYS ] AS (column_expression) [ VIRTUAL ]
  [ evaluation_edition_clause ] [ unusable_editions_clause ]
  [ inline_constraint [ inline_constraint ]... ]

evaluation_edition_clause:
EVALUATE USING { CURRENT EDITION | EDITION edition | NULL EDITION }

unusable_editions_clause:
[ UNUSABLE BEFORE { CURRENT EDITION | EDITION edition } ]
[ UNUSABLE BEGINNING WITH { CURRENT EDITION | EDITION edition | NULL EDITION } ]

Supported operations

• Defining column types by using the column_name datatype option is supported.

• Specifying the column visibility attribute by using the VISIBLE | INVISIBLE option is supported. The default value is VISIBLE, which is not declared.

• Defining inline constraints by using the inline_constraint option is supported. For more information, see Constraints.

• Defining virtual columns by using the GENERATED ALWAYS AS column_expression and column datatype AS column_expression options is supported. Here is the sample code:

  Notice

  Defining virtual columns by using the GENERATED ALWAYS AS identity option is not supported. OMS ignores the option during schema migration and migrates only the field names and type definitions.

  CREATE TABLE T (C1 CHAR, C2 CHAR GENERATED ALWAYS AS (SUBSTR(C1,1,2)));
  CREATE TABLE T (C1 CHAR, C2 CHAR AS (SUBSTR(C1,1,2)));
  

Ignored clauses and options

• The COLLATE column_collation_name option for defining the column collation.

• The evaluation_edition_clause clause for specifying the edition of an editioned PL/SQL function. The edition is searched during name resolution of the function.

• The unusable_editions_clause clause for specifying one or more editions of queries. In these editions, the virtual column expression is unusable for query evaluation.