Oracle

Oracle Database is a multi-model database management system designed to handle large-scale data workloads and complex enterprise applications. It provides features like data storage, querying, and transaction processing while supporting high availability, scalability, and integration with modern cloud environments.

Prerequisites

Authorize the Connection to Oracle DB

In Oracle DB

Please keep in mind that this guide provides general instructions, and your individual setup might need a different approach or some extra steps. Always take your organization's security guidelines into account when you're making these changes. Additionally, it's a good idea to seek assistance from a database administrator or an IT professional to make sure everything is set up correctly.

Create User

1. Connect to your database. You will need admin-level permissions to create a user and assign permissions.
2. Use the following statement, replacing [password] with the actual password:

   CREATE USER dataddo
       IDENTIFIED BY [password] 
   

For more information, refer to Oracle's official documentation.

Set Permissions

To allow the newly created user access to the Oracle database, execute the following statements:

GRANT CREATE SESSION TO dataddo;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON <schema>.<table_name> TO dataddo;

• For access to all tables in the database, use:

  GRANT INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO dataddo;
  

For read-only access (for Oracle only as a source), use:

GRANT SELECT ON <schema_name>.<table_name> TO dataddo

• To grant read-only access to all tables in the database, use:

  GRANT SELECT ANY TABLE TO dataddo;
  

Allow Network Connection

If you're using Oracle Cloud, go to your database. Under the Network section, find the Access control list and click edit. Here, you can add all IP addresses that need to be whitelisted.

You can also configure the network access at the firewall level. Here are the general steps:

1. Identify the firewall that's protecting your service. This could be a Windows or Linux firewall running on the server itself, or it could be a network firewall.
2. Open the firewall's configuration settings. This process will vary depending on the type of your firewall.
3. Look for the setting that allows you to define inbound rules or policies.
4. Create a new rule or policy that allows inbound traffic on the port that the service is using (the default is 1433) from the Dataddo IP addresses.
5. Save your changes and test the connection from Dataddo to make sure everything is working as intended.

In Dataddo

1. On the Authorizers page, click on Authorize New Service and select Oracle.
2. You will be asked to fill the following fields
   1. Server IP or Hostname: Public IP or Hostname of your Oracle server.
   2. Database: Name of the database you will use for writing or reading the data.
   3. Username: Username of your Oracle user.
   4. Password: Password associated with the Oracle username.
   5. Port: Dataddo uses native connection with default port 1521.
3. [Optional] Configure the connection via SSH tunnel. To do so, please follow these steps.
4. Click on Save.

Create a New Oracle DB Destination

Create a Flow to Oracle DB