Oracle Driver Documentation

This documentation explains the Oracle driver implementation and how connection parameters map to UI fields in the Gigantic application.

Connection Parameters

Technical Details

The Oracle driver implementation uses the oracledb Node.js library with connection pooling capabilities.

Key technical aspects:

• Connection pooling is used through the Pool class from oracledb
• Owner and Container (PDB) concepts are specific to Oracle
• The driver uses DBMS_METADATA package for DDL generation
• Virtual column handling in the normalizeRecordOracle method
• Multiple load modes supported: insert, update, merge

When SSH tunneling is enabled, connections are first established through the tunnel before connecting to the Oracle server.

Connection String Format

Oracle connections combine individual UI fields into a connection string. Two formats are supported:

1. Easy Connect Syntax (default):

//host:port/database

2. Direct Connect String:

user/password@//host:port/database

The application takes the individual fields from the UI (Host, Port, Database) and combines them into format #1 for the oracledb library.

Authentication Options

Oracle supports several authentication types:

SSH Tunnel Support

The Oracle driver supports SSH tunneling for secure access to databases behind firewalls or in private networks. For detailed information on configuring SSH tunnels, please refer to the SSH Tunnelling documentation.

API Endpoints Used

Oracle connections are primarily used in:

• Tap creation (data source discovery)
• Sink creation (data destination for anonymized data)
• Pipeline execution (data extraction and loading)

Large Tables and Resume

This section lists only what is specific to Oracle. How resume, parallel ranges and row estimates work in every driver is explained in Large databases.

Reading

• Tables are read in chunks ordered by the primary key, so a job that stops resumes after the last key it saved. A table with no primary key is read with one cursor and starts over if the job stops: ROWID is not used, because ordering by it sorts the rest of the table on every chunk.
• Every primary key column must be a NUMBER (with any precision and scale, or none, such as NUMBER, NUMBER(10) or INTEGER) or text (CHAR, VARCHAR2, NVARCHAR2). Key values are read and compared as text, so large and decimal numbers resume exactly. A key with a FLOAT, BINARY_FLOAT or BINARY_DOUBLE column is not used, so that table is read with one cursor. Text keys are compared with NLS_SORT and NLS_COMP set to BINARY.

Parallel Ranges and Row Estimates

• Primary key ranges are cut from a SAMPLE BLOCK ... SEED sample of the key, sized from the BLOCKS statistic of the table. When the sample is too small, a single integer key is split evenly between its minimum and maximum.
• The row estimate is NUM_ROWS from ALL_TABLES (USER_TABLES when no Owner is set). A table with no statistics has none, and the row count saved in the model is used instead: gather statistics on big tables (DBMS_STATS.GATHER_TABLE_STATS).

Loading

• Before loading, the job disables the foreign keys of the tables it loads and the foreign keys of other tables that reference them. Each one is first recorded in the table GIGANTICS_DISABLED_CONSTRAINTS, created in the Owner schema (or in the user's schema when no Owner is set). At the end only the recorded ones are enabled again, with ENABLE VALIDATE, and their records removed. A foreign key that was disabled before the job stays disabled.
• If a job dies, the records stay, and the next job that loads those tables enables them. A foreign key that fails ENABLE VALIDATE (because the loaded rows break it) stays disabled and recorded, and a warning is logged.
• If the records table cannot be created, the job logs a warning and, at the end, enables every disabled foreign key of those tables.
• Triggers of the loaded tables are disabled with DISABLE ALL TRIGGERS and enabled with ENABLE ALL TRIGGERS at the end. They are not recorded: if a job dies, they stay disabled until a later load of those tables ends.
• The truncate option runs TRUNCATE TABLE.
• Update and merge loads go through a global temporary table and a MERGE that matches all the columns of a composite primary key.
• When some rows of a batch fail, the job logs a warning with the number of failed rows and the first error, and commits the rest of the batch.

Permissions

For an Oracle sink, the database user needs:

• CREATE TABLE and quota on its tablespace, for GIGANTICS_DISABLED_CONSTRAINTS and the merge staging tables. When Owner is another schema, both are created and written in that schema, which needs privileges such as CREATE ANY TABLE.
• SELECT on DBA_CONSTRAINTS when Owner is set (for example through SELECT_CATALOG_ROLE).
• ALTER on the tables it loads and on the tables whose foreign keys reference them (ALTER ANY TABLE when they belong to another schema).

Custom Params

The Oracle driver supports additional custom parameters that can be specified in YAML format to fine-tune connection behavior, pooling, SSL settings, and other advanced options.

Connection Attributes

# Connection Configuration
stmtCacheSize: 30
edition: "ORA$BASE"
events: false
externalAuth: false
privilege: "SYSDBA"  # or "SYSOPER", "SYSASM", "SYSBACKUP", "SYSDG", "SYSKM", "SYSRAC"

# Sharding Support
shardingKey: ["key1", "key2"]
superShardingKey: ["super_key1", "super_key2"]

# Session Tagging
tag: "production_app"

Pool Attributes

# Pool configuration
poolAlias: "mypool"
poolMin: 4
poolMax: 10
poolIncrement: 1
poolTimeout: 60
poolPingInterval: 60
poolMaxPerShard: 5

# Queue management
queueMax: 50
queueRequests: true
queueTimeout: 60000

# Pool statistics
enableStatistics: true

SSL/TLS Configuration

# SSL Configuration
sslServerCertDN: "CN=server.example.com,O=Example Corp,C=US"
sslServerDNMatch: true
sslAllowWeakDNMatch: false
useSNI: true

# Oracle Wallet
walletLocation: "/path/to/oracle/wallet"
walletPassword: "wallet_password"
walletContent: ${file:/path/to/wallet.zip}

Token-Based Authentication

# Token-based authentication
accessToken:
  token: "valid_access_token_string"
  privateKey: ${file:/path/to/private_key.pem}
  type: "OCI_TOKEN"  # or "OAUTH2_TOKEN"

Example Configurations

Basic Configuration

poolMin: 2
poolMax: 15
stmtCacheSize: 50

SSL Configuration with Wallet

walletLocation: ${file:/path/to/oracle_wallet}
walletPassword: wallet_pass
sslServerDNMatch: true
enableStatistics: true
poolTimeout: 30

Multitenant Configuration

owner: app_owner
poolMax: 20
queueTimeout: 30000

Token-Based Authentication

accessToken:
  token: ${env:OCI_ACCESS_TOKEN}
  type: "OCI_TOKEN"
stmtCacheSize: 100
events: true