Oracle DB
Write data to an Oracle Database table using MERGE for upsert. Use Zeotap to materialize model results, enriched profiles, or audience data into Oracle Database.
Prerequisites
- An Oracle Database instance (version 12c or later recommended for full MERGE support)
- A database user with INSERT, UPDATE, DELETE, CREATE TABLE, and ALTER TABLE privileges
- Network connectivity between Zeotap and the Oracle Database server
- The Oracle service name for your database instance
Authentication
Oracle DB uses Username & Password authentication.
- Enter the Oracle Database Username and Password in Zeotap
- The user must have sufficient privileges to create and modify tables in the target schema
Configuration
| Field | Type | Required | Description |
|---|---|---|---|
| Host | Text | Yes | Oracle Database server hostname or IP address |
| Port | Number | No | Listener port. Default: 1521 |
| Service Name | Text | Yes | The Oracle service name (e.g. ORCL, XEPDB1) used to identify the database instance |
Target Settings
| Field | Type | Required | Description |
|---|---|---|---|
| Schema | Text | No | Database schema (owner) to write to. Defaults to the authenticated user |
| Table | Text | Yes | Target table name |
Supported Operations
Sync Modes: Upsert, Insert, Update, Mirror
| Mode | Description |
|---|---|
| Upsert | Uses Oracle MERGE to insert new rows and update existing rows matched by primary key |
| Insert | Inserts only new rows into the target table |
| Update | Updates only changed rows matched by primary key |
| Mirror | Performs upsert for added/changed rows, then deletes removed rows to keep the target table in sync |
Audience Sync Modes: Add, Remove, Mirror, Upsert
| Mode | Description |
|---|---|
| Add | Inserts audience members into the target table |
| Remove | Deletes audience members from the target table by primary key |
| Mirror | Full sync — adds new members, removes departed members |
| Upsert | Adds or updates audience members by primary key |
Features
- Field Mapping: Yes
- Schema Introspection: Yes — Zeotap reads column metadata from Oracle’s data dictionary views
- Auto Table Creation: Yes — Zeotap creates the target table automatically on the first sync if it does not exist
- Auto Column Addition: Yes — Zeotap adds missing columns to the target table automatically during sync
How It Works
- Staging table: Zeotap creates a temporary staging table in the database to hold the batch data
- Batch insert: Rows are inserted into the staging table using Oracle
INSERT ALLstatements in sub-batches of up to 500 rows - MERGE/DML: Depending on the sync mode, Zeotap executes a MERGE statement (for upsert), INSERT (for insert), or DELETE (for remove) against the target table using the staging data
- Cleanup: The staging table is dropped after each batch completes
Rate Limits
Oracle Database does not impose API-level rate limits. Performance depends on your database instance resources (CPU, memory, I/O) and network bandwidth. For large syncs, ensure adequate redo log and tablespace capacity.
Best Practices
- Use a dedicated Oracle user for Zeotap syncs with only the necessary privileges
- Ensure the target tablespace has sufficient free space for staging tables and data growth
- For large tables, create indexes on the primary key column before the first sync to improve MERGE performance
- Use the Schema target setting to write to a specific schema rather than the default user schema
- Monitor Oracle alert logs and AWR reports during initial large syncs to tune performance
Troubleshooting
ORA-12541: TNS:no listener
Verify the host and port are correct, and that the Oracle TNS listener is running on the target server. Check that the listener is configured to accept connections on the specified port.
ORA-12514: TNS:listener does not currently know of service requested
The service name is incorrect or the database instance is not registered with the listener. Verify the service name matches your Oracle instance configuration. Use lsnrctl status on the server to list available services.
ORA-01017: invalid username/password; logon denied
Double-check the username and password. Oracle usernames are case-insensitive by default, but passwords are case-sensitive in Oracle 11g and later.
ORA-01031: insufficient privileges
The Oracle user needs CREATE TABLE, ALTER TABLE, INSERT, UPDATE, and DELETE privileges on the target schema. Grant the necessary privileges or use a user with the DBA role for testing.
ORA-01652: unable to extend temp segment
The tablespace is full. Increase the tablespace size or add additional datafiles. For the staging table operations, ensure there is enough temporary or permanent tablespace available.
Connection timeout
Verify network connectivity between Zeotap and the Oracle server. Check firewall rules and ensure port 1521 (or the configured port) is open. For cloud-hosted Oracle instances, verify the network security group or VPC settings allow inbound connections.