Oracle Integration Cloud comes with a library of adapters for both Oracle and 3rd Party applications. All of these capabilities mean that it’s possible to integrate applications faster and increase business agility. I’ve been writing a lot lately on the topic of Oracle Autonomous Database and also presenting about Oracle Integration Cloud, so I thought, why not merge the 2 subjects and document here how you might go about using the Oracle Integration out-of-the-box adapters to integrate to an Oracle Autonomous Transaction Processing (ATP) or Autonomous Data Warehouse (ADW) instance (both Oracle Autonomous Database – ADB – offerings).
In this post I will describe how to use the Oracle Autonomous Database adapter in Oracle Integration Cloud to directly connect to an ATP or ADW instance. It will not cover how to connect when a connectivity agent is needed (coming soon!).
Note 1 – The details below assume that you already have a provisioned & active Oracle Autonomous Database service instance. If you do not, you may wish to follow the steps in my posts: Creating an ATP Database in Oracle Cloud & Connecting to an ATP Database in Oracle Cloud. It also assumes that you have an OIC instance.
Note 2 – Given the regular release schedule present in Oracle Cloud, it is possible that the screens may change somewhat beyond the writing of this post. If this happens, please comment on this post and I will try to help you out. (and update this post!)
Connectivity Agent – Yay or Nay?
Before I get started in describing how to leverage the OIC OOTB adapter for ATP and ADW instances, it’s important to understand first if you are going to need a connectivity agent. A distinction of “Connectivity Agent YAY or NAY” is:
- If you are connecting to a dedicated instance of Oracle Autonomous Database (ADB-D) then you do need a connectivity agent
- If you are connecting to a shared infrastructure instance of Oracle Autonomous Database (ADB-S) then you do not need a connectivity agent………most of the time!
Here is a very quick reference table:
| Instance Type | Direct Connectivity? | Connectivity Agent? |
|---|---|---|
| ADB-S: Oracle Autonomous Database (ADB) – Shared Infrastructure |  | |
| ADB-S: Oracle Autonomous Database (ADB) – Shared Infrastructure with private endpoint |  | |
| ADB-D: Oracle Autonomous Database (ADB) – Dedicated Infrastructure | | |
You will see from the table above that “most of the time!” refers to the fact that if an ADB-S instances in behind a private endpoint, then the same rules apply as if you are using an ADB-D instance – direct connectivity isn’t available and instead, the use of a connectivity agent is required.
For ADB-S (public endpoint) Oracle integration connects using JDBC over SSL directly to the public endpoint.
Configure an OIC ADB Adapter (without agent)
Step 1: Log into Oracle Integration Cloud
Step 2: Click on the menu icon (often referred to as “Hamburger” or “Pancake Stack” icon)
Step 3: Select “Integrations” from the navigation menu
Step 4: Select “Connections” from the navigation menu. The connections screen will be displayed. Note that by default, the view will be filtered by connections created/updated by you (this filter can be removed)
Step 5: Click “Create” button to begin creating a new connection
Step 6: Given we are wanted to configure an adapter to Oracle Autonomous Database, here we need to choose either “Oracle ADW” or “Oracle ATP”. As my instance I will use for this is an ATP instance, I chose Oracle ATP and clicked “Select”.
Step 7: Add descriptive information to the adapter and click “Create”
Name – user defined, stick to an agreed naming convention
Identifier – this is an internal identifier. it will auto generate based on the connection name but as a user, you can alter that. Note that only capital letters, numbers and “_” or “-” can be used.
Role – the connections can be limited to “Trigger” only, “Invoke” only or both “Trigger and Invoke”. Selectable by a drop down list, any restrictions placed here at connector level will apply to any integration that uses it.
Description – a short description of the connection is useful for developers but not mandatory
Step 8: Once you have clicked create, you will be navigated to add connection details. For the purposes of this post, the below screenshots and details show the quickest connection route. For this, I only need to provide security details. In this case, as I will connect directly using “JDBC Over SSL” which will require database wallet for connectivity. Before moving to Step 9, download your credentials wallet from the Oracle Cloud console. If you would like to follow steps on how to do this, follow steps 2 to step 4 in my Connecting to an ATP Database in Oracle Cloud post.
Step 9: configure security details as per the below
- Security Policy – this will be set as JDBC Over SSL as default
- Wallet – upload the credentials wallet downloaded in Step 8
- Wallet Password – this is the password you gave during the credential wallet download
- Database Service Username – this is your schema/user username
- Database Service Password – this is your schema/user password
Step 10: You will also need to add a service name to connect to, this is available in the same screen where you downloaded your credentials wallet in step 8.
Step 11: Click “Test” to test your connectivity. If you have entered all details correctly, you will see a success message
Note also that the connection will show as 100% configured once you have performed this test action
Step 12: Click “save “Save” to save your connection configuration. You can now navigate back to the connection screen and your adapter is ready for use.
Use an OIC ADB Adapter (without agent)
If you have successfully followed the steps documented above, you will now be able to go ahead and use the adapter in an integration. For completeness, I have documented below a very basic usage of this adapter. I will simply create a scheduled integration (for ease of illustration) and within this integration I will invoke the ATP adapter to load a data row into a database table. Note here, the importance of the “Trigger/Invoke” choice when setting up the connection. Without declaring that the connection can be used in an Invoke scenario, this would not be possible.
Step 1: Log into Oracle Integration Cloud
Step 2: Click on the menu icon (often referred to as “Hamburger” or “Pancake Stack” icon)
Step 3: Select “Integrations” from the navigation menu
Step 4: Select “Integrations” again. This will navigate you to the integrations page. Note that by default, the view will be filtered by integrations created/updated by you (this filter can be removed).
Step 5: Click Create and select integration style. As mentioned I have selected the “Scheduled Orchestration” type. Once selected click “Select”.
Step 6: Add descriptive information to the adapter and click “Create”
Name – user defined, stick to an agreed naming convention
Identifier – this is an internal identifier. it will auto generate based on the connection name but as a user, you can alter that. Note that only capital letters, numbers and “_” or “-” can be used.
Package – associate this integration with a package (or collection) of integrations.
Description – a short description of the connection is useful for developers but not mandatory
Step 7: The integration designer will be displayed. Click the “Invokes” icon which is the first icon in the far right of the screen which looks like a target.
Step 8: Find and select your connection which was configured earlier and drag it onto the “+” icon in the integration flow.
Step 9: Enter basic information to the endpoint configuration wizard and click next
- Name – be descriptive but follow some convention
- Description – not mandatory but useful for developers
- Operations – here you can select to invoke a store procedure, run a SQL statement or perform an operation on a table. I have chosen to perform a sql statement
Step 10: Enter the required SQL query note that to pass data to a sql query, we need to use “#”. Click “Validate SQL Query” and then “Next”
Step 11: Endpoint configuration is complete, click “Done”
Step 12: The integration designer should now look like this. The outstanding activity is to map data to the SQL query. You could then add other elements or logic to your integration.
AMY SIMPSON-GRANGE - BLOG 