OIC - There are large number of scheduled orchestrated integrations need to run at the same time. What we can do to avoid backlogged or queued integrations ?

When there are too many scheduled integrations configured, instances can get backlogged waiting for resources to become available or previous integration runs to complete. This can cause processing delays where some instances are in a waiting state longer than they should be and schedules may not start at the scheduled time.

If we absolutely require a large number of scheduled integrations,  recommend the following design changes as a solution:

1. For each scheduled integration, convert it to an application-driven orchestrated, REST Adapter trigger-based integration. 
2. Create a new scheduled integration that only performs an asynchronous invoke of the application-driven orchestrated integration you converted in step 1 above.

Benefits of the design:

This solution enables the scheduled integration to start at the scheduled time, invoke the REST Adapter trigger-based child integration asynchronously, and complete within milliseconds. This approach reduces the backlog and contention surrounding scheduler resources.

If you have a large number of scheduled integrations to convert, a staged approach starting with the following integrations is recommended.

1. Longest-running scheduled integrations.
2. Scheduled integrations configured with the shortest frequency (for example, integrations that run every 10 minutes or less).

In the above screenshot,  I have created one schedule integration which is calling app driven integration where i have intentionally put 5 mins of wait. When I run the schedule one, it completes in mili second , where as app driven is still running.

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/integrations-user/common-integration-style-pitfalls-and-design-best-practices.html

OIC - ATP - Polling data

Usecase: Here, we will see how to poll data using ATP database.

Note:

Can we poll data as trigger using ATP DB:

• For direct connections (configured without using the connectivity agent), the Oracle Autonomous Transaction Processing Adapter can only be used for making outbound invocations as an invoke connection. This type of Oracle Autonomous Transaction Processing Adapter-specific connection cannot be configured as a trigger connection in an integration. Use cases related to inbound polling must be implemented using the scheduled orchestration integration pattern.
• If we create or configure ATP connection using Connectivity agent, then only we can poll data as trigger  from ATP Database.

Detailed screenshots:

Step1: create ATP connection using  connectivity agent

Step2: take an appdriven integration and configure the ATP poll for new or changed records as logical delete on STATUS field.

Put a logger

Insert some data in the db table

From Monittoring track instances

After polled:

Note : If we create direct atp connection without connectivity agent, then during trigger configuration, it will not allow you for the polling setups cause the feature is only supported with connectivity agent.

OIC - About ATP Integration | Can we poll data from ATP as Trigger | ATP adapter Capabilities

About ATP:

• Oracle Autonomous Transaction Processing delivers a self-driving, self-securing, self-repairing database service that can instantly scale to meet demands of mission critical transaction processing and mixed workload applications. 
• The Oracle Autonomous Transaction Processing Adapter enables you to integrate the Oracle Autonomous Transaction Processing database with Oracle Integration through use of a wallet for direct connectivity. 
• The Oracle Autonomous Transaction Processing Adapter is one of many predefined adapters included with Oracle Integration. You can configure the Oracle Autonomous Transaction Processing Adapter as a connection in an integration in Oracle Integration.

ATP provides the following capabilities:

1. Support for using a wallet for direct connectivity to connect to the Oracle Autonomous Transaction Processing - Shared (ATP-S) database in place of using the on-premises connectivity agent.
2. Support for accessing an Oracle Autonomous Transaction Processing – Dedicated (ATP-D) database with a wallet-based connection that uses the connectivity agent.
3. Support for invocation of stored procedure, run SQL statements, support for DML operation on table like Insert, update, merge, select etc.
4. Support for the bulk data import operation. This feature is supported for the following scenarios:
1. When using direct connectivity (that is, without use of the connectivity agent)
2. When using the Oracle Autonomous Transaction Processing - Shared (ATP-S) database
3. With the comma-separated value (CSV) data format only.
5. Mm

How to create ATP connection:

To create a connection to Oracle ATP CS instance, you must select Oracle ATP adapter and then specify the connection properties given below.

         1. Service Name. <<For example, myatp_tp. You can get the service name from the wallet or administrator can provide you>>

And specify the security properties given below:

          1. Wallet : <<Download the wallet from ATP and upload it here>> Please look at the steps given below to download the wallet from ATP.

          2. Wallet Password: <<Provide wallet password>>

          3. Database Service Username : <<Provide database service username>>

          4. Database Service Password: <<Provide database service password>>

Can we poll data as trigger using ATP DB:

• For direct connections (configured without using the connectivity agent), the Oracle Autonomous Transaction Processing Adapter can only be used for making outbound invocations as an invoke connection. This type of Oracle Autonomous Transaction Processing Adapter-specific connection cannot be configured as a trigger connection in an integration. Use cases related to inbound polling must be implemented using the scheduled orchestration integration pattern.
• If we create or configure ATP connection using Connectivity agent, then only we can poll data as trigger  from ATP Database.

Follow my below blog for atp polling usecase:

https://soalicious.blogspot.com/2024/06/oic-atp-polling-data.html

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/atp-adapter/oracle-autonomous-transaction-processing-adapter-capabilities.html

OIC - Invoke OTBI report in Oracle Integration and save the report in a ftp location.

Usecase: Here, we will invoke the OTBI report from OIC integration and save the xml report to a ftp location.

OTBI webservice:

https://<fusion_instance/analytics-ws/saw.dll/wsdl/v12?wsdl

Steps to follow:

1. First create a SOAP invoke connection using above wsdl with security user name and password.
2. Take a scheduled or app driven integration and create schedule params for Logon user, password etc.
3. Drag and drop created soap connection and configure for SAWSessionService >> logon operation >> provide logon user name and password >> it will give session id as output.
4. Drag and drop created soap connection and choose XMLViewService >> choose executeXMLQuery operation >> provide following information as input:
1. Session id , which we received from previous step.
2. Variable name value pair if any parameter to pass
3. Report path : which we will get form the report >> more >> properties >> Location and name. For example if location:  /shared/Custom and name: XXOTBIMultiParam then report path will be : shared/Custom/XXOTBIMultiParam
4. Execute options 
1. Async : true
2. Maxrowsperpage: 10
3. Refresh: false
4. PresentationInfo: false
5. Type: 1
5. We get the ouput in CDATA xml format as response
6. Take a stage and opaque schema to  write the xml string as a xml file. In mapper, map the encodeBase64 data of rowset
7. Take another stage and otbi response schema to read the xml file from the stage write filereference.
8. Configure ftp adapter and write the file using a target schema or csv file. 
9. Map the read xml contents to target xml file.

Note: we can also mention outputFormat as. SAWRowsetSchemaAndData if it is giving output format is missing error.

Detailed screnshots:

HCM/ERP - Invoke OTBI report webservice from SOAP UI Tool

Usecase: 

Here, we will see how to invoke an OTBI report webservice using SOAP UI Tool.

OTBI webservice:

https://<fusion_instance/analytics-ws/saw.dll/wsdl/v12?wsdl

Steps to follow:

1. Open soap ui tool and create a soap project using the above otbi wsdl.
2. Open SAWSessionService >> logon operation >> provide logon user name and password >> it will give session id as output.
3. Open XMLViewService >> choose executeXMLQuery operation >> provide following information as input:
1. Session id , which we received from previous step.
2. Variable name value pair if any parameter to pass
3. Report path : which we will get form the report >> more >> properties >> Location and name. For example if location:  /shared/Custom and name: XXOTBIMultiParam then report path will be : shared/Custom/XXOTBIMultiParam
4. We get the ouput in CDATA xml format

Detailed screenshots:

Reference:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=4569601291952&parent=EXTERNAL_SEARCH&sourceId=HOWTO&id=2705306.1&_afrWindowMode=0&_adf.ctrl-state=9n4a27dyb_4

OIC OTBI report links

OTBI links:

1. ERP / HCM - BIP VS OTBI
   
2. ERP/HCM - Create OTBI report with parameter
   
3. HCM / ERP - Invoke OTBI webservice using SOAP UI tool
   
4. OIC - Invoke OTBI report in Oracle Integration and save the report in a ftp location.
   
5. OTBI - How to use OTBI analysis in BI publisher
   
6. OTBI - How to create OTBI dashboard report