Oracle

This guide provides step-by-step instructions for connecting Oracle Database to Unistream.

Prerequisites

You will need:

• Access to your Oracle Database instance

• DBA privileges to create a new user (or existing user credentials)

• Host address and port number

• Service Name or SID

• Unistream platform credentials

---

Step 1: Configure Access in Oracle

Before connecting to Unistream, you need to create a dedicated user in Oracle and grant appropriate permissions.

1.1 Create User

Connect to your Oracle Database (using SQL*Plus, SQL Developer, or another tool) and run the following commands:

-- Create a new user with password
CREATE USER <username> IDENTIFIED BY <password>;

-- Grant connect privilege
GRANT CONNECT TO <username>;

-- Grant session creation privilege
GRANT CREATE SESSION TO <username>;

Replace:

• <username> with your desired username

• <password> with a secure password

1.2 Grant Permissions

Choose the appropriate permission level based on your needs:

Option A: Grant Access to All Tables in a Schema

-- Grant select on all tables in a schema
GRANT SELECT ANY TABLE TO <username>;

-- Or grant select on specific schema
GRANT SELECT ON <schema>.* TO <username>;

Replace:

• <schema> with your schema name

Note: For more granular control, use Option B or C instead of SELECT ANY TABLE.

Option B: Grant Access to Specific Tables

-- Grant select on specific tables
GRANT SELECT ON <schema>.<table1> TO <username>;
GRANT SELECT ON <schema>.<table2> TO <username>;

Replace:

• <schema> with your schema name

• <table1>, <table2>, etc. with your table names

Option C: Grant Access to Specific Columns

-- Grant select on specific columns
GRANT SELECT (<column1>, <column2>, ...) ON <schema>.<table> TO <username>;

Replace:

• <schema> with your schema name

• <table> with your table name

• <column1>, <column2>, etc. with your column names

1.3 Example: Complete Setup

Here's a complete example for a schema named SALES:

-- Create user
CREATE USER unistream_user IDENTIFIED BY SecureP@ssw0rd123;

-- Grant connect and session privileges
GRANT CONNECT TO unistream_user;
GRANT CREATE SESSION TO unistream_user;

-- Grant select on all tables in SALES schema
GRANT SELECT ON SALES.CUSTOMERS TO unistream_user;
GRANT SELECT ON SALES.ORDERS TO unistream_user;
GRANT SELECT ON SALES.PRODUCTS TO unistream_user;

-- Optional: Grant access to dictionary views for metadata
GRANT SELECT ON ALL_TABLES TO unistream_user;
GRANT SELECT ON ALL_TAB_COLUMNS TO unistream_user;

---

Step 2: Connect to Unistream

2.1 Add Oracle Source

1. In Unistream, navigate to Lake

2. Click Add Source

3. Select Oracle

2.2 Configure Connection

Fill in the General Fields:

• Name: Enter a descriptive name for your connection (e.g., "Production Oracle")

• Code: Enter a unique code identifier (e.g., "prod_oracle")

Fill in the Connection Details:

• Host: Your Oracle server address

• Port: Oracle listener port (default: 1521)

• Username: The username you created in Step 1 (e.g., unistream_user)

• Password: The password you set in Step 1

• (Optional) Database: The Oracle service name

• Use SID: Check this box if you're using SID instead of Service Name (for older versions of Oracle)

  • When checked, enter the Oracle SID in the Database field instead of Service Name

Note: By default, the Database field expects a Service Name. Only check "Use SID" for older Oracle installations that use SID instead of Service Name.

2.3 Test and Save

1. Click Test Connection to verify the connection is working

2. If the connection is successful, you'll see a success message

3. Click Add to save your connection

Need Help?

If you encounter any issues not covered in this guide, please contact Unistream support