soalicious

Friday, June 9, 2023

OIC - Read Multiple records are of different types CSV file in Oracle Integration where 1st column does not have default values

UseCase: Read multiple records are of different types CSV file in Oracle Integration where 1st column does not have default values.

If we have requirement to read a "multiple records are of different types" CSV file in Oracle Integration where 1st column have default values. Then follow my below blog:

https://soalicious.blogspot.com/2023/06/oic-read-multi-records-of-different.html

In this blog, we have the following file to read:

Source file to be read:

Header1, Header2

Line1,Line2,Line3,Line4

Line11,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Header11, Header2

Line1,Line2,Line3,Line4

Line11,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Header111, Header2

Line1,Line2,Line3,Line4

Line11,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Important notes:

If you notice the file format, you will see the following:

One set is repeating mutilple times where

Header record 1 time

Line records 4 times fixed count

Implementation steps:

First create an XSD based on the CSV file using Jdeveloper File adpater configuration.

For the steps, Follow my previous blog :

https://soalicious.blogspot.com/2023/06/oic-read-multi-records-of-different.html

Then modify the xsd a little so that it can read this file properly.

Changes done:

Replace the choice element with sequence and make maxOccurs as unbounded.
Added a SET element as a pair element for repeating header and lines.
Remove the conditionValues element as we dont have default values as 1st column.

Modified XSD:

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

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" xmlns:tns="http://TargetNamespace.com/fileReference" targetNamespace="http://TargetNamespace.com/fileRefeference" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars" nxsd:encoding="US-ASCII">

<xsd:element name="Root-elememt">

<xsd:complexType>

<xsd:sequence maxOccurs="unbounded" >

<xsd:element name="SET">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="HEADER">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="header1" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="header2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="LINE" minOccurs="4" maxOccurs="4">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="line1" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="line2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="line3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="line4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

References:

https://technology.amis.nl/amis/oracle-11g-soa-suite-read-multi-record-data-from-csv-file-with-the-file-adapter-master-detail/

Wednesday, June 7, 2023

OIC - Read Multi records of different types CSV file | How to create a multi records of different types XSD to read CSV file

Use Case: We have multi Records are of Different types CSV file. How we can create a schema for the same and read via oracle integrarion.

File to be read: InputSourceData.txt

Invoice,header1, header2,header3,header4

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice,header1, header2,header3,header4

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Trailer, Trailer1

Notes: This case, we have following 3 types of records and 1st column values are fixed which would be our conditionValue while generating the xsd. >>

Invoice (Header)
Invoice Line(Line)
Invoice Trailer(Trailer)

Implementation steps:

Step1: Create XSD from Jdeveloper tool

Create a SOA project and open composite.xml canvas and drag and drop a File adapter:

XSD is generated but while you tested the C1 or column 1 value for each record is missing due to have conditionValue attribute. Here we modify the code little to add the column 1 fixed or default values:

For each record add the below line and it works:

<xsd:element name="C1" type="xsd:string" default="Invoice"/>

The Modified XSD:

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

<xsd:element name="Root-elememt">

<xsd:complexType>

<xsd:choice minOccurs="1" maxOccurs="unbounded" nxsd:choiceCondition="terminated" nxsd:terminatedBy=",">

<xsd:element name="RECORD1" nxsd:conditionValue="Invoice">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice"/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="C3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="RECORD2" nxsd:conditionValue="Invoice Line">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice Line"/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="C3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="C6" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="C7" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="C8" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>

<xsd: element name="C9" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="RECORD3" nxsd:conditionValue="Invoice Trailer">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice Trailer "/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:choice>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Step2: Create a scheduled orchestration integration and drag and drop a FTP adpater connection and read the file using this created schema.

Activate and test.

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.

Solution:

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

Testing:

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:

Create FTP connection.
Create an Scheduled orchestrated integration and call FTP adapter and poll the CSV file.
Drag and drop FTP adapter again and configure to write json file to a directory.
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:

https://www.liquid-technologies.com/online-xml-to-xsd-converter

Suppose you have below employee XML:

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

<FirstName>Sanddy</FirstName>

<Qualification>BTECH</Qualification>

</Employee>

</Employee>

<FirstName>Rupesh</FirstName>

</Employee>

</Employees>

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:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="Employee">
          <xs:complexType>
            <xs:sequence>
              <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" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Tuesday, May 9, 2023

OIC - Oracle Integration Generation 3 New Features

OIC Gen 3 New Features:

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.
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.
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.
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.
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.
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.
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.
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.
Instance Id change from Integer to String or alphanumeric.
New mapper functions : 3 new functions introduced.

Matches
replace
tokenize.

Test mapper enhancements : the test mapper supports the testing of mappings you created with tracking variables and local variables in assign actions.
Mapper function, operator and XSLT statement embedded help.
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.