Links

Oracle

Connect & Ingest data from / to an Oracle database

Sling CLI 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 / Service Name of the instance
  • tns (optional) -> The Oracle TNS string of the instance
  • port (optional) -> The port of the instance. Default is 1521.
Here is an example 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 url
$ sling conns set ORACLE url=oracle://myuser:[email protected]:1521/<sid>

Environment Variable

export ORACLE='oracle://myuser:[email protected]:1521/<sid>'

Sling Cloud Setup

  • In Sling Cloud, click the Connections link on the left sidebar.
  • On top, click New Connection.
  • Search for "Oracle". Click on it.
  • Input the needed information.
  • Click Test to verify connectivity.
  • Click Save to save the connection.

Database user creation

To allow Sling to access your database, we need to create a user with the proper privileges. Please follow the steps below:
  1. 1.
    First you’ll need to login as system or sys. Create a user sling (or whatever you prefer) by running :
    CREATE USER SLING IDENTIFIED BY "<password>";
  2. 2.
    If you are planning to load data into this connection, grant the following privileges to that user so we can create tables:
    GRANT CREATE SESSION TO SLING;
    GRANT CREATE TABLE TO SLING;
    ALTER USER SLING QUOTA UNLIMITED ON USERS;
    ALTER DATABASE DEFAULT TABLESPACE USERS;
  3. 3.
    If you are planning to extract data from this connection, you need to give permission to read the tables you'd like Sling to extract.
    GRANT CREATE SESSION TO SLING;
    GRANT SELECT ON <schema_name>.<table_name> TO SLING;
    -- Script to give access to all tables in a specific schema
    CREATE PROCEDURE grant_select(
    schema_name VARCHAR2,
    grantee VARCHAR2)
    AS
    BEGIN
    FOR r IN (
    SELECT owner, table_name
    FROM all_tables
    WHERE owner = schema_name
    )
    LOOP
    EXECUTE IMMEDIATE
    'GRANT SELECT ON '||r.owner||'.'||r.table_name||' to ' || grantee;
    END LOOP;
    END;
    -- then run this to grant SELECT permission to all tables in schema MARKETING to user SLING (should be uppercase here)
    EXEC grant_select('MARKETING','SLING');

IP Address Whitelisting

If your database instance is secured behind a cloud firewall, please whitelist the IP addresses listed here. We may not be able to establish a connection without this step.
If you are facing issues connecting, please reach out to us at [email protected].
Last modified 1mo ago