Oracle Database

Connect to your Oracle database.

Getting Started

Prerequisites to create an Oracle Database based workflow. You will need

1. A source Oracle Database connection.

2. (optional) A list of tables OR SQL queries.

3. (optional) A destination Oracle Database connection OR object storage connection.

For the source database connection, we recommend using a backup or clone with read-only permissions, instead of connecting directly to your production database.

Do not use your input database connection as an output connector. This action can result in the unintended overwriting of existing data.

Create a Connection

A oracle connection is created using the following parameters:

Connection Creation Parameters

name	Display name of your choosing used to identify your connection within Gretel.	my-oracle-connection
username	Unique identifier associated with specific account authorized to access database. The connection will be to this user's schema.	john
password	Security credential to authenticate username.	...
host	Fully qualified domain name (FQDN) used to establish connection to database server.	myserver.example.com
port	Optional Port number; If left empty, the default value - 1521- will be used.	1521
service_name	Name of database service to connect to.	my_service_name
(optional) instance_name	Optional Name of specific database instance for this connection.	instance_id
(optional) params	Optional JDBC URL parameters that can be used for advanced configuration.	key1=value1;key2=value2

Creating Connections

CLI

First, create a file on your local computer containing the connection credentials. This file should also include type, name , config, and credentials. The config and credentials fields should contain fields that are specific to the connection being created.

Below is an example Oracle Database connection:

{
    "type": "oracle",
    "name": "my-oracle-connection",
    "config": {
        "username": "john",
        "host": "myserver.example.com",
        "port": 1521,
        "service_name": "mydatabase"
    },
    "credentials": {
        "password": "..."
    }
}

Now that you've created the credentials file, use the CLI to create the connection

gretel connections create --project [project id] --from-file [credential_file.json]

Console

• Navigate to the Connections page using the menu item in the left sidebar.

• Click the New Connection button.

• Step 1, choose the Type for the Connection - Oracle Database.

• Step 2, choose the Project for your Connection.

• Step 3, fill in the credentials and select Add Connection.

Step 1, choose the Type of connection

Step 3, fill in credentials

SDK

from gretel_client import create_or_get_unique_project
from gretel_client.config import get_session_config
from gretel_client.rest_v1.api.connections_api import ConnectionsApi
from gretel_client.rest_v1.models import (
    CreateConnectionRequest,
    UpdateConnectionRequest,
)

session = get_session_config()
connection_api = session.get_v1_api(ConnectionsApi)

project = create_or_get_unique_project(name="oracle-workflow")

connection = connection_api.create_connection(
    CreateConnectionRequest(
        name="my-oracle-connection",
        project_id=project.project_guid,
        type="oracle",
        config={
            "username": "john",
            "host": "myserver.example.com",
            "service_name": "my_database",
            #"port": 1521,
            #"instance_name": "myinstance",
            #"params": "key1=value1;key2=value2",
        },
        # note: best practice is to read in credentials from a file
        # or secret instead of directly embedding sensitive values
        # in python code.
        credentials={
            "password": "...",
        },
    )
)

Permissions

In Oracle, the CREATE SCHEMA command does not create a new, standalone schema. Instead, one creates a user. When the user is created, a schema is also automatically created for that user. When the user logs in, that schema is used by default for the session. In order to prevent name clashes or data accidents, we encourage you to create separate Oracle users for the Source and Destination connections.

Source Connection Permissions

The Oracle source action requires enough access to read from tables and access schema metadata. The following SQL script will create an Oracle user suitable for a Gretel Oracle source.

-- Create the user
CREATE USER user
IDENTIFIED BY password  -- change to something more secure
DEFAULT TABLESPACE SYSTEM;

-- Required to log in
GRANT CREATE SESSION ON user;

Destination Connection Permissions

The following SQL script will create an Oracle user suitable for a Gretel Oracle destination. It will write to its own schema.

-- Create the user
CREATE USER user
IDENTIFIED BY password  -- change to something more secure
DEFAULT TABLESPACE SYSTEM  -- change if you have a separate tablespace
QUOTA UNLIMITED ON SYSTEM;  -- change to limit amount of space allocated

-- Required to log in
GRANT CREATE SESSION ON user;

-- Required for writes
GRANT CREATE TABLE ON user;

For more details please check your installation's version and see Oracle documents on CREATE USER.