Tuesday, May 16, 2023

OIC - ERP Bulk Import - Do we always need to provide manifest or .properties file for a import job

Rules to follow:

Basically, if an import job has associated with parameters, then we need to send a manifest or .properties file with the source file as a zip. But if the import job does not have any parameters, then we don't send any manifest file at all. 

For example, we have GL daily rates import job which does  not have any parameters. So simple a source file is enough to do bulk import to ERP.

Otherside,  AP invoice, Supplier or Journal are associated with parameters, thus for them, we need to send .properties file with the source files as zip to do bulk import.

OIC - How to continue FOR EACH loop for any failure case | Oracle Integration

Problem Description: Suppose, we have a scenario where we are taking the employees data as a rest trigger and writting for each employee one by one to a database table. Here, if any employee data gets errored out then what will happen, the process will be stopped from the moment and other employees will not be inserted to Database. 

What we can do so that all valid data employees to be inserted into database table irrespective of any failure for an employee invalid data. The invalid employee data can be discarded and stored to a csv file and do a mail to business.


We can follow the below 2 steps to achieve this requitement:

Step1: Take a scope inside the FOR EACH loop and insert the data into db.

Step2: Go to the scope default fault handler >> handle the error, like this case, we are writing the failed employee details as a CSV file to a FTP directory


Scenario: Tested with 3 employees where 1st employee has invalid department data which exceeds the actual size.

You can see 1st employee has not been inserted to database table. Other 2 employees has been successfully inserted to database even the 1st employee got errored out.

Thursday, May 11, 2023

OIC - Convert CSV file data to JSON | Oracle Integration

Usecase: we will poll a csv file using FTP adapter and convert it to json file using FTP adapter configuration.

Highlevel steps:

  1. Create FTP connection.
  2. Create an Scheduled orchestrated integration and call FTP adapter and poll the CSV file.
  3. Drag and drop FTP adapter again and configure to write json file to a directory.
  4. Map the csv file response to write file json data.

CSV file:

Json format:

Detailed steps with screenshots:

XSD - Convert XML payload to XSD Schema online | Sample Employees XML and XSD

We can convert a XML file to XSD online easily. Several free online tools are available in the market.

I have used below URL to convert:


Suppose you have below employee XML:

<?xml version="1.0" encoding="UTF-8" ?>
























Converted XSD:

<?xml version="1.0" encoding="utf-8"?>
<!-- Created with Liquid Technologies Online Tools 1.0 (https://www.liquid-technologies.com) -->
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Employees">
        <xs:element maxOccurs="unbounded" name="Employee">
              <xs:element name="FirstName" type="xs:string" />
              <xs:element name="LastName" type="xs:string" />
              <xs:element name="Department" type="xs:string" />
              <xs:element name="Qualification" type="xs:string" />
              <xs:element name="Age" type="xs:unsignedByte" />

Tuesday, May 9, 2023

OIC - Oracle Integration Generation 3 New Features

OIC Gen 3 New Features:

  1. Oracle Redwood theme : The Oracle integration user interface reflects the Oracle Redwood look and feel, All screenshots, icons, and termonology has been updated in the Oracle Integration library accordingly.
  2. Shape determines your upgrade window : When you create an instance in the OCI console, you must choose a shape, either Production or Developement. The shape determines when the instance receives monthly and quaterly updates. Instances with a development shape receive updates two weeks before the instances with a production shape. Once you selected the shape, you cant change the shape of an instance.
  3. Real time billing and usage data: Billing happens on the number of actual messgaes consumed , not the configured number of messages packs. On the metrics Explorer page, the new graphs show the number of messgaes that you purchased and that you have used.
  4. Oracle integration Home Page metrics : View the total number of messages, total errors and the number of failed integrations. You can also view existing integrations or create new ones from the home page and view recent activities with their status.
  5. Cut and Paste elements in the integrations canvas : Allow you to cut and paste elements(actions and invokes) in the integration canvas. This enables you to move elements to other places in your integration and eliminates the need to recreate them.
  6. Expandable and Collapsible actions : You can expand and collapse logic actions in the integrations canvas ( for example, a scope, while, for-each, switch and others) to see chile actions and view errors.
  7. Control activity stream data retention during integration activation : During integration activation, you will be prompted to select the tracing level. The level you select determines how long your data is available in the activity stream. Production: 32 days, Audit: 8 days, Debug: ( Not recommended) : 24 hrs.
  8. Activity stream enhancements : To improve performance, message payloads are accessible differently in the activity stream based on their size. Binary payloads can be downloaded from the activity stream.
  9. Instance Id change from Integer to String or alphanumeric.
  10. New mapper functions : 3 new functions introduced. 
    1. Matches
    2. replace
    3. tokenize.
  11. Test mapper enhancements : the test mapper supports the testing of mappings you created with tracking variables and local variables in assign actions.
  12. Mapper function, operator and XSLT statement embedded help.
  13.  While using connectivity agent, basic authentication is not supported in generation 3. To install and use connectivity agent, we need to use OAUTH 2.0 token based authentication in installerprofile.cfg file.
    1. https://soalicious.blogspot.com/2023/10/oic-gen3-connectivity-agent-install.html
  14. Parallel action. Follow my blog.
    1. https://soalicious.blogspot.com/2023/09/oic-gen3-parallel-action.html
  15.  Publish Event action. Follow my blog
    1. https://soalicious.blogspot.com/2023/09/oic-gen3-how-to-publish-and-subscribe.html
  16. RBAC - Resource based access control over projects.
    1. https://soalicious.blogspot.com/2023/09/oic-gen-3-how-to-regulate-access-to.html
  17. CC, BCC added along with to and from in Email notification action.
  18. Read file in segment - set your own file segment size.
    1. https://soalicious.blogspot.com/2023/10/oic-gen3-read-file-in-segment-set-your.html
  19. TBD


Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...