Skip to main content

Oracle Database

The Conduit Platform by default supports Oracle Database as a source and a destination.

The Oracle Database destination can connect to and produce records to a table.

Required Configurations​

NameDescriptionRequiredDefault
urlConnection string for the Oracle Database. Example: oracle://admin:[email protected]:1521/orclpdb1Yes
tableThe table the destination connector should write to, by default. Example: ordersYes
keyColumnColumn name used to detect if the target table already contains the record.Yes

Upsert Behavior​

The destination connector takes an sdk.Record and parses it into a valid SQL query. It is designed to handle different payloads and keys. Because of this, each record is individually parsed and upserted.

If the target table already contains a record with the same key, the Destination will still upsert the new record value. Since keys must be unique, this can lead to overwriting and potential data loss, so the keys must be correctly assigned from the Source.

Table name​

If a record contains an oracle.table property in its metadata, it will be inserted in that table, otherwise, it will fall back to use the table configured in the connector. Thus, a Destination can support multiple tables in a single connector, as long as the user has proper access to those tables.

User privileges​

The Oracle user you provide to the Conduit Platform must have privileges to CREATE TABLE. To do this, run the following queries against your Oracle Database instance using Oracle SQL developer or SQL*Plus.

  1. Create a unique user for the platform. You may replace conduit_user with whatever you prefer.
SQL> CREATE USER conduit_user IDENTIFIED BY very-secure-password;
  1. Grant the CREATE TABLE privilege to the user for the table. Replace my_table with your respective table name.
SQL> GRANT CREATE TABLE TO conduit_user ON my_table;

If you do not have the privileges to perform above actions, please contact your Oracle administrator.

Known Limitations​

  • Type convention: Describes the conversion between Oracle to Go types. Oracle does not support a boolean type, so the best pratice is to keep the values as 0 or 1. Oracle: NUMBER(1,0) Go: bool
  • Updating the configuration can cause completely unexpected results.

Amazon RDS Setup​

Requirements​

  1. Configure Security Groups
  2. Configure Network ACLs (only if your Oracle Database is within a VPC)
  3. Enable Oracle Database User Priviledges

Configure Security Groups​

To allow access to your Oracle Database from Conduit Platform IPs, follow these steps:

  1. Go to Amazon RDS in your AWS account.
  2. Go to Databases in the Amazon RDS menu.
  3. Click into the Oracle Database instance you'd like to use.
  4. In the Connectivity & security section, under Security and VPC security groups, click your security group by clicking the blue hyperlink.
  5. You should now see a list of Security Groups, click into the selected security group by clicking the blue hyperlink under Security group ID.
  6. Under Inbound rules, click the Edit inbound rules button.
  7. Click the Add rule button.
  8. Set Protocol to TCP.
  9. Set Port to the port of your database. For Oracle Database it's 1521 by default.
  10. Set the Source to include the Conduit Platform IPs.

Configure Network ACLs​

You only need to update this if your database is within a VPC.

To allow access to your Oracle Database from Conduit Platform IPs, follow these steps:

  1. Go to Amazon RDS in your AWS account.
  2. Go to Databases in the Amazon RDS menu.
  3. Click into the Oracle Database instance you'd like to use.
  4. In the Connectivity & security section, under Networking and VPC, click your VPC by clicking the blue hyperlink.
  5. You should now see a list of Your VPCs. Click into the selected VPC by clicking the blue hyperlink under VPC ID.
  6. In your VPC Details, click the blue hyperlink under Main network ACL.
  7. You should now see a list of Network ACLs. Click into the selected Network ACL by clicking the blue hyperlink under Network ACL ID.
  8. You will update both the Inbound and Outbound Rules of the Network ACL to contain ALL - 0.0.0.0/0 - ALLOW.
  9. If your Inbound and Outbound Rules do not contain ALL - 0.0.0.0/0 - ALLOW—update the Source to allow Conduit Platform IPs both Inbound and Outbound.

Oracle Database User Privileges​

When using Amazon RDS you can connect with your Oracle Database in the following two ways. In both cases, you will need an Oracle DB endpoint.

Oracle DB Endpoint in Amazon RDS​

Here are instructions on how to find your Amazon RDS Oracle Database endpoint and port.

  1. Go to Amazon RDS in your AWS account.
  2. Go to Databases in the Amazon RDS menu.
  3. Click into the Oracle Database instance you'd like to use.
  4. In the Connectivity & security section, under Endpoint & port, find the Endpoint and Port values for your Oracle Database instance.