All Products
Search

This Product

    ApsaraDB for OceanBase:Virtual columns

    Document Center

    ApsaraDB for OceanBase:Virtual columns

    Last Updated:Jun 03, 2024

    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 DDL

    • Defining column types by using the column_name datatype option is supported. For more information about type conversion, see Data type conversion.

    • 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 an example:

      Important

      Defining virtual columns by using the GENERATED ALWAYS AS identity option is not supported. If this option is used, schema migration of the data transmission service ignores this option and migrates only field names and types. 

      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

    Note

    The following clauses and options are ignored and are not resolved or converted when they are specified in the synchronized DDL operations.

    • 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 queried 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.