Oracle Database
The Oracle connector reads tables and stored procedure results, and writes rows to tables, in Oracle Database. It connects by service name, SID or a TNS connect string.
When to use it
- You read from or write to Oracle Database.
- You call a stored procedure as a step in the data pipeline.
- You need a staging table created and dropped around a load, with a DB Script node.
What you need
- The host and port the runtime can reach, and the service name or SID. Or a full TNS connect string.
- A database user and password. The user needs read access for Sources and write access for loads.
Connect
The connection form lists its steps on the left. Open any step from that list. The Environments step holds the values that differ per environment.
Test connection is in the form footer. Beside it, Test against picks Base, the connection as saved, or an environment, whose values are applied first.
| Field | Step | What to enter |
|---|---|---|
| Username | Authentication | The database user. |
| Password | Authentication | The user's password. Required to connect. |
| Host | Connection | Host name or IP address. Not shown for TNS Name. |
| Port | Connection | Default 1521. Not shown for TNS Name. |
| Connect By | Connection | Service Name, SID or TNS Name. Default Service Name. |
| Service Name | Connection | The service name, such as XEPDB1. |
| SID | Connection | The system identifier. |
| TNS Name | Connection | A connect descriptor or Easy Connect string, sent to Oracle as written. |
| Service / SID | Connection | Used when the field for the chosen Connect By is blank. |
| Connect Timeout (ms) | Options | Default 30000. |
| Pool Max | Options | Maximum open connections. Default 10. |
| Note | Options | Free text for your team. |
A TNS alias, rather than a full descriptor, resolves only on a runtime that has
a tnsnames.ora defining it, such as a Private Agent.
Supported operations
| Action | Node | Operations |
|---|---|---|
| Table Query (Source) | Source | Query, in No-code or SQL mode |
| Table Load (Target) | Load | Insert, Update, Upsert, Delete |
| Stored Procedure (Source) | Source | Execute |
| Stored Procedure (Target) | Load | Execute |
| DDL Script | DB Script | Create, Alter, Drop, Truncate, Rename |
Limits
- A table with a single-column primary key is read by seeking that key. Other objects are read in a fixed order with an offset, which is slower on a large table.
- Upsert, Update and Delete loads send rows in batches, not one statement per row.
- Batch size is set in bytes, from each column's declared length.
Troubleshooting
- Test connection fails with correct credentials. The database must accept connections from the runtime that runs the data pipeline, not from your computer. For a database on a private network, run a Private Agent inside that network or allow-list the runtime's address.
- The test passes but Configure Action lists no objects. The user can sign in but cannot see the catalog. Grant it read access on the schema, then press Get Metadata again.
- A column is missing from the field list. The list is read when metadata is loaded. Press Get Metadata again to read the current columns.
- A load fails on one batch. The run log names the statement and the batch. Check the rows in that batch against the column types and lengths.
- ORA-12514 or ORA-12505 on test. The listener does not know the service name or SID. Check Connect By and the value for that mode.
Related
- Source node, Load node and DB Script node.
- Connections for environments and sharing.
- PostgreSQL, MySQL, SQL Server and Snowflake.