Oracle sink connector

Use the Oracle Sink Connector to get data from your Decodable streams to your Oracle Database. Currently, it only works with non-CDB deployments. Support for container databases (CDB) is forthcoming.

Features

Delivery guarantee

Exactly once

Read from multiple streams

Yes

Prerequisites

  • The Oracle database must be accessible from the Decodable network.

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

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

Steps

If you want to use the Decodable CLI or API to create the connection, you can refer to the Property Name column for information about what the underlying property names are. The connector name is oracle.

  1. From the Connections page, select Oracle and complete the following fields.

    UI Field

    Property Name

    Description

    Host

    hostname

    The host name for your Oracle database.

    Port

    port

    Optional. The port number to use when connecting to the host.

    Defaults to 1521.

    Service Name

    service-name

    The TNS service name for the database instance or container you are connecting to. This or the SID must be specified, but not both.

    System Identifier (SID)

    sid

    The identifier for the database instance or container you are connecting to. This or the Service Name must be specified, but not both.

    Username

    username

    The username to use to authenticate to Oracle.

    Password

    password

    The password associated with the username. This must be provided as a secret resource.

  2. Select Next. The next screen shows the list of Decodable streams in your account.

  3. Select the streams that you want to send to Oracle. Decodable automatically creates a table in your database for each stream selected. The new tables will be created in the schema defined by the username.

    By default, Decodable uses the stream name as the name of the table it creates. 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.

    Each stream’s schema is automatically translated to Oracle, including:

    • field names

    • data types (See Data types mapping for how Decodable types map to Oracle types)

    • primary keys

  4. (Optional) If you want to rename the tables to be created, you can override the generated table names.

  5. Select Next.

  6. Give the newly created connection a Name and Description. Then, select Save.

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 table below shows the mapping from Decodable data types to Oracle data types. 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

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

Resource specifier keys

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

Name Description

table-name

The table name