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:[email protected]: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:

$ 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:[email protected]:1521/<sid>"

Environment Variable

export ORACLE='oracle://myuser:[email protected]: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:[email protected]:1521/<sid>"

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 [email protected], on discord or open a Github Issue here.