Considerations and Support Information

Ensure that you understand what is supported, and any current limitations, dependencies, and required tasks before you create data assets in Data Integration.

OCI Vault Secrets and Oracle Wallets

Oracle Cloud Infrastructure Data Integration is integrated with Oracle Cloud Infrastructure Vault to enable you to manage sensitive information using vaults, keys, and secrets.

A vault is a container for keys and secrets. Secrets store credentials such as required passwords for connecting to data sources. You use an encryption key in a vault to encrypt and import secret contents to the vault. Secret contents are based64-encoded. Oracle Cloud Infrastructure Data Integration uses the same key to retrieve and decrypt secrets when creating a data asset and connecting to the data source.

For most data source types, you can use a secret in OCI Vault to store the password for the default connection in a data asset. To create a vault and a secret, see Creating a Vault and Creating a Secret in a Vault.

When you create a data asset, you provide the OCID of the secret in the connection details, so you don't have to enter the actual password. To copy the secret OCID, see Getting a Secret's Details.

For Oracle Database, Oracle Autonomous Data Warehouse, and Oracle Autonomous Transaction Processing sources, you have the option to use secrets for the Oracle wallet and passwords instead of uploading the wallet and entering the wallet password when you create your data asset.

To use an Oracle wallet with secrets in OCI Vault, you must:

  1. Provide a wallet password when you download the wallet.
  2. Remove the .p12 file from the dowloaded wallet zip.
  3. Use any base64 encoder to encode the modified wallet zip to base64.
  4. Copy the base64-encoded data to a secret in a vault.
  5. Create a secret for the wallet password.
  6. Create a secret for the database password.

To use secrets in OCI Vault, ensure that you have the following policy:

allow any-user to read secret-bundles in compartment <compartment-name> where ALL {request.principal.type = 'disworkspace', request.principal.id = '<workspace-ocid>'}

The following policy enables a group of users who are not administrators to use secrets with Oracle Autonomous Data Warehouse and Oracle Autonomous Transaction Processing:

allow group <group-name> to read secret-bundles in compartment <compartment-name>

Supported Data Sources for Data Assets

The following table lists the data sources that you can use with Data Integration.

Data Source Type Version Source Target
Amazon RDS for SQL Server 2019 Yes No
2017 Yes No
2016 Service Pack 2 Yes No
2014 Service Pack 3 Yes No
2012 Service Pack 4 Yes No
Amazon Redshift Amazon Redshift Yes No
Apache Hive CDH 5.4 and higher Yes No
Apache 1.0, 2.0, 3.0, and higher Yes Yes
Hadoop Distributed File System (HDFS) 3.1.2 Yes Yes
Azure SQL Database 11.0 and higher Yes No
12.0 and higher Yes No
Microsoft Azure Synapse Analytics 12.0 and higher Yes No
Microsoft SQL Server 2022 Yes No
2019 Yes No
2017 Yes No
2016 Service Pack 2 Yes No
2014 Service Pack 3 Yes No
2012 Service Pack 4 Yes No
MySQL 5.7.x and 8.0.x Yes Yes
MySQL HeatWave 8.0 and higher Yes No
MySQL on Amazon RDS 5.7.x and 8.0.x Yes No
Amazon S3 Amazon S3 Yes No
Autonomous Data Warehouse 18c/19c Yes Yes
Autonomous Transaction Processing 18c/19c Yes Yes
Oracle Database 11g Yes (except SQL task stored procedure) Yes
12.1 Yes Yes
12.2 Yes Yes
18 Yes Yes
19 Yes Yes
21 Yes Yes

Oracle Database on Oracle Cloud Infrastructure

11g Yes (except SQL task stored procedure) Yes
12.1 Yes Yes
12.2 Yes Yes
18 Yes Yes
19 Yes Yes
Oracle Peoplesoft

CRM 8.4 and higher

PeopleTools 8.49 and higher

Yes No
Oracle Siebel 8.0 and higher Yes No
Oracle E-Business Suite 12.0.4 and higher Yes No
Exadata DB Systems 11g Yes (except SQL task stored procedure) Yes
12.1 Yes Yes
12.2 Yes Yes
18 Yes Yes
19 Yes Yes
Oracle Cloud Infrastructure Object Storage Latest Yes Yes
Oracle on Amazon RDS 12.1 Yes No
12.2 Yes No
18 Yes No
19 Yes No
21 Yes No
Oracle Fusion Applications using Oracle Business Intelligence Cloud Connector (BICC)

BICC API version 13.20.10 and higher

Fusion Applications version 13.20.10 (20 Oct) and higher

YesNo
Oracle Fusion Applications using Oracle Business Intelligence Publisher (BIP) 11.1.1.9 and higher Yes No
PostgreSQL 12.0 and higher Yes No
11.0 and higher Yes No
10.1 Yes No
9.6, 9.5, 9.4, 9.3, 9.2, 9.1, and 9.0 Yes No
8.4, 8.3, and 8.2 Yes No
IBM DB2 DB2 V11.1 and higher for Linux, UNIX, and Windows Yes No
DB2 V10.1 and higher for Linux, UNIX, and Windows Yes No
DB2 V9.1 and higher for Linux, UNIX, and Windows Yes No
DB2 V8.x and higher for Linux, UNIX, and Windows Yes No
DB2 V12 and higher for z/OS Yes No
DB2 V11 and higher for z/OS Yes No
DB2 V10 and higher for z/OS Yes No
DB2 V9.1 and higher for z/OS Yes No
DB2 UDB V8.1 for z/OS Yes No
DB2 i 7.1 and higher Yes No
DB2 i 6.1 and higher Yes No
DB2 V5R4 and higher for i 5/OS Yes No
Amazon Web Services (AWS) Aurora PostgreSQL 1.0 and higher Yes No
Influx DB 1.8 and 2.x Yes No
REST OpenAPI 3.0.0 and higher Yes No
Snowflake NOT APPLICABLE Yes No
Salesforce Salesforce API 56.0 Yes No

Supported Object Types

For Oracle Cloud Infrastructure Object Storage and Amazon S3 data assets, Data Integration supports the following object types:

  • CSV
  • JSON
  • Parquet
  • Avro
  • Excel (Currently, only XLSX files are supported.)

Note that only read and write of primitive data types are supported.

Supported Compression Types

For Oracle Cloud Infrastructure Object Storage data assets, Data Integration supports the following compression types or methods for using the CSV or JSON object file type with a source or target operator:

  • Auto (Default)
  • Gzip
  • Bzip2
  • Deflate
  • Lz4
  • Snappy

For Parquet and Avro file types, only Auto (Default) is supported.

If a source file is compressed, the compression type is actually the compression algorithm that is used. If you don't know the compression algorithm, then use the Auto (Default) compression type.

Data Types Not Supported

Data source Data types not supported
Oracle Database
  • RAW
  • ROWID
  • UROWID
  • BFILE
  • TIMESTAMP WITH LOCAL TIMEZONE
  • INTERVAL DAY TO SECOND
  • INTERVAL YEAR TO MONTH
  • XMLTYPE
  • SDO_GEOMETRY
  • NCHAR
  • NVARCHAR

Hierarchical Data Types

Data Integration supports hierarchical data types in source and target data entities.

To read and write data with hierarchical data types, currently you can use only generic REST data assets and file storage data assets such as OCI Object Storage, Amazon S3, and HDFS. The JSON file format is supported by generic REST data assets. For file storage data assets, the following file formats are supported:

  • JSON and multi-line JSON
  • Avro
  • Parquet

The supported hierarchical data types are the Array, Struct, and Map complex types. You can perform any type of file to file transformation, such as JSON to Avro or Avro to JSON.

Before using hierarchical data entities and complex types, ensure that you understand the supported capabilities in Data Integration when working with components and performing tasks.

Task/ComponentSupportLimitation
Prepare data sources
  • Generic REST data asset and JSON file format
  • OCI Object Storage data asset and JSON, multi-line JSON, Avro, and Parquet file formats
  • Array, Struct, and Map complex types
  • Array is not supported in Avro and Parquet file formats
Add and configure a source operator
  • JSON, Avro, and Parquet file types
  • Exclude and Rename rules on first-level fields of ARRAY_TYPE, COMPOSITE_TYPE (Struct), and MAP_TYPE
  • Simplified data structure view of a complex type is displayed
  • Attributes tab: Cannot apply rules on nested fields
  • Data tab: Data Profile does not display for complex types
Add and configure a target operator

Select the Create new data entity checkbox:

  • JSON, Avro, and Parquet hierarchical file formats
  • Array, Struct, and Map complex types

Select existing data entity:

  • File: JSON, Avro, and Parquet hierarchical file formats
  • Database: Only Oracle Database and Oracle Database on Oracle Cloud Infrastructure
Use shape operators
  • Array and Struct complex types are supported in all operators
  • For Union operator, only Union All (include duplicate rows) is supported with Array and Struct complex types
  • For Union All, Minus, Intersect, Filter, and Split operators: Map complex type is not supported
  • Union (eliminate duplicate rows) is not supported
  • Attribute bulk selections and patterns are not supported for complex types. For example, %MACRO_INPUT% for bulk selection of attributes is not supported in the Expression Builder.
Map attributes
  • First-level fields of JSON, Avro, and Parquet hierarchical data entities can be mapped
  • To map a nested field, create an expression for the nested field, and then map the derived field
  • Nested fields of hierarchical data entities cannot be mapped directly

For example, NAME and EMAIL can be mapped. F_NAME and L_NAME in NAME cannot be mapped directly. EMAILID and EMAILTYPE in EMAIL cannot be mapped directly:

{
   "CUST_ID":1333,
   "NAME":{
      "F_NAME":"Sam",
      "L_NAME":"Smith"
   },
   "EMAIL":[
      {
         "EMAILID":"abc@oracle.com",
         "EMAILtype":"work"
      },
      {
         "EMAILID":"abc@othermail.com",
         "EMAILtype":"personal"
      }
   ],
   "GENDER":"Male"
}
Use data transformations (Data tab)
  • Exclude and Rename transformations on first-level fields of ARRAY_TYPE, COMPOSITE_TYPE, and MAP_TYPE
  • All other transformations and bulk transformations are not supported for complex types

Unicode Support

Data Integration supports the Unicode standard, which is a universal character encoding standard for written characters and text in any language. The Unicode standard provides a unique number for every character regardless of the platform, device, or application. For example, 0041 is the Unicode character for the English letter "A".

Data Integration supports Unicode characters (including multibyte characters) in data and metadata.

Unicode support in data means that the attributes and attribute values in your source and target data assets can include Unicode and multibyte characters. You can also enter Unicode and multibyte characters in expressions. For JSON and CSV Object Storage data assets, the default encoding is UTF-8, and you cannot change it.

Unicode support in metadata means that the data entity and schema names of your data assets can include Unicode and multibyte characters. You can also enter Unicode and multibyte characters for names and descriptions when working with objects in Data Integration.

In the Unicode standard, a unique number assigned to a Unicode character is a code point. Currently, Data Integration supports the following Unicode code points and range of code points:

Code Point or RangeCharacterNumber of Characters Supported
Basic Latin characters
0024$ (dollar sign)1
0041 - 005AA to Z26
005F_ (underscore)1
0061 - 007Aa to z26
Latin-1 Supplement characters
00C0 - 00D6Latin-1 Supplement characters with accents23
00D8 - 00F631
00F8 - 00FF8
Characters in 46 ranges from Latin Extended-A to Greek Extended
0100 - 1FFFCharacters in the following named ranges: Latin Extended-A, Latin Extended-B, IPA Extensions, Spacing Modifier Letters, Combining Diacritical Marks, Greek and Coptic, Cyrillic, Cyrillic Supplementary, Armenian, Hebrew, Arabic, Syriac, Thaana, Devanagari, Bengali, Gurmukhi, Gujarati, Oriya, Tamil, Telugu, Kannada, Malayalam, Sinhala, Thai, Lao, Tibetan, Myanmar, Georgian, Hangul Jamo, Ethiopic, Cherokee, Unified Canadian Aboriginal Syllabics, Ogham, Runic, Tagalog, Hanunoo, Buhid, Tagbanwa, Khmer, Mongolian, Limbu, Tai Le, Khmer Symbols, Phonetic Extensions, Latin Extended Additional, Greek Extended7936
Characters in 4 ranges from Hiragana to Hangul Compatibility Jamo
3040 - 318FCharacters in the following named ranges: Hiragana, Katakana, Bopomofo, Hangul Compatibility Jamo336
Characters in 4 CJK (Chinese, Japanese, and Korean) ranges
3300 - 337FCJK Compatibility characters128
3400 - 3D2DCJK Unified Ideographs Extension A characters2350
4E00 - 9FFFCJK Unified Ideographs characters20992
F900 - FAFFCJK Compatibility Ideographs characters512

Understanding Data Type Mappings

Data types from the source and target systems you use are mapped to and mapped from a core set of generic data types in Oracle Cloud Infrastructure Data Integration.

In the set of generic data types, some of the types have additional length or scale properties that you can use to further constrain the data type.

The Expression operator in Data Integration does not yet support all the generic data types. You can create a new attribute based on a generic data type only if the generic type is supported.

Generic Data Type Mappings
Data type Expression support Data type string reference Length Scale
ANYDATA Seeded:/typeSystems/PLATFORM /dataTypes/ANYDATA
BIGINT Yes Seeded:/typeSystems/PLATFORM /dataTypes/BIGINT
BINARY Yes Seeded:/typeSystems/PLATFORM /dataTypes/BINARY
BINARY_DOUBLE Seeded:/typeSystems/PLATFORM /dataTypes/BINARY_DOUBLE
BINARY_FLOAT Seeded:/typeSystems/PLATFORM /dataTypes/BINARY_FLOAT
BLOB Seeded:/typeSystems/PLATFORM /dataTypes/BLOB
BOOLEAN Yes Seeded:/typeSystems/PLATFORM /dataTypes/BOOLEAN
CHAR Yes Seeded:/typeSystems/PLATFORM /dataTypes/CHAR Yes
CLOB Seeded:/typeSystems/PLATFORM /dataTypes/CLOB
DATE Yes Seeded:/typeSystems/PLATFORM /dataTypes/DATETIME
DATETIME Yes Seeded:/typeSystems/PLATFORM /dataTypes/DATETIME
DECIMAL Yes Seeded:/typeSystems/PLATFORM /dataTypes/DECIMAL Yes Yes
DOUBLE Yes Seeded:/typeSystems/PLATFORM /dataTypes/DOUBLE
FIXED Seeded:/typeSystems/PLATFORM /dataTypes/FIXED
FLOAT Yes Seeded:/typeSystems/PLATFORM /dataTypes/FLOAT Yes
INTEGER Yes Seeded:/typeSystems/PLATFORM /dataTypes/INTEGER
INTERVAL DAY TO SECOND Yes Seeded:/typeSystems/PLATFORM /dataTypes/INTERVAL DAY TO SECOND Yes Yes
INTERVAL YEAR TO MONTH Yes Seeded:/typeSystems/PLATFORM /dataTypes/INTERVAL YEAR TO MONTH Yes
LONG Yes Seeded:/typeSystems/PLATFORM /dataTypes/LONG
MONEY Seeded:/typeSystems/PLATFORM /dataTypes/MONEY
NCHAR Seeded:/typeSystems/PLATFORM /dataTypes/NCHAR Yes
NCLOB Seeded:/typeSystems/PLATFORM /dataTypes/NCLOB
NUMBER Seeded:/typeSystems/PLATFORM /dataTypes/NUMBER
NUMERIC Yes Seeded:/typeSystems/PLATFORM /dataTypes/NUMERIC Yes Yes
NVARCHAR Seeded:/typeSystems/PLATFORM /dataTypes/NVARCHAR Yes
REAL Seeded:/typeSystems/PLATFORM /dataTypes/REAL
SMALLINT Yes Seeded:/typeSystems/PLATFORM /dataTypes/SMALLINT
STRING Yes Seeded:/typeSystems/PLATFORM /dataTypes/STRING Yes
TIME Yes Seeded:/typeSystems/PLATFORM /dataTypes/TIME
TIMESTAMP WITH TIME ZONE Yes Seeded:/typeSystems/PLATFORM /dataTypes/TIMESTAMP WITH TIME ZONE Yes
TIMESTAMP Yes Seeded:/typeSystems/PLATFORM /dataTypes/TIMESTAP Yes
TINYINT Yes Seeded:/typeSystems/PLATFORM /dataTypes/TINYINT
VARBINARY Yes Seeded:/typeSystems/PLATFORM /dataTypes/VARBINARY
VARCHAR Yes Seeded:/typeSystems/PLATFORM /dataTypes/VARCHAR Yes
XMLFORMAT Seeded:/typeSystems/PLATFORM /dataTypes/XMLFORMAT
XMLTYPE Seeded:/typeSystems/PLATFORM /dataTypes/XMLTYPE
Mapping: Oracle to and from Generic

The table shows Oracle to Generic type mapping, and Generic to Oracle type mapping.

Oracle to Generic Generic Generic to Oracle
ANYDATA ANYDATA ANYDATA
BIGINT NUMBER
BINARY BLOB
BINARY_DOUBLE BINARY_DOUBLE BINARY_DOUBLE
BINARY_FLOAT BINARY_FLOAT BINARY_FLOAT

BLOB

RAW

LONGRAW

BLOB BLOB
BOOLEAN VARCHAR2
CHAR CHAR CHAR
CLOB CLOB CLOB
DATE DATE DATE
DATETIME TIMESTAMP
DECIMAL NUMBER
DOUBLE NUMBER
FIXED BLOB
FLOAT FLOAT FLOAT
INTEGER NUMBER
INTERVAL DAY TO SECOND INTERVAL DAY TO SECOND INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH INTERVAL YEAR TO MONTH INTERVAL YEAR TO MONTH
LONG LONG NUMBER
MONEY NUMBER
NCHAR NCHAR NCHAR
NCLOB NCLOB NCLOB
NUMBER NUMBER
NUMBER NUMERIC NUMBER
NVARCAHR2 NVARCHAR NVARCHAR2
REAL REAL REAL
SMALLINT NUMBER
STRING VARCHAR2
TIME DATE
TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE
TIMESTAMP(0) WITH LOCAL TIME ZONE TIMESTAMP WITH TIME ZONE
TIMESTAMP(0) WITH TIME ZONE TIMESTAMP WITH TIME ZONE

TIMESTAMP

TIMESTAMP(0)

TIMESTAMP TIMESTAMP
TINYINT NUMBER
VARBINARY BLOB

ROWID

UROWID

VARCHAR2

VARCHAR VARCHAR2
XMLFORMAT XMLFORMAT XMLFORMAT
XMLTYPE XMLTYPE XMLTYPE
Mapping: Hive to Generic

The table shows Hive to Generic type mapping.

Hive Generic
ARRAY VARCHAR
BIGINT BIGINT
BOOLEAN BOOLEAN
CHAR CHAR
DATE DATE
DECIMAL DECIMAL
DOUBLE DOUBLE
FLOAT REAL
INT INTEGER
MAP VARCHAR
SMALLINT SMALLINT
STRING VARCHAR
STRUCT VARCHAR
TIMESTAMP TIMESTAMP
TINYINT TINYINT
UNION VARCHAR
VARCHAR VARCHAR
Mapping: MySQL to and from Generic

The table shows MySQL to Generic type mapping, and Generic to MySQL type mapping.

MySQL to Generic Generic Generic to MySQL
ANYDATA VARCHAR
BIGINT BIGINT BIGINT
BINARYBINARYBINARY
BINARY_DOUBLE DOUBLE PRECISION
BINARY_FLOAT FLOAT

BLOB

LONGBLOB

MEDIUMBLOB

TINYBLOB

BLOB BLOB
BOOLEAN VARCHAR
CHAR CHAR CHAR

LONGTEXT

MEDIUMTEXT

TEXT

JSON

CLOB LONGTEXT
COMPLEX VARCHAR
DATE DATE DATE
DATETIME TIMESTAMP
DECIMAL DECIMAL DECIMAL

DOUBLE

DOUBLE PRECISION

DOUBLE DOUBLE
FIXED BLOB
FLOAT FLOAT FLOAT

INT

MEDIUMINT

INTEGER INT
INTERVAL DAY TO SECOND VARCHAR
INTERVAL YEAR TO MONTH VARCHAR
LONG BIGINT
MONEY NUMERIC
NCHAR VARCHAR
NCLOB VARCHAR
NUMBER NUMERIC
NUMERIC NUMERIC NUMERIC
NVARCHAR VARCHAR
REAL REAL REAL
SDO_GEOMETRY VARCHAR
SMALLINT SMALLINT SMALLINT

YEAR

TINYTEXT

STRING VARCHAR
TIME TIME TIME

DATETIME

TIMESTAMP

TIMESTAMP

TIMESTAMP
TIMESTAMP WITH TIME ZONE TIMESTAMP
TINYINT TINYINT TINYINT
VARBINARY VARBINARY VARBINARY

BIT

VARCHAR

ENUM

SET

GEOMETRY

POINT

LINESTRING

POLYGON

MULTIPOINT

MULTILINESTRING

MULTIPOLYGON

GEOMCOLLECTION

VARCHAR VARCHAR
XMLFORMAT VARCHAR
XMLTYPE VARCHAR
Mapping: PostgreSQL to Generic

The table shows PostgreSQL to Generic type mapping.

PostgreSQL Generic

BIGINT

BIGSERIAL

BIGINT
BIT BINARY

BIT VARYING

BYTEA

VARBINARY
BOOLEAN BOOLEAN
CHARACTER CHAR
CHARACTER VARYING VARCHAR
DATE DATE
DOUBLE PRECISION DOUBLE
INTEGER INTEGER
JSON VARCHAR(10485760)
NUMERIC NUMERIC
REAL REAL
SERIAL INTEGER
SMALLINT SMALLINT
TEXT VARCHAR(1073741823)

TIME

TIME WITH TIMEZONE

TIMESTAMP

TIMESTAMP WITH TIMEZONE

TIMESTAMP

UUID

ARRAY

VARCHAR
XML XMLTYPE
Mapping: SQL Server to and from Generic

The table shows SQL Server to Generic type mapping, and Generic to SQL Server type mapping.

SQL Server to Generic Generic Generic to SQL Server

bigint

bigint identity

BIGINT bigint
binary

BINARY

binary

BINARY_DOUBLE

BINARY_FLOAT

float
bit BOOLEAN varchar
char CHAR char
date DATE datetime
datetime DATETIME datetime
DOUBLE float
FIXED binary

datetime2

timestamp

TIMESTAMP

decimal

decimal() identity

DECIMAL decimal
float FLOAT float

int identity

int

INTEGER int

INTERVAL DAY TO SECOND

INTERVAL YEAR TO SECOND

varchar
LONG bigint

money

smallmoney

MONEY money
nchar NCHAR nchar
nvarchar NVARCHAR nvarchar

ntext

nvarchar(max)

NCLOB nvarchar(max)
NUMBER numeric

numeric() identity

numeric

NUMERIC numeric
real REAL real
smalldatetime DATETIME

smallint identity

smallint

SMALLINT smallint
STRING varchar
time TIME time

TIMESTAMP WITH TIME ZONE

TIMESTAMP

datetime

tinyint identity

tinyint

TINYINT tinyint
varbinary VARBINARY varbinary

image

varbinary(max)

BLOB varbinary(max)

text

varchar(max)

CLOB varchar(max)

sql_variant

sysname

uniqueidentifier

varchar

VARCHAR varchar
XMLTYPE text
Mapping: File to and from Generic

The table shows File to Generic type mapping, and Generic to File type mapping.

File to Generic Generic Generic to File

Ascii signed zoned decimal

Ascii unsigned zoned decimal

NUMERIC

Binary signed big endian

Binary signed little endian

Binary unsigned big endian

Binary unsigned little endian

NUMERIC
Date DATE Date

Ebcdic signed zoned decimal

Ebcdic unsigned zoned decimal

NUMERIC

Ebcdic

Fixed Ebcdic

Fixed String

VARCHAR
Numeric NUMERIC
Signed packed decimal NUMERIC
String VARCHAR
Unsigned packed decimal NUMERIC
BIGINT Numeric
BINARY String

BINARY_DOUBLE

BINARY_FLOAT

Numeric

BLOB

BOOLEAN

String

CHAR

CLOB

String
DATETIME Date

DECIMAL

DOUBLE

Numeric
FIXED String
FLOAT Numeric
INTEGER Numeric

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

String
LONG Numeric
MONEY Numeric

NCHAR

NCLOB

String

NUMBER

NUMERIC

Numeric
NVARCHAR String
REAL Numeric

SMALLINT

TINYINT

Numeric
STRING String
TIME String

TIMESTAMP WITH TIME ZONE

TIMESTAMP

Date
VARBINARY String
VARCHAR String
XMLTYPE String
Mapping: CSV to and from Generic

All type mapping is String.

Mapping: JSON to and from Generic

The table shows JSON to Generic type mapping, and Generic to JSON type mapping.

JSON to Generic Generic Generic to JSON

Null

String

VARCHAR String
BIGINT Number
BINARY String

BINARY_DOUBLE

BINARY_FLOAT

Number
BLOB String
Boolean BOOLEAN Boolean

CHAR

CLOB

String

DATE

DATETIME

String

DECIMAL

DOUBLE

Number
FIXED String

FLOAT

INTEGER

Number

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

String

LONG

MONEY

Number

NCHAR

NCLOB

NVARCHAR

String
NUMBER Number
Number NUMERIC Number
REAL Number
STRING String

SMALLINT

TINYINT

Number

TIME

TIMESTAMP WITH TIME ZONE

TIMESTAMP

String
VARBINARY String
XMLTYPE String
Mapping: Parquet to and from Generic

The table shows Parquet to Generic type mapping, and Generic to Parquet type mapping.

Parquet to Generic Generic Generic to Parquet

BIGINT

LONG

NUMBER

INT64

BINARY

BSON

FIXED_LEN_BYTE_ARRAY

INT96

BINARY BINARY

BINARY_DOUBLE

BINARY_FLOAT

BLOB

CHAR

CLOB

FIXED

INTERVAL DAY TO SECOND

INTERVAL DAY TO MONTH

NCHAR

NCLOB

NVARCHAR

VARBINARY

XMLTYPE

BINARY
BOOLEAN BOOLEAN BOOLEAN
DATE DATE DATE
DATETIME INT32
DECIMAL DECIMAL DECIMAL
DOUBLE DOUBLE DOUBLE

ENUM

INTERVAL

JSON

UTF8

key

VARCHAR UTF8
FLOAT FLOAT FLOAT

INTEGER

MONEY

SMALLINT

TINYINT

INT32
REAL DOUBLE
STRING UTF8
TIME TimeType
TIMESTAMP WITH TIME ZONE TimestampType
TIMESTAMP TimestampType

INT32

INT64

TIMESTAMP_MICROS

TIMESTAMP_MILLIS

TIME_MICROS

TIME_MILLIS

NUMERIC INT32
Mapping: Avro to and from Generic

The table shows Avro to Generic type mapping, and Generic to Avro type mapping.

Avro to Generic Generic Generic to Avro
bytes STRING string
ANYDATA string
BIGINT long
BINARY string
BINARY_DOUBLE string
BINARY_FLOAT string
BLOB string
boolean BOOLEAN boolean
CHAR string
CLOB string
DATE string
DATETIME string
DECIMAL double
double DOUBLE double
FIXED string
float FLOAT float
int INTEGER int
INTERVAL DAY TO SECOND string
INTERVAL YEAR TO MONTH string
long LONG long
MONEY double
NCHAR string
NCLOB string
NUMBER int
NUMERIC int
NVARCHAR string
REAL float
SMALLINT int
string STRING string
TIME string
TIMESTAMP WITH TIME ZONE string
TIMESTAMP string
TINYINT int
VARBINARY string
null VARCHAR string
XMLFORMAT string
XMLTYPE string
Mapping: Excel to Generic

The table shows Excel to Generic type mapping.

Excel Generic
BOOLEAN BOOLEAN
NUMERIC NUMERIC
STRING STRING
Mapping: Fusion Applications (BICC) to and from Generic

The table shows Oracle Fusion Applications using Oracle Business Intelligence Cloud Connector (BICC) to Generic type mapping, and Generic to Oracle Fusion Applications using BICC type mapping.

Oracle Fusion Applications using BICC to GenericGenericGeneric to Oracle Fusion Applications using BICC
NUMERICNUMERICNUMERIC
VARCHARVARCHARVARCHAR
DATEDATEDATE
TIMESTAMPTIMESTAMPTIMESTAMP

Network Configurations

Your network configurations depend on the source and target data assets you're using with the Data Integration service, and where the assets are located.

See the Understanding VCN Configuration for Data Integration blog to identify the options for your needs.

A workspace can have an attached Virtual Cloud Network (VCN). For data sources in a private network, create a VCN with at least one regional subnet. Only regional subnets are supported, and DNS hostnames must be used in the subnets. Depending on the location of your data sources, you might have to create other network objects such as service gateways, network security groups, and Network Address Translation (NAT) gateways.

In general, for data sources that are accessible from the internet:

  • If a workspace has an attached VCN: Data Integration can connect directly through a Network Address Translation (NAT) gateway on the VCN of the workspace.
  • If a workspace does not have an attached VCN: Data Integration can connect directly using public IP addresses.

Resources, such as workspaces, with private IPs defined in any subnet can access other private resources in different virtual cloud networks and regions through Service gateways or NAT gateways using local or remote peering gateways.

You can also combine gateways when you need to access both Object Storage and Autonomous Data Warehouse. For example, for public connectivity, you would need both a Service gateway for Object Storage and a NAT gateway for Autonomous Data Warehouse.

For data sources that are not accessible from the internet, other options include:

  • Create a workspace with a private endpoint enabled, with the private endpoint in the same subnet as the data source.

  • Use Oracle Cloud Infrastructure FastConnect.

  • Use Oracle Cloud Infrastructure VPN Connect (also referred to as an IPSec VPN).

Note, however, that Oracle Cloud Infrastructure FastConnect and Oracle Cloud Infrastructure VPN Connect must be used when these sources are in private domains:

  • Oracle on Amazon RDS
  • MySQL on Amazon RDS
  • Amazon RDS for SQL Server
  • Microsoft Azure SQL Database

Oracle Cloud Infrastructure Networking documentation: