Oracle
Connect & Ingest data from / to an Oracle database
The following credentials keys are accepted:
host
(required) -> The hostname / ip of the instanceuser
(required) -> The username to access the instancepassword
(required) -> The password to access the instanceschema
(optional) -> This is the default schemasid
(optional) -> The Oracle System ID / Service Name of the instancetns
(optional) -> The Oracle TNS string of the instanceport
(optional) -> The port of the instance. Default is1521
.
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>
export ORACLE='oracle://myuser:[email protected]:1521/<sid>'
- 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.
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
orsys
. Create a usersling
(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 schemaCREATE PROCEDURE grant_select(schema_name VARCHAR2,grantee VARCHAR2)ASBEGINFOR r IN (SELECT owner, table_nameFROM all_tablesWHERE owner = schema_name)LOOPEXECUTE 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');
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.
Last modified 1mo ago