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.

OIC Oracle Database polling | Oracle Integration cloud DB Polling

Here, we will see how to poll new rows from Oracle database and then write them to a file.

Highlevel steps:

1. Create a db table for polling
2. Create a Db Trigger connection
3. Creat an app driven orchestration integtation and configure db trigger 
4. Call a ftp connection adapter and write file

Steps in detail(With Screenshots):

Create a Database table and insert 2 rows.

Create Table EMPLOYEES_NEW

(

"ID" VARCHAR2(20 BYTE) ,

"NAME" VARCHAR2(200 BYTE),

"ISNEW" VARCHAR2(10 BYTE)

)

INSERT INTO EMPLOYEES_NEW(ID,NAME,ISNEW) VALUES(13,'Sanddy1','NEW')

INSERT INTO EMPLOYEES_NEW(ID,NAME,ISNEW) VALUES(14,'Dip1','NEW')

Create a database Trigger connection

Choose Oracle Database

Provide Connection Name, select role as Trigger >> Create

Provide Host, Port and Sid or service name

Select security as Username Password Token and provide user and password details. If the db is configured with Agent, then choose the respective agent group.

Create an App driven orchestration.

Adding Db Trigger and configure

Drop the Oracle DB connection as a Trigger type.

Enter a endpoint Name

Click Import Tables

Select the DB schema and enter table name and Search >> select the table and click Ok

Click on Edit button of Review the polling strategy and specify polling options.

Polling strategy: Logical delete

Logical Delete field: ISNEW

Read Value: PROCESSED(this value indicates the row has been processed)

Unread value: NEW( This value indicates the row to be processed)

Polling Frequency(Sec): 10(after which time of secconds, the rows wil be polled)

Click Next

Done

Adding a FTP adapter to write file.

Drop a FTP conntection

Privide Endpoint name

Select Operation as Write file

Select a Transfer mode as ASCII

Output directory, File Name and select Append to Existing file option.

Privide a CSV file sample.

Map the polling data to write file.

Save and Activate

Before activation:

After Activation

File content:

Note: suppose at a time there are multiple new rows in the db table, then for each row, separate transaction will be created and that we can observe in the monitoring page