OIC | Merge two CSV files into one CSV file based on a common field or primary key

Use Case: Here,we will merge two CSV files into one final file based on a common primary key or common field values.

In the below example, we will use EmpId as primary key in both the CSV files for merging.

EmpDetail.csv

EmpId,FName,LName

101,sid,das

102,dip,chak

103,sri,das

EmpContact.csv

EmpId,Contact

101,101_123456

102,102_234567

101,102_987654

103,103_456789

Merged file:

EmpId,FName,LName,Contact

101,sid,das,101_123456 102_887654

102,dip,chak,102_234567

103,sri,das,103_456789

Highlevel steps:

1. Read both the CSV files using FTP adapter one by one.
2. For each emp record of 1st CSV file, we will Map both the files and write the Merge file using FTP adapter. We will use a condition to match the records of the both the file using the EmpId of both the files.

Steps in detail:

Integration flow:

Once both files read, we will loop for each emp record of the 1st file.

Map the current emp details and map the contact using following condition:

"$readEmployeeContact/nsmpr2:SyncReadFileResponse/ns24:FileReadResponse/ns22:EmpContactRecSet/ns22:EmpContactRec[ns22:Empid=$currentEmp/nsmpr1:EmpDataRec/nsmpr1:EmpId]/ns22:Contact"

Test:

Note: instead of taking a for each action, we can also do the same in xslt itself:

Xslt code:

<nstrgmpr:WriteFile xml:id="id_12">

<ns30:Employees>

<xsl:for-each select="$readEmployeeData/nsmpr2:SyncReadFileResponse/ns27:FileReadResponse/ns26:/EmpDataRecSet/ns26:/EmpDataRec">

<xsl:variable name="EmpId" select="ns26:/EmpId"/>

<ns30:Employee>

<ns30:C1>

<xsl:value-of select="ns26:EmpId"/>

</ns30:C1>

<ns30:C2>

<xsl:value-of select="ns26:FName"/>

</ns30:C2>

<ns30:C3>

<xsl:value-of select="ns26:LName"/>

</ns30:C3>

<ns30:C4>

<xsl:value-of select="$readEmployeeContact/nsmpr1:SyncReadFileResponse/ns23:FileReadResponse/ns20:/EmpContactRecSet/ns20:/EmpContactRec[ns20:EmpId=$EmpId]/ns20:Contact"/>

</ns30:C4>

</ns30:Employee>

</xsl:for-each>

</ns30:Employees>

</nstrgmpr:WriteFile>

DB - Check the status for all type of objects using user_objects

 Query:

select object_type, object_name, status from user_objects where object_type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY','TRIGGER') order by object_type,object_name;

Oracle Integration Cloud | Merge two CSV files into a single file without any primary key or common field

Use Case: Here, we will Merge the content of two files into one csv file where there is no common field.

Consideration: we will map the two files sequencially. That means, for 1st row of file1 to 1st row of file2, 2nd row of file1 to 2nd row of file2......nth row of file1 to nth row of file2.

For example,

File1.csv:

FirstName,LastName

sid,das

dip,chak

File2.csv

EMail

sid@gmail.com

dip@gmail.com

Merged file:

FirstName,LastName

sid,das,sid@gmail.com

dip,chak,dip@gmail.com

Highlevel steps:

1. Create a scheduled orchestration with a name.
2. Drag and drop the FTP adapter and read both the source files one by one.
3. Drag and drop the FTP adapter to write the final merge file. Here, in the map, for each file1 record, we will create a variable named "i" with value "position()" and that postion we will use to fetch the value from file 2 hsing $i.

Steps in detail(with screenshots):

Integration flow

Read file1

Read file2

Write Merge file

Map codes:

<xsl:template match="/" xml:id="id_11">

<nstrgmpr:WriteFile xml:id="id_12">

<ns28:FileMergedRecSet>

<ns28:FileMergedRec>

<ns28:C1>

<xsl:value-of select="'Fname'"/>

</ns28:C1>

<ns28:C2>

<xsl:value-of select="'Lname'"/>

</ns28:C2>

<ns28:C3>

<xsl:value-of select="'Email'"/>

</ns28:C3>

</ns28:FileMergedRec>

<xsl:for-each select="$readFile1/nsmpr1:SyncReadFileResponse/ns21:FileReadResponse/ns22:File1RecSet/ns22:File1Rec">

<xsl:variable name="i" select="position()"/>

<ns28:FileMergedRec>

<ns28:C1>

<xsl:value-of select="ns22:FirstName"/>

</ns28:C1>

<ns28:C2>

<xsl:value-of select="ns22:LastName"/>

</ns28:C2>

<ns28:C3>

<xsl:value-of select="$readFile2/nsmpr2:SyncReadFileResponse/ns25:FileReadResponse/ns24:File2RecSet/ns24:File2Rec[$i]/ns24:EMail"/>

</ns28:C3>

</ns28:FileMergedRec>

</xsl:for-each>

</ns28:FileMergedRecSet>

</nstrgmpr:WriteFile>

</xsl:template>

OIC | Split a CSV file into multiple CSV files based on a column distinct values in Oracle integration cloud

Use Case : We have a Source CSV file containing rows for different BU values. Based on the BU column, we will create separate CSV files for each unique Business Unit values. For instance, for "N" unique BU values, we will segregate them into "N" of CSV files.

Example,

Source file:

CompanyName,CompanyID,BU,Location

abc,101,BU1,US

cde,105,BU2,IND

efg,501,BU3,PH

ghi,102,BU1,US

ijk,101,BU2,IND

Outputs:

We have 3 Unique BUs= BU1,BU2,BU3 so we should have 3 files segregation.

Output files:

BU1.csv

abc,101,BU1,US

ghi,102,BU1,US

BU2.csv

cde,105,BU2,IND

ijk,101,BU2,IND

BU3.csv

efg,501,BU3,PH

Highlevel steps:

1. Create a scheduled orchestration and provide a name.
2. Drag and drop the FTP adapter and use Read a file operation to poll the source file.
3. Drag and drop the Stage file action just after the FTP adapter and configure a Write a file operation. Use for-each-group and group by element to fetch unique BU values in the map.
4. Drag and drop the Stage file action and Read a file operation to read distinct BU file.
5. Drop a for-each action and use the repeating element from the stage file read action.
6. Drag and drop an FTP connection inside this loop to write the records of each BU independently.

Steps in detail:

Integration flow:

Read file using FTP connection and using a sample CSV source file.

Take a stage and write Distinct BUs in a file using for-each-group and group by elements.

Map codes:

<xsl:template match="/" xml:id="id_12">

<nstrgmpr:Write xml:id="id_12">

<ns23:BUs>

<xsl:for-each-group select="$readFile/nsmpr1:SyncReadFileResponse/ns18:FileReadResponse/ns18:Companies/ns18:Company" group-by="ns18:BU">

<ns23:BU>

<ns23:BUColumn>

<xsl:value-of select="ns18:BU"/>

</ns23:BUColumn>

</ns23:BU>

</xsl:for-each-group>

</ns23:BUs>

</nstrgmpr:Write>

</xsl:template>

Take a Stage and read the BUs file.

Take a for-each and for each unique BUs

Write Split files using FTP adapter 

Map the following 3 things:

1. File name generation: cutrentBU >> BU>> C1 to OutboundFTPHeaderType >> fileName

Concat($currentBU/nsmpr1:BU/nsmpr1:C1,".csv")

2. Repeat the company element and for 1st company , hardcode header values.

3. For ReadFile Response (FTP) , map each company elements to target comlany element with the following condition:

$readFile/nsmpr3:SyncReadFileResponse/ns25:FileReadResponse/ns23:Companies/ns23:Company[$currentBU/nsmpr1:BU/nsmpr1:C1=ns23:BU]

OIC Certificate Management | Different type of Certificates in Oracle Integration Cloud | Navigations | How to upload Certificate | Identity store vs Trust Store

Why We need Certificates:

• Certificates are used to validate outbound SSL connections. If we make an SSL connection in which root certificates do not exist in OICS, an exception will be thrown. In such cases, we must upload the appropriate certificates.
• A certificate enables OICS to connect with external services. If the external endpoint requires a specific sertificate, request the certificate and then upload it into Oracle integration.
• PGP type certificates or keys are used to encrypt or decrypt the files.

Prerequisite

Enable following feature:
• oic.suite.settings.certificate  (Suite level certificate landing page)

To enable feature flags - Refer to Blog on Enabling Feature Flags in Oracle Integration

The minimum Oracle Integration version required for the feature is 190924.1600.31522

Type of Certificates:

Type: X509 (SSL Transport)  – An SSL/TLS X.509 certificate is a digital file that's usable for Secure Sockets Layer (SSL) or Transport Layer Security (TLS). The certificate can assist with authenticating and verifying the identity of a host or site thus enables Oracle Integration to connect with external service.

• Category: Identity (Ex. .jks):
• An identity certificate is a keystore which can contain various certificates with passwords. 
• Use this option to upload certificate for two way SSL communication.
• Category: Trust  (Ex. .crt or .cert): 
• use this option to upload a trust certificate.

Type: SAML(Authentication & Authorization) - SAML refers to the XML variant language used to encode information.

• Category: Message Protection - Its a Message Protection certificate which has SAML token support.

Type: PGP (Encryption & Decryption) - Pretty Good Privacy (PGP) is used in Stage File for signing, encrypting, and decrypting texts.

• Category: Private - Content can be decrypted with private PGP key.
• Category: Public - Content can be encrypted with public PGP key. 

Navigation To Certificate:

Settings >> Certificates

Certificates Upload:

• Click on the upload on top-right corner.
• A drawer opens up with the details to fill up.
• Enter alias name which identifies the certificate.
• Give a brief description (optional) about the certificate you are uploading.
• Select the type of Certificate you want to upload. You can choose from the list: X.509, SAML, and PGP.
• Choose the category of certificate. For a X.509 → Trust, Identity, SAML → Message Protection, and PGP → Public, Private.
• Choose a file from your local system to upload.

The main difference between Identity and Trust stores:

Identity Store : Used to store Server Certificates , CA certificate Chain , Public private key pair. Whenever some client requests Server to communicate on SSL, server uses them to serve the SSL certificate to a client.

Trust Store: Used to store third party Root certificates or Third Party server certificates. It is used to validate the third party server certificates while communicating over SSL.

When you configure SSL, you must decide how identity and trust will be stored. Although one keystore can be used for both identity and trust, Oracle recommends using separate keystores for both identity and trust because the identity keystore ( contains private key/digital certificate pairs ) and the trust keystore (contains trusted CA certificates only) may have different security requirements.