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.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.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.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].

MySQL

Postgres

Last modified 1mo ago