OIC Invoke BI Publisher report with Name value parameters

Please read the following my privious blog for understanding how we can create BI Report and call from OIC:

https://soalicious.blogspot.com/2022/03/oic-how-to-call-and-read-bi-publisher.html

Screenshots steps for Name value params:

***Expand the "parameterNameValues" element

Expand "Item" and then enter your BI Report Parameter name in name* element 

Now expand values* and map or pass Parameter Values in Item element .

Parameter names and its values configuration completed

*Note : suppose you have multiple parameters for your BI Report then you have to repeat the Item tag and then pass the Parameter name and its Values.***

BI Report with a parameter

Call Maps from Integration:

parameterNameValue

reportAbsolutePath

sizeOfDataChunkDoenload = "-1"

Name

Values ->> Item

Parameter name in the BI report data model:

OIC | How to call and read BI publisher report in OIC Integration

High level steps:

1. Create a SOAP connection for the report service.
2. Create a schedule integration and drag and drop the created soap connection and select operation as runReport.
3. Map the required fields like reportAbsolutePath, NameValuesParameters etc.
4. DecodeBase64 ERP response to readable format. 

Read the follow blog to know how to create BI publisher report in Cloud ERP:

https://soalicious.blogspot.com/2022/03/cloud-erp-how-to-create-otbi-report.html

Steps in detail:

Create a SOAP connection for the BI report call.

Here we will configure  " ExternalReprtWSSService WSDL URL " which can be used to run a report. The same connection we will use in below steps while developing integration.

1. Navigate to connection window and select SOAP adapter, give any meaningful name for connection.
2. Enter the ExternalReportWSSService WSDL URL  and Select the TLS Version. TLS Version depends on your ERP Cloud Application Instance. Mostly it is TLSv1.1
3. WSDL URL Format : https://<your oracle cloud application instance domain>/xmlpserver/services/ExternalReportWSSService?WSDL
4. Choose Security policy as Username Password token and Enter the *Username and *Password of the Oracle Cloud Application Instance. Click SAVE and TEST the Connection.

Create a scheduled integration.

Drag and drop the created soap connection and probide endpoint name

Choose operation as runReport.

Keep it as default.

Edit the Map

Now let's do the MAPPINGS. We will pass all the required fields to invoke BI report ( like absolute Report Path, size of data chunk download, Report Parameters Name and its Value ,attribute template etc.)

In this case, I have used the following parameters:

ReportAbsolutePath: "/Custom/Integrations/Poc/InvoiceId_POC_REP.xdo"

sizeOfDadaChunkDownload: " -1"

You can also use the following paramters as per your requirement:

Expand the "parameterNameValues" element

Expand "Item" and then enter your BI Report Parameter name in name* element 

Now expand values* and map or pass Parameter Values in Item element .

Parameter names and its values configuration completed

*Note : suppose you have multiple parameters for your BI Report then you have to repeat the Item tag and then pass the Parameter name and its Values.

Take an assign action and decode the base64 reaponse to reference using decodeBase64ToReference() function.

Now take a stage activity and use that referenced variable and read it using a sample csv file.

Add tracking , save, activate and submit run.

From Monitoring

OIC - How to read file size up to 1 GB in oracle integratio

By default, we can only read 10 MB file. To read a file size upto 1GB file we have to follow the below steps:

Steps:

1. List the files using FTP connection
2. Loop the list of files using For Each action
3. Download the each file into a stage (VFS) location.
4. Read file records in chunk using read in segments. Each record contains 200 records.
5. Read the file using a sample csv file format.

Screenshots:

List files using FTP connection

Download the file using FTP connection

Take a For each and loop each file

Map each file from list response to download request

Read file in segments

OIC | Embedded file server in Oracle integration | using file server in oracle integration generation 2

File Server provides an embedded SFTP server within Oracle Integration, enabling organizations to focus on building integrations without needing to host and maintain a separate SFTP server.

Three Reasons to Use File Server:

File Server is an SFTP server that is bundled with Oracle Integration. With File Server, you get 500 GB of storage for free, allowing you to store, share, and receive files, including files for SaaS integrations and third-party transfers.

1. Eliminate the cost and operational expenses associated with hosting and maintaining an SFTP server: Managing a do-it-yourself file-based storage system is complex and daunting. From setting up hardware to maintaining the server, implementing a homegrown solution costs both time and money. With File Server, you get free file storage. Moreover, Oracle manages the operational tasks for you, so you can focus on solving more pressing business problems.
2. Create file-based integrations easily: If your organization needs an integration solution that supports file-based integrations, FileServer is for you. And because File Server is embedded in Oracle Integration, you can start building file-based integrations quickly.
3. Manage permissions in one place, using in an intuitive interface: Configuring and managing multiple users, groups, and folders in an SFTP environment can be challenging, but this work is simplified with File Server. File Server has a powerful administrative console that contains the core set of features that you expect from an SFTPserver, all in an intuitive user interface that streamlines your workflows.

To begin using File Server in Oracle Integration, it must first be enabled for the Oracle Integration instance in the Oracle Cloud Infrastructure Console. Enabling File Server is a one time action.

Common Use Cases

File Server can be used in a variety of scenarios. Here are some common use cases

1. Communication with trading partners Communication with trading partners such as customers and suppliers. In these cases, File Server enables trading partners to send information such as purchase orders, invoices, and shipping information using SFTP.
2. Integration with SaaS applications SaaS (or on-premises) applications often export bulk data to files on an SFTP server such as File Server. For example, Oracle E-Business Suite generates a zip file with external transactions, which need to be bulk uploaded to ERP. Oracle Integration can pick up the files, process them, and send them to a target system.
3. SFTP server lift-and-shift If your organization is running an on-premise SFTP server with Oracle Integration using the SFTP adapter, you may want to move this SFTP server to the cloud. Move the SFTP files into the Oracle Integration File Server, and redirect the SFTP adapter.

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/file-server/using-file-server-oracle-integration-generation-2.pdf

OIC | Oracle Cloud Integrarions | Restrictions using Oracle Integearion Generation 2

Restrictions

Note the following current restrictions when creating and using Oracle Integration.

• You can create Oracle Integration Generation 2 instances in any Oracle data region listed in Availability.

• You can create Oracle Integration for Oracle SaaS Generation 2 instances in any Oracle data region if you created a new Oracle Cloud account on or after February 11, 2020.

• Email notifications from Processes work correctly. However, it's not possible to set a custom “from” sender (that is, the from address is the default).

OIC | Oracle Integrations Editions | Standard vs Enterprise features

 Oracle Integration Editions

Oracle Integration is available in two editions: 

• Standard 
• Enterprise.

Either edition gives you the power to integrate your Software as a Service (SaaS) applications and your on‑premises applications. 

Enterprise edition enables you to also design, automate, and manage your business processes in the cloud.

Regardless of which edition you choose, Oracle handles cloud and database management, backup, restore, and other administrative tasks for you.

Here’s a side-by-side comparison of what’s licensed in each edition.

OIC Service limits | Generation 2 Resources limitation

This is very important to know the service limits. A service limit is the quota or a allowance set on a resource.

Follow are few of the service limits. 

1. Oracle Cloud Infrastructure Console Service Limits

Resource: Integration instance count

Service Limit: 200 instances per region

2. Adapters

A. Timeouts for all outbound adapter invocations. The following values are set and cannot be changed:

• READ timeout is set to 5 minutes.

• Connection timeout is set to 5 minutes.

B. Oracle Autonomous Data Warehouse Adapter, Oracle Autonomous Transaction Processing Adapter, Oracle Database Cloud Service Adapter, MySQL Adapter, Microsoft SQL Server Adapter, Oracle Database Adapter, and IBM DB2 Adapter:

Starting with the August 2021 release, all new integrations that include stored procedure or PureSQL database operations must finish within 240 seconds. Otherwise, the query times out.

C. Timeouts for all connectivity agent-based outbound adapter invocations:

Connection timeout is set to 4 minutes.

D. File Adapter - file size:

10 MB.

Note:
The size of CSV files increases when translated into a message. Therefore, the file size must be less than 10 MB, so that the after-translation message size does not exceed 10 MB.

E. FTP Adapter - file size

For invoke configurations

Read File operation:

• 1 GB when used without a schema (with a connectivity agent).

• 10 MB when using a schema for transformation.

Write File operation:

• 1 GB when used without a schema (with a connectivity agent).

• 10 MB when using a schema for transformation.

Download File operation: 1 GB.

F. AS2 Adapter - file size limit

10 MB

G. REST Adapter

For trigger configurations

• XML document size for schema generation: 3 MB. See REST Adapter Capabilities.

• Incoming message size (without attachment): 10 MB. Messages with attachments, for example, multipart/mixed and multipart/form-data, are not subject to this constraint.

• Incoming JSON attachments size: 1 GB.

• Incoming structured message payload size (any content-type header containing JSON, XML, HTML, YAML, or YML): 10 MB.

• Incoming content as raw bytes (application/octet-stream as content type): 1 GB.

• Specifying the response payload format: JSON sample files of up to 100 KB in size are supported.

For invoke configurations

• XML document size for data definition generation: 3 MB. See REST Adapter Capabilities.

• Attachment size in outbound requests: 1 GB. These attachments can be multipart/mixed, multipart/form-data, or application/octet-stream.

• Outgoing structured message payload size (any content-type header containing JSON, XML, HTML, YAML, or YML): 10 MB.

• Outgoing unstructured message payload size (multipart/form-data and binary/octet-stream): 1 GB.

• Specifying the request payload format: JSON sample files of up to 100 KB in size are supported.

H. REST-Based Adapters (Adapters that expose REST endpoints on the inbound or adapters invoking external REST endpoints. For example, Oracle Commerce Cloud Adapter, Oracle Field Service Adapter, etc.)

For trigger configurations (wherever applicable)

• XML document size for schema generation: 3 MB. See REST Adapter Capabilities.

• Incoming message size (without attachment): 10 MB. Messages with attachments, for example, multipart/mixed and multipart/form-data, are not subject to this constraint.

• Incoming JSON attachments size: 1 GB.

• Incoming structured message payload size (any content-type header containing JSON, XML, HTML, YAML, or YML): 10 MB.

• Incoming content as raw bytes (application/octet-stream as content type): 1 GB.

• Specifying the response payload format: JSON sample files of up to 100 KB in size are supported.

For invoke configurations (wherever applicable)

• XML document size for data definition generation: 3 MB. See REST Adapter Capabilities.

• Attachment size in outbound requests: 1 GB. These attachments can be multipart/mixed, multipart/form-data, or application/octet-stream.

• Outgoing structured message payload size (any content-type header containing JSON, XML, HTML, YAML, or YML): 10 MB.

• Outgoing unstructured message payload size (multipart/form-data and binary/octet-stream): 1 GB.

• Specifying the request payload format: JSON sample files of up to 100 KB in size are supported.

I. Salesforce Adapter - batch file size

8 MB (10,000 records).

J.SOAP Adapter

For trigger configurations

• Structured payload (XML) size in Request and Response: 10 MB.

For invoke configurations

• Structured payload (XML) size in Request and Response: 10 MB.

• MTOM attachment (binary and non-binary content) size in Request and Response: 1 GB.

K. SOAP-Based Adapters (Adapters that expose SOAP endpoints on the inbound or adapters invoking external SOAP endpoints. For example, Oracle Logistics Adapter)

For trigger configurations (wherever applicable)

• Structured payload (XML) size in Request and Response: 10 MB.

For invoke configurations (wherever applicable)

• Structured payload (XML) size in Request and Response: 10 MB.

• MTOM attachment (binary and non-binary content) size in Request and Response: 1 gb

L. Database Adapters (Oracle Database Adapter, IBM DB2 Adapter, Microsoft SQL Server Adapter, MySQL Adapter, Oracle Autonomous Data Warehouse Adapter, Oracle Autonomous Transaction Processing Adapter, and Oracle Database Cloud Service Adapter)

For trigger configurations

• Polling Operation: 10 MB with schema transformation.

For invoke configurations

• Stored Procedure/Operation on Table/Run PureSQL Statement Operations: 10 MB with schema transformation for all the outbound operations.

M. Apache Kafka Adapter

For invoke configurations

• Produce/Consume Message Operations: 10 MB with schema transformation for all the outbound operations.

N. JMS Adapters (Oracle WebLogic JMS Adapter and IBM MQ Series JMS Adapter)

For trigger configurations

• Consume Message Operation: 10 MB with schema transformation.

For invoke configurations

• Produce Message Operation: 10 MB with schema transformation.

O. Oracle CPQ Cloud Adapter - response payload

10 MB.

3. Integrations:

A. Connectivity agent - memory

A minimum of 8 GB memory with 4 GB of heap size dedicated to the on-premise agent's Java Virtual Machine (JVM). To include any other processes on the host besides the agent, increase the physical memory to a value greater than 8 GB.

B. Connectivity agent - message payload

10 MB, through the use of compression.

All connectivity-agent-enabled adapters

• 10 MB as request.

• 10 MB as response.

SOAP and REST adapters configured with connectivity agent

• 10 MB (structured XML/JSON document) as request.

• 10 MB (structured XML/JSON document) as response from private SOAP/REST endpoints.

• 1 GB for attachments as part of a request.

• 1 GB for attachments as part of a response from private SOAP/REST endpoints.

C. Stage File action (in orchestrated integrations) - file size

Read Entire File operation: 10 MB. For files greater than 10 MB, use the Read File in Segments operation.

Encrypt File operation: 1 GB.

Decrypt File operation: 1 GB.

D. Synchronous integration message timeout

300 seconds.

Synchronous integrations (integrations that return a response payload) return a timeout error if they run more than 300 seconds.

E. Oracle Integration Messaging - message size

10 MB.

F. Encode and Decode File Attachment Content (mapper)

The functions encodeReferenceToBase64(String reference) and decodeBase64ToReference(String base64Content) have a file size limit of 10 MB.

4. File Server

Storage: 500 GB.

Concurrent connections: Maximum of 20 connections per service instance

Oracle doc reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/oracle-integration-oci/service-limits.html#GUID-EB64C32A-BAEE-4ABC-BF2D-0E8B3FD5B848