Using the driver version ojdbc6, it currently supports Oracle 11g, 12c, 18c, 19c versions, among which the new features of 18c and 19c are not supported.
Use Limits
Currently, when Data Integration synchronizes data, it only supports UTF8, AL32UTF8, AL16UTF16, and ZHS16GBK encoding formats.
Note:
Supports reading view tables.
Oracle Offline Single Table Read Node Configuration
Parameters
Description
Data Source
Available Oracle data sources.
Database
Supports selecting or manually entering the database name to be read
By default, the database bound to the data source is used as the default database. Other databases need to be manually entered.
If the data source network is not connected and the database information cannot be fetched directly, you can manually enter the database name. Data synchronization can still be performed when the Data Integration network is connected.
Schema
Supports selecting or manually entering the Schema to be read.
Table
Supports selecting or manually entering the table name to be read.
Add Sharded Database/Table
Multiple data sources can be created, and corresponding table objects can be added.
Note:
For sharded databases and tables, ensure that the schema information of the selected multiple table objects is consistent (including field names and field types). The system defaults to displaying the metadata field information of the first table from the first data source in the data fields module. If the fields between multiple tables are inconsistent, it may cause runtime failures.
Split Key
Specify the field used for data sharding. After specifying, concurrent tasks will be initiated for data synchronization, enhancing data synchronization efficiency.
You can use a column from the source data table as the partition key. It is recommended to use the primary key or an indexed column as the partition key. If you need to configure other types of fields, such as strings, floating-point numbers, and dates, please enter them manually.
Filter Conditions (Optional)
Oracle concatenates SQL based on the specified WHERE condition and performs data extraction accordingly. For example, when testing, specify the WHERE condition as row_number().
The WHERE condition can effectively perform incremental business synchronization.
If the WHERE condition is not configured or is empty, it will be considered as full table data synchronization.
Oracle Offline Single Table Write Node Configuration
Parameters
Description
Data Destination
The Oracle data source to be written to.
Database
Supports selection or manual input of the database name to write to
By default, the database bound to the data source is used as the default database. Other databases need to be manually entered.
If the data source network is not connected and the database information cannot be fetched directly, you can manually enter the database name. Data synchronization can still be performed when the Data Integration network is connected.
Schema
Supports selecting or manually entering the Oracle data mode to be written to.
Table
Supports selection or manual input of the table name to write to
If the data source network is not connected and the table information cannot be fetched directly, you can manually enter the table name. Data synchronization can still be performed when the Data Integration network is connected.
Whether to Clear Table
You can manually choose whether to clear the Oracle data table before writing to it.
Batch Submission Size
The size of the record for one-time batch submissions. This value can significantly reduce the number of network interactions between the data synchronization system and Oracle, and improve overall throughput. If this value is set too high, it may cause the data synchronization process to run into an OOM exception.
Write Mode
Oracle supports two writing modes:
append: append write.
upsert: update write based on the set primary key fields.
Pre-Executed SQL (Optional)
The SQL statement executed before the synchronization task. Fill in the correct SQL syntax according to the data source type, such as clearing the old data in the table before execution (truncate table tablename).
Post-Executed SQL(Optional)
The SQL statement executed after the synchronization task. Fill in the correct SQL syntax according to the data source type, such as adding a timestamp (alter table tablename add colname timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP).
Data type conversion support
Read
The supported data types for reading from Oracle and their conversion relationships are as follows (when processing Oracle, the data types from the Oracle data source will first be mapped to the data types of the data processing engine):
Oracle Data Types
Internal Types
NUMBER,INTEGER,INT,SMALLINT
Long
NUMERIC,DECIMAL,FLOAT,DOUBLE PRECISION,REAL
Double
LONG,CHAR,NCHAR,VARCHAR,VARCHAR2,NVARCHAR2,CLOB,NCLOB,CHARACTER,CHARACTER VARYING,CHAR VARYING,NATIONAL CHARACTER,NATIONAL CHAR,NATIONAL CHARACTER VARYING,NATIONAL CHAR VARYING,NCHAR VARYING
String
TIMESTAMP,DATE
Date
bit, bool
Boolean
BLOB,BFILE,RAW,LONG RAW
Bytes
Write
The supported data types for writing to Oracle and their conversion relationships are as follows:
Internal Types
Oracle Data Types
Long
NUMBER,INTEGER,INT,SMALLINT
Double
NUMERIC,DECIMAL,FLOAT,DOUBLE PRECISION,REAL
String
LONG,CHAR,NCHAR,VARCHAR,VARCHAR2,NVARCHAR2,CLOB,NCLOB,CHARACTER,CHARACTER VARYING,CHAR VARYING,NATIONAL CHARACTER,NATIONAL CHAR,NATIONAL CHARACTER VARYING,NATIONAL CHAR VARYING,NCHAR VARYING
