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 the Oracle compatible mode 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 clause is supported.

• Specifying the column visibility attribute by using the VISIBLE | INVISIBLE clause 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 clause for defining the column collation.

• The evaluation_edition_clause clause for specifying the version of a PL/SQL function. The Resolver searches for functions of the specified version during name resolution.

• The unusable_editions_clause clause for marking one or more versions of a query unusable by specifying the virtual column expression that cannot be used for query evaluation.