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:
Provide a wallet password when you download the wallet.
Remove the .p12 file from the dowloaded wallet zip.
Use any base64 encoder to encode the modified wallet zip to base64.
Copy the base64-encoded data to a secret in a vault.
Create a secret for the wallet password.
Create a secret for the database password.
To use secrets in OCI
Vault, ensure that you have the following policy:
Copy
allow any-user to read secret-bundles in compartment <compartment-name> where ALL {request.principal.type = 'disworkspace', request.principal.id = '<workspace-ocid>'}
Use the following policy to enable a group of users who aren't administrators to use secrets with Oracle Autonomous Data Warehouse and Oracle Autonomous Transaction Processing:
Copy
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
Yes
No
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 the compression algorithm that's 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/Component
Support
Limitation
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:
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 Range
Character
Number of Characters Supported
Basic Latin characters
0024
$ (dollar sign)
1
0041 - 005A
A to Z
26
005F
_ (underscore)
1
0061 - 007A
a to z
26
Latin-1 Supplement characters
00C0 - 00D6
Latin-1 Supplement characters with accents
23
00D8 - 00F6
31
00F8 - 00FF
8
Characters in 46 ranges from Latin Extended-A to Greek Extended
0100 - 1FFF
Characters 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 Extended
7936
Characters in 4 ranges from Hiragana to Hangul Compatibility Jamo
3040 - 318F
Characters in the following named ranges: Hiragana, Katakana, Bopomofo, Hangul Compatibility Jamo
336
Characters in 4 CJK (Chinese, Japanese, and Korean) ranges
3300 - 337F
CJK Compatibility characters
128
3400 - 3D2D
CJK Unified Ideographs Extension A characters
2350
4E00 - 9FFF
CJK Unified Ideographs characters
20992
F900 - FAFF
CJK Compatibility Ideographs characters
512
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 types have length, scale, and other 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.
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 Generic
Generic
Generic to Oracle Fusion Applications using BICC
NUMERIC
NUMERIC
NUMERIC
VARCHAR
VARCHAR
VARCHAR
DATE
DATE
DATE
TIMESTAMP
TIMESTAMP
TIMESTAMP
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.
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: