Oracle
The Oracle destination writes data to tables in a Oracle database. For information about supported versions, see Supported Systems and Versions in the Data Collector documentation.
You can use the Oracle destination to write new data or change data capture (CDC) data to Oracle. The destination writes data from record fields to table columns based on matching names. The destination can compensate for data drift by creating new columns and tables in Oracle when new fields and table references appear in records.
The Oracle destination uses a JDBC connection string or Oracle host and service name to connect to the Oracle database. When you configure the destination, you specify the connection string or host and service name, and credentials to use to connect to the database. You can also use a connection to configure the destination.
You configure the Oracle database and tables to use, and specify error handling.
You can optionally enable data drift. You can have the destination create new tables, as needed. You can also specify whether to create all new columns as String instead of inferring the type.
Before you can use the Oracle destination, you must complete a prerequisite task.
Prerequisite
Before using the Oracle destination, install the Oracle JDBC driver for the database. The destination cannot access the database until you install this driver.
- Download the Oracle JDBC driver from the Oracle website.Note: Writing XML data to Oracle requires installing the Oracle Data Integrator Driver for XML. For more information, see the Oracle documentation.
- Install the driver as an external library into the JDBC branded Oracle stage
library,
streamsets-datacollector-jdbc-branded-oracle-lib
, which includes the origin.
Enabling Data Drift Handling
The Oracle destination can automatically compensate for changes in column or table requirements, also known as data drift.
- Create new columns
-
The destination can create new columns in Oracle tables when new fields appear in records. For example, if a record suddenly includes a new
Address2
field, the destination creates a newAddress2
column in the target table.By default, the destination creates new columns based on the data in the new fields, such as creating a Double column for decimal data. You can, however, configure the destination to create all new columns as String.
- Create new tables
- The destination can create new
tables as needed. For example, say the destination writes data to
tables based on the region name in the
Region
field. When a newSW-3
region shows up in a record, the destination creates a newSW-3
table in Oracle and writes the record to the new table.
Generated Data Types
When creating new tables or creating new columns in existing tables, the Oracle destination uses field names to generate the new column names.
Record Field Data Type | Oracle Column Data Type |
---|---|
Byte Array | Binary Float |
Char | Char |
String | Varchar2 |
Decimal | Number |
Long | Long |
Float | Float |
Integer | Number |
Short | Number |
Date | Date |
Datetime | Timestamp |
Zoned Datetime | Timestamp With Time Zone |
Double | Float |
The destination does not support nested Data Collector data types: List, List-Map, and Map. By default, the destination treats fields with invalid data types as an error record. You can configure the destination to ignore fields with invalid data types, replacing them with an empty value.
Creating Tables
If you configure the Oracle destination to handle data drift, you can also configure the destination to create tables. Once configured, the destination creates tables when the specified or needed tables do not exist in Oracle.
- Table
- The destination creates a table if the table specified in the Table Name property does not exist.
- Table columns
- In the created tables, the destination determines the columns from the first batch of data processed. The destination infers the data types from the data in the batch.
CRUD Operation Processing
The Oracle destination can insert, update, upsert, or delete data when you configure the destination to process CDC data.
sdc.operation.type
record header attribute. The destination
performs operations based on the following numeric values:- 1 for INSERT
- 2 for DELETE
- 3 for UPDATE
- 4 for UPSERT
If
your pipeline has a CRUD-enabled origin that processes
changed data, the destination simply reads the operation
type from the sdc.operation.type
header
attribute that the origin generates. If your pipeline has
a non-CDC origin, you can use the Expression Evaluator
processor or a scripting processor to define the record
header attribute. For more information about Data Collector changed data processing and a list of CDC-enabled
origins, see Processing Changed Data.
Configuring an Oracle Destination
Configure an Oracle destination to write data to Oracle tables.