Oracle Autonomous Transaction Processing Adapter in OIC

The Oracle Autonomous Transaction Processing Adapter enables you to integrate the Oracle Autonomous Transaction Processing database with Oracle Integration through use of direct connectivity. Use the Oracle Autonomous Transaction Processing Adapter to execute SQL queries or stored procedures in the Oracle database.

In this article, we are going to explain capabilities of ATP adapter and how to connect to Autonomous Transaction Process(ATP) in Oracle Integration Cloud(OIC) using OOTB Oracle ATP adapter.

In my earlier article, I have explained how to provision the Autonomous Transaction Process(ATP) instance. Kindly see the article, before you go ahead in this article.

Short video on ATP Adapter

Capabilities of Oracle Autonomous Transaction Processing Adapter

• Support for using direct connectivity to connect to the Oracle Autonomous Transaction Processing database in place of using the on-premises connectivity agent.
• Support for invocation of stored procedures in the Oracle database
• Support for non-JDBC (PL/SQL record and PL/SQL table) datatypes in outbound invocations of stored procedures
• Support for execution of DML statements and SQL queries: Select, Insert, Update, and Delete.
• Support for updating or inserting multiple records in a single request.
• Support for the operations on a table feature, which enables you to model SQL statements with the Adapter Endpoint Configuration Wizard. The operation on a table feature also supports multiple records in a single request.

Oracle Autonomous Transaction Processing Adapter Restrictions

• The PL/SQL boolean type is not supported as an IN/OUT parameter in a stored procedure. However, you can create a wrapper stored procedure that converts PL/SQL boolean to an integer and use those wrapper stored procedures in Oracle Integration
• Nested PL/SQL types ( for example, RECORD types inside a TABLE type) are not supported as IN/OUT parameters in a stored procedure. However, you can define OBJECT types inside the TABLE type
• Cross schema stored procedures are not allowed in cases where Oracle Integration must generate the wrappers
• Inbound (trigger) polling is not supported if using direct connectivity (that is, without using the connectivity agent in the connection)

Pre-requisite to Connect to ATP in OIC

• Client credentials wallet
• The target database should be accessible
• Ensure that you have write permissions to the database
• Ensure that you have the required permissions to run stored procedures and SQL statements
• The database service name
• The database service username and password for connecting to the database

Download the client credentials wallet from the Oracle Autonomous Transaction Processing instance

• Login in Oracle Cloud
• Click on the Hamburger menu from upper left and click on the Autonomous Transaction Processing
• Click on the Instance of which you want to download client credentials wallet
• Click on DB Connection

• On the opened popup, click on “Instance Wallet” from WALLET TYPE drop down and click on the Download Wallet button

• Once clicked on Download Wallet button, another popup will be opened which allow you to enter Wallet password(Wallet@123) and click on Download button

• A file with name Wallet_<Database name>.zip will be downloaded which will be used to create ATP connection in OIC.

The database service name

The database service name can be found in tnsnames.ora which is under Wallet_<Database name>.zip file. There are 5 service name and you can use any on them during configuring ATP connection in OIC. To connect to ATP in OIC we’ll use “atpdb_high“

See Oracle official document to know the purpose of each service name.

The database service username and password

The database service username and password will be the credentials which was given during ATP instance provisioning. The username was “Admin” and password “Welcome@0987“

Create Oracle ATP connection in OIC

• Login into OIC and move to the Connections
• Click on the Create button from Connections page and select Oracle ATP

• Enter the connection name, identifier and click on Create button
• Click on the Configure Connectivity button and enter Service Name as ” atpdb_high”

• Click on Configure security button, enter below details and click on Ok button
  • Wallet: Upload the credentials wallet zip file( Wallet_<Database name>.zip)
  • Wallet Password: Enter Waller password(Wallet@123)
  • Confirm Wallet Password: Confirm password(Wallet@123)
  • Database Service Username: Enter service username(Admin)
  • Database Service Password: Enter service password (Welcome@0987)
  • Confirm Database Service Password: Confirm service password(Welcome@0987)

Click on the Test button and connection should be tested successfully.

Now connection is ready to use ATP objects(Tables, Stored procedure etc.)