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:
-- 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:
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:
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 type | Oracle type |
|---|---|
| Boolean | NUMBER(1, 0) |
| Small integer | NUMBER(5, 0) |
| Integer | NUMBER(10, 0) |
| Big integer | NUMBER(19, 0) |
| Float, double | BINARY_DOUBLE |
| Decimal with precision up to 38 | NUMBER(p, s) |
| Decimal without a precision | NUMBER |
| Decimal with precision over 38 | VARCHAR2(4000 BYTE), or CLOB if it doesn’t fit |
| String with a known length up to 1,000 | VARCHAR2(n CHAR) |
| Other strings | CLOB (VARCHAR2(1000 CHAR) for a primary key) |
| UUID | VARCHAR2(36 CHAR) |
| JSON, arrays | CLOB |
| Binary | BLOB (RAW(2000) for a primary key) |
| Date | DATE |
| Time | VARCHAR2(32), for example 13:45:00.123456 |
| Timestamp | TIMESTAMP(6) |
| Timestamp with time zone | TIMESTAMP(6) WITH TIME ZONE |
| Interval | VARCHAR2(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:
LONGandLONG RAW, which Oracle can’t read in aMERGEINTERVALtypesCLOB,NCLOB,BLOBandJSONprimary keysRAWandBLOBcolumns, 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.