DBMS_CLOUD Package Avro to Oracle Data Type Mapping

Describes the mapping of Avro data types to Oracle data types.

Note:

Complex types, such as maps, arrays, and structs are supported starting with Oracle Database 19c. See DBMS_CLOUD Package ORC, Parquet and Avro Complex Types for information on using Avro complex types.

If your database version is Oracle Database 18c, the external table supports scalar data types only, with the following exceptions:

  • DBMS_CLOUD supports UNION of types [null, SIMPLE_TYPE], where SIMPLE_TYPE is: INT, LONG, FLOAT, DOUBLE, STRING, BYTES.
  • DBMS_CLOUD does not support UNION of multiple types, for example [null, INT, DOUBLE]
  • Files that contain arrays and/or maps of simple types can be read. These columns are skipped.
  • Files that contain arrays and/or maps of complex types, for example an array of records, cannot be read and report an error. For example: querying an Avro file with unsupported types shows:
    ORA-29913: error in executing ODCIEXTTABLEOPEN calloutdbms_cloud.create_external_table 
    threw an error when attempting to automatically define columns that aren't supported:
    ORA-20000: Error building column list from file
  • Files that contain records of simple types can be read.
Avro Type Oracle Type
INT NUMBER(10)
LONG NUMBER(19)
BOOL NUMBER(1)
UTF8 BYTE_ARRAY RAW(2000)
FLT BINARY_FLOAT
DBL BINARY_DOUBLE
DECIMAL(p) NUMBER(p)
DECIMAL(p,s) NUMBER(p,s)
DATE DATE
STRING VARCHAR2
TIME_MILLIS VARCHAR2(20 BYTE)
TIME_MICROS VARCHAR2(20 BYTE)
TIMESTAMP_MILLIS TIMESTAMP(3)
TIMESTAMP_MICROS TIMESTAMP(6)
ENUM VARCHAR2(n) Where: "n" is the actual maximum length of the AVRO ENUM's possible values
DURATION RAW(2000)
FIXED RAW(2000)
NULL VARCHAR2(1) BYTE

See DBMS_CLOUD Package ORC, Parquet and Avro Complex Types for information on using Avro complex types.