Change date time format in Oracle Integration Cloud

We got a request from multiple folks saying they are struggling to change the date time format in Oracle Integration Cloud.

We are writing this article to see how to change the default date format(2019-04-13T05:09:05.049+00:00) to a different format.

Oracle Integration Cloud Service provides an Out of the box function(xp20:format-dateTime) which allows us to change the date time format. We are on ICS version (18.4.3.0.3) and the function is available(xp20:format-dateTime)

Let us see how to apply this function and change the date format as per the requirement

The function(xp20:format-dateTime) takes below two arguments:

1. dateTime: Provide the DateTime input parameter as a string which needs to be the format
2. Format: Provide the format as a string as per the requirement

Example-1: Parse the date time in the format "dd-MM-yyyy", below will be the syntax:

xp20:format-dateTime(dateTime,'[D01]-[M01]-[Y0001]')

Example-2: Parse the date time in the format "dd-MM-yyyy HH:mm:ss", below will be the syntax:

xp20:format-dateTime(dateTime,'[D01]-[M01]-[Y0001] [h01]:[m01]:[s01]'

Example-3: Parse the date time in the format "MM/dd/yyyy HH:mm:ss", below will be the syntax:

xp20:format-dateTime(dateTime,'[M01]/[D01]/[Y0001] [h01]:[m01]:[s01]')

Example-4: Parse the date time in the format "yyyy-MM-dd h:mm AM/PM", below will be the syntax:

xp20:format-dateTime(dateTime,'[Y0001]-[M01]-[D01] [h1]:[m01] [P]')

Example-5: Parse the date time in the format "HH:mm TimeZone", below will be the syntax:

xp20:format-dateTime(dateTime,' [H01]:[m01] [z]')

Example-6: Get the ordinal value of the day, below will be the syntax:

xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,' [Dwo]')

Example-7: Get the name of the Month in the capital, below will be the syntax:

xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,' [MNn]')

Example-8: Get the day of the week, below will be the syntax:

xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,' [FNn]')

Below are the explanations for all the formatting codes in the example:

Value	Description
D01	Produces the two-digit day
M01	Produces the two-digit month
Y0001	Produces the four-digit year
h01	Produces the 2-digit hour in a 24-hour clock
m01	Produces the 2-digit minutes
s01	Produces the 2-digit seconds
h1	Produces the 1- or 2-digit hour in a 12-hour clock
P	Generates a.m. or p.m.
z	Produces the time zone
MNn	Generates the capitalized name of the month. For example: If we are sitting in April month, it will produce “April”
FNn	The capitalized name of the day of the week For example: If today is Saturday, it will produce “Saturday”
Dwo	The word for the ordinal value of the day For example: If today is 13th April, it will produce “thirteenth”

• Let's go ahead and create a scheduler process in ICS and create 8 variable as per the example was given above

• Print all 8 variables in the different logger. Activate the scheduler and execute
• Open the activity stream and see the values of all 8 variables. Notice the start time(since we have given the start time as an input of the function) and accordingly the result of each format. 

Download CSV file from REST service Response Content: Oracle Integration Cloud

There are certain REST Services which provides the CSV file as part of the response and needs to consume in the Integration Cloud. In the article, we'll demonstrate how to catch the CSV file from REST service response leveraging Integration Cloud REST Adapter.

In the below Screenshot, when we hit the REST Service in the browser URL box then it downloads a CSV file.

Let's go step by step and see how to achieve this use case

• Create a Scheduler with the name(DownlaodCSV)

• Drop the REST service connection and configure the below option on the initial page
• What do you want to call your endpoint: DownloadFile
• What is the endpoint's relative resource URI?: /{path}
• What action does the endpoint perform: GET
• Check the checkbox Add and review parameters for this endpoint
• Check the checkbox Configure this endpoint to receive the response 

• Click the Next button on the next screen

• Select Raw Radio button of the Select the response payload format. Click the Next button and finish the wizard

• Open the edit mapper and configure the template parameter as per the REST service

• Drop the Stage action just below the REST adapter and enter "WriteToStage" in What do you want to call your action? text box

• Enter the below information and select the Next button
• Choose Stage File Operation: Write File
• Specify the File Name: test.csv
• Specify the Output Directory: /stage

• Select the "Select an existing schema from the File system" radio button

• Past the below schema in notepad file, save it and select using the "Choose File" option

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

<schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/"

            xmlns="http://www.w3.org/2001/XMLSchema" >

      <element name="opaqueElement" type="base64Binary" />

</schema>

• Edit the "WriteToStage" mapper, click on the "opaqueElement", drop the "encodeReferenceToBase64()" function and then drop the "streamReference" element from left panel on the function "encodeReferenceToBase64()"

• Drop another "Stage" activity, and enter "ReadFromStage" in What do you want to call your action? text box

• Enter the below information and select the Next button
• Choose Stage File Operation: Read Entire File
• Specify the File Name: test.csv
• Specify the Output Directory: /stage

• Select "Create a new schema from a CSV file" Radio button and click the Next button

• Configure below option and click the Next button
• Select a new Delimited Data File: Choose the sample CSV file which must be of the same type as REST service response
• Enter the Record Name: Record
• Enter the Recordset Name: Records

• Finish the wizard. Integration is completed and ready to test now. Save and Close the integration and simply Submit

Open the "DownloadCSV" instance and open the activity stream. The CSV data should be printed into the payload section

Fetch Email attachments using Microsoft Email adapter in Oracle Integration Cloud

In the article, we'll show how to fetch the attachments from email and dump the attachments on the SFTP server.

To complete this article, we should have in place:

1. MS email connection: If not created, use the post to create a connection with Microsoft outlook
2.  FTP connection: If not created, use the post to create a connection with FTP

Below is the end to end use case which is demonstrated in the article:

• Fetch the emails from MS outlook
• Extract the attachments from each and every email
• Dump all attachments on the SFTP server

Let's go ahead and see how to achieve this

• Create a scheduled integration with name "GetAttachments"

• Drop the Microsoft Email adapter just below the scheduled activity. The configuration wizard will be opened. On the very first screen enter below information and click the Next button
• What do you want to call your endpoints: Enter the name of your choice let's say "FetchEmails"

• Choose an operation "Get message of a folder" and simply click the Next button

• On the last page of the wizard, simply click the Done button

• Edit the "FetchEmails" mapper and hardcode the "Inbox" value in "folder_id" template parameter as we are fetching the email from "Inbox" only

• Drop the "For Each" activity just below the MS email adapter and configure the below options in the opened wizard of "For Each" activity
• Name: Enter the name of your choice, let's say "LoopEmails"
• Repeating Element: Drop the "value" element of the $FetchEmails node
• Current Element Name: Enter the name of your choice, let's say "CurrentEmail"

• Drop the Microsoft Email adapter again under For Each loop. The configuration wizard will be opened. On the very first screen enter below information and click the Next button
• What do you want to call your endpoints: Enter the name of your choice let's say "FetchAttachments"

• Choose an operation "Get an attachment collection" and simply click the Next button. 

• Edit the "FetchAttachments" mapper and drop the "id" element under the "$CurrentEmail" node to the "message_id" template parameter 

• Drop the "For Each" activity just below the MS email adapter again and configure the below options in the opened wizard of "For Each" activity. This loop will be used to loop over the attachments of each and every email
• Name: Enter the name of your choice, let's say "LoopAttachments"
• Repeating Element: Drop the "value" element of the $FetchAttachments node
• Current Element Name: Enter the name of your choice, let's say "CurrentAttachment"

• Now, drop the FTP adapter inside the inner "For Each" loop. The configuration wizard will be opened. On the very first screen enter below information and click the Next button
• What do you want to call your endpoints: Enter the name of your choice let's say "WriteAttachment"

• On the Next screen, enter the information as below and click on the Next button
• Select Operation: Write File
• Select a Transfer mode: ASCII
• Specify an Output Directory: Enter directory location where the attachments will be dumped. In our case it is "/home/opc/attachments"
• Specify a File pattern: Let's say it is "abc.csv"
• Enable PGP security: No

• Select "No" from the Do you want to define a schema for this endpoint ? and click the Next button and finish the wizard

• Edit the "WriteAttachment" mapper and do below mapping:
• FileReference: Click on this element, drop the seeded function "decodeBase64ToReference" on this element and map the "ContentBytes" element of the "$CurrentAttachment" node

• $CurrentAttachment->Name  ---> OutboundFTPHeaderType->fileName

• Integration is completed now. Activate the integration. Before submitting the integration, open the outlook email box and see the emails and attachments of each and every email. In my case, we have two emails, one has two attachments and another is having a single attachment. In total, there are three attachments

• Let's submit the integration. After completion of the integration, open the SFTP location and notice the three files should exist there

This is how attachments can be fetched from outlook Inbox and save it to the SFTP location.

Gmail adapter in Oracle integration cloud

The Gmail adapter of Oracle Integration Cloud enables us to create an integration with the Gmail application. This is one of the OOB adapters that Integration Cloud offers.

In this particular article, we'll show you how to create Gmail connection using Google Email adapter.

Prerequisites for Creating a Connection

To use the Gmail adapter, we must have access the Gmail APIs. So to access the Gmail APIs, we must create the Gmail project.

Below are the steps to create Gmail project:

• Open the Gmail developer page https://console.developers.google.com
• Click the Select a project dropdown list

• Click on the New Project

• Enter Project Name(ICSGmailConn) and click on the Create button

• Once the project is created, make sure the newly created project(ICSGmailConn) is selected in the dropdown list

• Click ENABLE APIS AND SERVICES

• Search Gmail API and Select the same

• Click ENABLE

• On the right-hand side, click CREATE CREDENTIALS button

• Click on the Credentials from the left navigation

• Click on the Create Credentials drop down and select OAuth Client ID

• Select Web application from the application type

• In the Name field, specify a name for the OAuth 2.0 client ID credential
• In the Authorized Redirect URI field, enter the following URL and click Create button

https://{ICS_HOST}:{ICS_SSL_PORT}/icsapis/agent/oauth/callback

For example:

https://icshost.mycompany.com:443/icsapis/agent/oauth/callback

• Once created, it will give the client id and client secret. Copy and paste the same at a safe location because it would be required at the time of Connection creation

• Optionally, we can set the Scope for Google APIs from the OAuth consent screen page

Now let go ahead and create the Gmail adapter connection.

• Login into the Integration cloud console and move to the Connection
• Click on the Create button and search for the Google Mail adapter and select

• Enter connection Name(Gmail_conn) as per your choice and Click on Create button

• Click on the Configure Security button and enter below information
• Client Id: Enter the client id which was copied earlier
• Client Secret: Enter the client secret which was copied earlier
• Confirm Client Secret: Enter the client secret which was copied earlier
• Scope: Enter the scope for which the adapter functionality is limited to as below. For the time being, enter https://www.googleapis.com/auth/gmail.readonly and click on the Provide Consent button
• https://mail.google.com/
• https://www.googleapis.com/auth/gmail.compose
• https://www.googleapis.com/auth/gmail.insert
• https://www.googleapis.com/auth/gmail.labels
• https://www.googleapis.com/auth/gmail.modify
• https://www.googleapis.com/auth/gmail.readonly
• https://www.googleapis.com/auth/gmail.send

• Clicking the Provide Consent button will take to the different window where it will ask you to enter the Integration Cloud credentials(if already not logged-in in the same window). After which, it will prompt to enter the Gmail credentials from which we have created the client id and client secret. If already logged-in select the Gmail account and click Allow button

• Once the consent is successful, Access Allowed page will come up

• Go back to the Integration Cloud connection page and Test. If everything is OK, the connection will reach 100%