# Oracle

## 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:password@db.host: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`.

### 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:

{% code overflow="wrap" %}

```bash
$ 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:mypass@host.ip:1521/<sid>"
```

{% endcode %}

### Environment Variable

See [here](https://docs.slingdata.io/connections/database-connections/pages/eAdVs2BHCgdr6RS8GoJC#dot-env-file-.env.sling) to learn more about the `.env.sling` file.

{% code overflow="wrap" %}

```bash
export ORACLE='oracle://myuser:mypass@host.ip:1521/<sid>'
export ORACLE='{ type: oracle, user: "myuser", password: "mypass", host: "host.ip", port: 1521, sid: "<sid>" }'
```

{% endcode %}

### Sling Env File YAML

See [here](https://docs.slingdata.io/connections/database-connections/pages/eAdVs2BHCgdr6RS8GoJC#sling-env-file-env.yaml) to learn more about the sling `env.yaml` file.

```yaml
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:mypass@host.ip:1521/<sid>"
```

## LOB, XML and Binary Types

{% hint style="info" %}
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+**.
{% endhint %}

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`).

## 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](https://github.com/godror/godror) 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](https://github.com/sijms/go-ora) 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 <support@slingdata.io>, on [discord](https://discord.gg/q5xtaSNDvp) or open a Github Issue [here](https://github.com/slingdata-io/sling-cli/issues).


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.slingdata.io/connections/database-connections/oracle.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.