Oracle Integration Cloud Autonomous Transaction Processing Adapter Configuration !!!

Oracle’s two major ground breaking innovation last year were Autonomous Data warehouse (ADW) and Autonomous Database Transaction processing (ATP) both are database offering suitable for different workload and are self-driving, self-securing, and self-repairing in nature. If you want to read more about these services then please go through above links.

ADW/ATP both can be quickly provisioned on Oracle Cloud Infrastructure, it’s take less than 5 minute to spin ADW/ATP instance and database is ready to connect.

User can use Oracle SQL Developer to connect to ADW/ATP database as long as they are supported version. These DBaaS services also offers out-of-box browser based SQL Developer tool which can be used to run any kind of SQL statements.

Here is sample snap of browser based SQL Developer capabilities –

Once user has Database ready, obviously there could be requirement to access data residing inside ADW/ATP instances.

Fortunately, Oracle Integration Cloud provide Adapter for connecting ADW/ATP instance, click here to know more about ATP Adapter capabilities –

In this blog I will be covering simple steps how you can connect to ADW/ATP instances using OIC Autonomous Transaction Processing Adapter (ATP) Adapter.

I made assumption that ADW/ATP instance already exists. if you not sure how to create ADW/ATP instance then refer this blog which was written by my colleague who already explained how to create ADW/ATP database instance and connect from SQL developer.

So, let move forward. Login to your Oracle Integration Cloud (OIC) home page >> Integration >> Connection >> Create >> search for “Oracle ATP” >> select the same

Provide a meaningful name to your ATP Adapter connection, select role “Invoke” and click create.

Please Note: Currently only Invoke Role is supported, which means OIC can only invoke ADW/ATP Database. OIC integration won’t be able to trigger itself when there is any changes in ADW/ATP database.

Now, In order to connect ADW/ATP database instance you need following values –

Wallet File:

Read the document about Wallet file here.  This is one of the most important zip files containing private keys, keystore files, tnsname.ora files and many other useful configuration files. Keep it safe and don’t share with anyone until unless you trust him and want to provide access to your ADW/ATP instance.

Oracle client credentials (wallet files) are downloaded from Autonomous Transaction Processing by a service administrator. If you are not an Autonomous Transaction Processing administrator, your administrator should provide you with the client credentials.

To download client credentials, do the following from Oracle Cloud Infrastructure console:

Navigate to the Autonomous Transaction Processing details page.

Wallet.zip can be downloaded from two different location 1) DB Connection and 2) Service Console >> Administration >> Download Client Credentails (Wallet)

Note: We have seen some connectivity problem using the downloaded files from DB Connection section, which we have already highlighted to Oracle support and they must be fixing while I am writing this blog, So I would recommend to download wallet file from “Service Console>> Administration >> Download Client Credentials (Wallet)”

In the Download Wallet dialog, enter a wallet password in the Password field and confirm the password in the Confirm Password field.

The password must be at least 8 characters long and must include at least 1 letter and either 1 numeric character or 1 special character.

Note:This password protects the downloaded Client Credentials wallet. This wallet is not the same as the Transparent Data Encryption (TDE) wallet for the database; therefore, use a different password to protect the Client Credentials wallet.

Click Download to save the client security credentials zip file.

By default the filename is: Wallet_.zip. You can save this file as any filename you want.

You must protect this file to prevent unauthorized database access.

Service Name:

The tnsnames.ora file resides inside Wallet_.zip provided with the credentials zip file contains five database service names identifiable as tpurgent, tp, high, medium, and low. The predefined service names provide different levels of performance and concurrency for Autonomous Transaction Processing. To know more about these predefined service name, refer this document.

Wallet Password:

Provide the password which you have provided while downloading Wallet_.zip file.

Database Service username:

Its schema name or DB username.

Database Service Password:

Password for Schema name or DB username.

Final Config something look like this –

So, your ATP Adapter connection is ready to be used inside Integration.

Just create any type of OIC Integration flow and use this connection inside flow.

Once you drop the ATP connection instance in your OIC flow, its exposes 3 different kinds of operations-

• Invoke Stored Procedure
• Run a SQL Statement
• Perform an operation on Table

Once you select particular operation, it changes the subsequent wizard screens .In below snap I have selected “Perform an Operation on a table” option, hence its showing me Insert, Update, Merge,  Select sub-operation.

To understand more about of each of these operations, please refer this document section.

That’s easy is to connect to an ATP or ADW Database instance using Oracle Integration Cloud Oracle Autonomous Transaction Processing Adapter.

Stay tuned for more useful blog!!!

Happy Blogging