Skip to contentManaged Kafka is live: Apache Kafka on AWS for half the cost of MSK

Destinations

Oracle

How to configure Oracle as a destination in Artie: connecting to a pluggable database's service name, creating the service account, choosing schemas, and how Artie maps column types.

Required settings

  • Host name
  • Port (default is 1521)
  • Service account
  • Service name of the pluggable database (PDB) to write to, for example ORCLPDB1
  • Schema name

Artie connects straight to the PDB’s service, so a destination doesn’t need a container name. That’s different from an Oracle source, which reads a multi-tenant database from the container database (CDB).

Creating a service account

Run this in the pluggable database Artie writes to:

SQL
-- An Oracle schema is a user, so Artie writes to the ARTIE schema by default.
CREATE USER ARTIE IDENTIFIED BY "AStrongPassword1" QUOTA UNLIMITED ON USERS;
GRANT CREATE SESSION, CREATE TABLE TO ARTIE;

To write to a schema other than the service account’s own, also grant:

SQL
GRANT SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO ARTIE;
GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE TO ARTIE;

Tables count against the tablespace quota of the schema that owns them, not the service account’s.

Schemas

In Oracle, a schema is a user. Artie doesn’t create schemas, so the schema you pick, and the staging schema if you set one, has to exist before the pipeline starts:

SQL
CREATE USER ANALYTICS NO AUTHENTICATION QUOTA UNLIMITED ON USERS;

NO AUTHENTICATION creates a schema-only account that nobody can log in as. It’s available from Oracle 18c.

Leave Create missing schemas off. The Dashboard shows it for Oracle, but Artie can’t create Oracle schemas, so with it on, a deploy that needs a missing schema fails.

Artie creates and evolves tables inside the schema, and drops its own expired staging tables.

Table and column names

Artie quotes every identifier and writes it in uppercase, so a source column named orderId lands as ORDERID. Schema names are matched case-insensitively for the same reason.

Table names can be up to 128 bytes and can’t contain double quotes.

Data types

Source typeOracle type
BooleanNUMBER(1, 0)
Small integerNUMBER(5, 0)
IntegerNUMBER(10, 0)
Big integerNUMBER(19, 0)
Float, doubleBINARY_DOUBLE
Decimal with precision up to 38NUMBER(p, s)
Decimal without a precisionNUMBER
Decimal with precision over 38VARCHAR2(4000 BYTE), or CLOB if it doesn’t fit
String with a known length up to 1,000VARCHAR2(n CHAR)
Other stringsCLOB (VARCHAR2(1000 CHAR) for a primary key)
UUIDVARCHAR2(36 CHAR)
JSON, arraysCLOB
BinaryBLOB (RAW(2000) for a primary key)
DateDATE
TimeVARCHAR2(32), for example 13:45:00.123456
TimestampTIMESTAMP(6)
Timestamp with time zoneTIMESTAMP(6) WITH TIME ZONE
IntervalVARCHAR2(255)

Advanced

Does an Oracle to Oracle pipeline keep the source's column types?

Yes. When the source is also Oracle, Artie creates each column with the source’s exact type, for example NUMBER(10,2), VARCHAR2(40 BYTE) or TIMESTAMP(9) WITH LOCAL TIME ZONE. INTEGER lands as the equivalent NUMBER(38,0).

It falls back to the mapping above when a type can’t be written as is:

  • LONG and LONG RAW, which Oracle can’t read in a MERGE
  • INTERVAL types
  • CLOB, NCLOB, BLOB and JSON primary keys
  • RAW and BLOB columns, unless binary values are written as raw bytes rather than base64 text
What happens to a value that's too long for its column?

Artie replaces it with __artie_exceeded_value, cut to the column’s width if the column is narrower. Primary key values are never replaced, so an oversized key fails the load instead of colliding with another row.

Artie reads MAX_STRING_SIZE and the database’s character sets when it connects. With MAX_STRING_SIZE = EXTENDED, a VARCHAR2 column holds up to 32,767 bytes, so fewer values are replaced.