Oracle

Connect & Ingest data from / to an Oracle database

Setup

The following credentials keys are accepted:

• host (required) -> The hostname / ip of the instance

• user (required) -> The username to access the instance

• password (required) -> The password to access the instance

• schema (optional) -> This is the default schema

• sid (optional) -> The Oracle System ID of the instance

• service_name (optional) -> The Oracle Service Name of the instance

• tns (optional) -> The Oracle TNS string of the instance (example: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=my-oracle-database.provider.com)(PORT=1521))(CONNECT_DATA=(SID=my_service)))).

• port (optional) -> The port of the instance. Default is 1521.

• ssh_tunnel (optional) -> The URL of the SSH server you would like to use as a tunnel (example ssh://user:[e-mail]:22)

• ssh_private_key (optional) -> The private key to use to access a SSH server (raw string or path to file).

• ssh_passphrase (optional) -> The passphrase to use to access a SSH server.

• jdbc_str (optional) -> The JDBC connection string to use. No need to provide user, password or host

• sqlldr_path (v1.3.0)-> The path to the sqlldr binary. This is useful if you need to specify a custom path for sqlldr.

• prefetch_rows (v1.5.23) (optional) -> The number of rows to fetch per network round-trip when reading. Default is 100. See Read Performance.

Additional Parameters

• auth_type (optional) -> The auth type to use. Options include: OS, TCPS

• auth_server (optional) -> The Windows auth server to use. Options include: NTS

• os_user (optional) -> The Windows operating system user (with auth_type=OS)

• os_password (optional) -> The Windows operating system password (with auth_type=OS)

• domain (optional) -> Windows system domain name (with auth_type=OS)

• proxy_client_name (optional) -> to use proxy authentication

Using sling conns

Here are examples of setting a connection named ORACLE. We must provide the type=oracle property:

$ sling conns set ORACLE type=oracle host=<host> port=<port> sid=<sid> user=<user> password=<password> schema=<schema>

# OR use JDBC connection string
$ sling conns set ORACLE type=oracle jdbc_str=<jdbc_str>

# OR use url
$ sling conns set ORACLE url="oracle://myuser:[e-mail]:1521/<sid>"

Environment Variable

See here to learn more about the .env.sling file.

export ORACLE='oracle://myuser:[e-mail]:1521/<sid>'
export ORACLE='{ type: oracle, user: "myuser", password: "mypass", host: "host.ip", port: 1521, sid: "<sid>" }'

Sling Env File YAML

See here to learn more about the sling env.yaml file.

connections:
  ORACLE:
    type: oracle
    host: <host>
    user: <user>
    port: <port>
    sid: <sid>                   # sid or service_name
    service_name: <service_name> # sid or service_name
    tns: <tns>
    schema: <schema>
    password: <password>

  ORACLE_JDBC:
    type: oracle
    jdbc_str: <jdbc_str>

  ORACLE_URL:
    url: "oracle://myuser:[e-mail]:1521/<sid>"

LOB, XML and Binary Types

Byte-exact round-trip for large Oracle binary types (BLOB, LONG RAW) up to the target's per-value limit (64 MB on Snowflake) is available in v1.5.19+.

Sling supports every Oracle large-object, binary and structured type as a source. Each maps to a generic Sling type, which then maps to the target's native type:

Oracle source type	Sling generic type	Notes
CLOB	text	Character LOB. Streamed without loading the full value into memory.
NCLOB	text	National-character LOB. Converted from AL16UTF16 to UTF-8 on most targets.
BLOB	binary	Binary LOB. Bytes preserved exactly.
RAW	binary	Fixed-length raw bytes up to RAW(2000).
LONG RAW	binary	Legacy large binary (up to 2 GB). Treated like BLOB.
LONG	string	Legacy large character (up to 2 GB). Only one LONG or LONG RAW column per table.
BFILE	binary	Pointer to an external OS file. Sling reads the locator bytes, not the file contents.
XMLTYPE	text	Auto-cast to CLOB via .getclobval() so it streams as text.

Practical per-value limits are set by the target:

Target	Text	Binary
Snowflake	128 MB (VARCHAR)	64 MB (BINARY)
PostgreSQL	1 GB (text)	1 GB (bytea)
BigQuery	10 MiB (STRING)	10 MiB (BYTES) per row total
File targets	bounded by file format and disk	bounded by file format and disk

For Snowflake, Sling generates BINARY(67108864) DDL and stages via CSV (TO_BINARY('HEX')) by default. For binary-heavy workloads, opt into Parquet staging (target_options.format = parquet).

Read Performance

The prefetch_rows setting is available in v1.5.23+. Prior versions used a fixed value of 25, which could significantly slow down large reads.

When reading from Oracle, Sling fetches rows in batches. The prefetch_rows setting controls how many rows are retrieved per network round-trip. Since each round-trip costs a full network latency, a low value dominates the total read time on large tables.

Sling defaults to 100, a conservative value that is safe for tables with wide rows or large LOB columns. If extraction feels slow — especially over a higher-latency network or when moving millions of rows — raising this value is the first thing to try, and often the single biggest win:

connections:
  ORACLE:
    type: oracle
    host: <host>
    user: <user>
    password: <password>
    sid: <sid>
    prefetch_rows: 5000

It can also be set via sling conns or in a connection URL:

$ sling conns set ORACLE type=oracle host=<host> user=<user> password=<password> sid=<sid> prefetch_rows=5000

# OR as a URL query parameter
$ sling conns set ORACLE url="oracle://myuser:[e-mail]:1521/<sid>?prefetch_rows=5000"

Choosing a value

Scenario	Suggested value
Default	100
Narrow rows, moving millions of records	1000 – 5000
Large tables on a low-latency / local network	5000 or higher
Tables with large BLOB / CLOB columns	25 – 100

Higher values reduce round-trips but increase memory usage, since each batch is buffered in memory. The cost scales with prefetch_rows × row width, so tables with wide rows or large LOB columns need lower values. This matters most when many streams run concurrently on the same agent — if you encounter memory pressure, lower prefetch_rows before reducing concurrency.

Oracle Client Dependency

Until version 1.1.13, there was a dependency on the Oracle Client for Sling to work. This is because sling used to use a 3rd party driver which needs it. You can install it by following directions:

• MacOS: https://odpi-c.readthedocs.io/en/latest/user_guide/installation.html#scripted-installation

• Linux: https://odpi-c.readthedocs.io/en/latest/user_guide/installation.html#linux

• Windows: https://odpi-c.readthedocs.io/en/latest/user_guide/installation.html#windows

Starting in v1.1.14, Sling uses another library for Oracle, which does not need the Oracle client for connection. However, there is an advantage of having the Oracle client installed, as it contains the sqlldr tool, which sling can use to load data (if present in PATH). Loading data with sqlldr can be much faster.

If you are facing issues connecting, please reach out to us at [e-mail], on discord or open a Github Issue here.