DBMS_CLOUD Package Avro to Oracle Data Type Mapping

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

Note:

For Avro, 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.

Table A-4 Avro Data Type Support

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