Oracle sink connector

Features

Connector name

oracle

Delivery guarantee

Exactly once

Supported task sizes

S, M, L

Multiplex capability

A single instance of this connector can write to multiple tables in a single schema and database

Supported stream types

The connector only works with non-CDB deployments. Support for container databases (CDB) is forthcoming.

Configuration properties

Property Description Required Default

Property	Description	Required	Default
`hostname`	The host name for your Oracle database.	Yes
`port`	The port number to use when connecting to the host.	—	`1521`
`service-name`	The TNS service name for the database instance or container you are connecting to.	One of `service-name` or `sid` must be specified, but not both.
`sid`	The identifier for the database instance or container you are connecting to.
`username`	The username to use to authenticate to Oracle	Yes
`password`	The password associated with the username. This must be provided as a secret resource.	Yes

hostname

The host name for your Oracle database.

Yes

port

The port number to use when connecting to the host.

—

1521

service-name

The TNS service name for the database instance or container you are connecting to.

One of service-name or sid must be specified, but not both.

sid

The identifier for the database instance or container you are connecting to.

username

The username to use to authenticate to Oracle

Yes

password

The password associated with the username.

This must be provided as a secret resource.

Yes

Prerequisites

The Oracle database must be accessible from the Decodable network. Connectivity options include AWS PrivateLink, SSH tunnels, and allowing connections from the Decodable published IP addresses.

You must have an Oracle user that’s granted the following privileges:

GRANT CREATE SESSION TO <your_user>;
GRANT CREATE TABLE TO <your_user>;

Table names

By default, Decodable uses the stream name as the name of the table it writes to. If a table already exists with that name and the schema of the stream matches the schema of the table, Decodable will write to the existing table. If it doesn’t exist, Decodable will create it.

You can change the name of the table to which Decodable writes either in the web interface, or by using output-resource-name-template when calling decodable connection scan.

The schema of each stream is automatically translated to Oracle, including:

field names
data types (See data types for how Decodable types map to Oracle types)
primary keys

Writing data to multiple tables

A single instance of this connector can write to multiple tables in a single schema and database

If you are using the CLI to create or edit a connection with this connector, you must use the declarative approach. You can generate the connection definition for the tables that you want to write to decodable connection scan.

Resource specifier keys

When using the decodable connection scan command of the Decodable CLI to create a connection specification, the following resource specifier keys are available:

Name Description

Name	Description
`table-name`	The table name

table-name

The table name

Connector starting state and offsets

A new sink connection will start reading from the Latest point in the source Decodable stream. This means that only data that’s written to the stream when the connection has started will be sent to the external system. You can override this when you start the connection to Earliest if you want to send all the existing data on the source stream to the target system, along with all new data that arrives on the stream.

When you restart a sink connection it will continue to read data from the point it most recently stored in the checkpoint before the connection stopped. You can also opt to discard the connection’s state and restart it afresh from Earliest or Latest as described above.

Learn more about starting state here.

Data types mapping

The following table describes the mapping of Decodable data types to their Oracle data type counterparts. Oracle also allows columns to be declared with ANSI data type names but uses a corresponding Oracle data type internally. For more information, see the Oracle docs.

Decodable Type Oracle Type

Decodable Type	Oracle Type
`CHAR(n)` (1 <= n <= 2000)	`NCHAR(n)`
`CHAR(n)` (n > 2000)	`NCLOB`
`VARCHAR(n)` (1 <= n <= 4000)	`NVARCHAR2(n)`
`VARCHAR(n)` (n > 4000)	`NCLOB`
`STRING`	`NCLOB`
`BOOLEAN`	`NUMBER(1)`
`BINARY(n)` (1 <= n <= 2000)	`RAW(n)`
`BINARY(n)` (n > 2000)	`LONG RAW`
`VARBINARY(n)`	`LONG RAW`
`DECIMAL(p, s)`	`NUMBER(p,s)`
`TINYINT`	`NUMBER(3)`
`SMALLINT`	`NUMBER(5)`
`INT/INTEGER`	`NUMBER(10)`
`BIGINT`	`NUMBER(19)`
`FLOAT`	`BINARY_FLOAT`
`DOUBLE [PRECISION]`	`BINARY_DOUBLE`
`DATE`	`DATE`
`TIMESTAMP(p) [WITHOUT TIME ZONE]` (p = 0)	`DATE`
`TIMESTAMP(p) [WITHOUT TIME ZONE]` (0 < p <= 9)	`TIMESTAMP(p)`
`TIMESTAMP(p) WITH LOCAL TIME ZONE` (0 <= p <= 9)	`TIMESTAMP(p) WITH LOCAL TIME ZONE`
`TIMESTAMP_LTZ(p)` (0 <= p <= 9)	`TIMESTAMP(p) WITH LOCAL TIME ZONE`

CHAR(n)

(1 <= n <= 2000)

NCHAR(n)

CHAR(n)

(n > 2000)

NCLOB

VARCHAR(n)

(1 <= n <= 4000)

NVARCHAR2(n)

VARCHAR(n)

(n > 4000)

NCLOB

STRING

NCLOB

BOOLEAN

NUMBER(1)

BINARY(n)

(1 <= n <= 2000)

RAW(n)

BINARY(n)

(n > 2000)

LONG RAW

VARBINARY(n)

LONG RAW

DECIMAL(p, s)

NUMBER(p,s)

TINYINT

NUMBER(3)

SMALLINT

NUMBER(5)

INT/INTEGER

NUMBER(10)

BIGINT

NUMBER(19)

FLOAT

BINARY_FLOAT

DOUBLE [PRECISION]

BINARY_DOUBLE

DATE

TIMESTAMP(p) [WITHOUT TIME ZONE]

(p = 0)

DATE

TIMESTAMP(p) [WITHOUT TIME ZONE]

(0 < p <= 9)

TIMESTAMP(p)

TIMESTAMP(p) WITH LOCAL TIME ZONE

(0 <= p <= 9)

TIMESTAMP(p) WITH LOCAL TIME ZONE

TIMESTAMP_LTZ(p)

(0 <= p <= 9)

TIMESTAMP(p) WITH LOCAL TIME ZONE