Types of encryption techniques

There are several types of encryption techniques used to secure data, each with its own use case and strengths:

1. Symmetric Encryption

Uses a single key for both encryption and decryption.

• Examples: AES (Advanced Encryption Standard), DES (Data Encryption Standard), 3DES (Triple DES), Blowfish, Twofish

2. Asymmetric Encryption

Uses a pair of public and private keys for encryption and decryption.

• Examples: RSA (Rivest-Shamir-Adleman), ECC (Elliptic Curve Cryptography), Diffie-Hellman, ElGamal

3. Hashing

Transforms data into a fixed-length hash that cannot be reversed.

• Examples: SHA (Secure Hash Algorithm), MD5 (Message Digest Algorithm), bcrypt, Argon2

4. End-to-End Encryption (E2EE)

Ensures data remains encrypted throughout transmission and can only be decrypted by the intended recipient.

• Examples: Signal Protocol, PGP (Pretty Good Privacy), TLS (Transport Layer Security)

5. Homomorphic Encryption

Allows computation on encrypted data without decrypting it first.

• Examples: Paillier Cryptosystem, BGV (Brakerski-Gentry-Vaikuntanathan), CKKS (Cheon-Kim-Kim-Song)

6. Quantum Encryption

Leverages quantum mechanics for ultra-secure encryption.

• Examples: Quantum Key Distribution (QKD), BB84 Protocol

Each encryption type serves a specific purpose, from securing online communication to protecting stored data.

OIC - AES encryption and decryption in javascript using CryptoJS

Working...

Use Case

A source application calls an OIC REST service, sending an AES-encrypted request with a cipher key, encrypted message, and IV. OIC needs to decrypt the request, process the data, and encrypt the response before sending it back.

To encrypt a string using the AES algorithm in CBC mode, we need an Encryption Secret, Initialization Vector and Key. Here we will use CryptoJS in javascript.

In the digital era, safeguarding sensitive information is crucial. Encryption serves as a key method to prevent unauthorized access to data. This blog post delves into utilizing CryptoJS, a robust JavaScript library, for encrypting and decrypting data with the AES-256 encryption algorithm.

Steps to follow:

1. Download the cryptojs file from https://github.com/ihsmarkitosi/CryptoJS-v3.1.2/blob/master/rollups/aes.js
2. Use the below mentioned encrypt and decrypt code snippet.
3. First place step1 cryptojs code and then add step2 code. Test it in Online tool to test javascript: https://onecompiler.com/javascript

Encrypt and decrypt code snippet:

function EncryptAESCBC(plaintext, aesKey, aesIV) {

    var key = CryptoJS.enc.Utf8.parse(aesKey);

    var iv = CryptoJS.enc.Utf8.parse(aesIV);

    // Encrypt the plaintext

    var cipherText = CryptoJS.AES.encrypt(plaintext, key, {

        iv: iv,

        mode: CryptoJS.mode.CBC

        //padding: CryptoJS.pad.NoPadding

    });

    var encryptedString = cipherText.toString();

    return encryptedString;

}

function DecryptAESCBC(cipherText, aesKey, aesIV) {

    // IV is a base64 string

    var key = CryptoJS.enc.Utf8.parse(aesKey);

    var iv = CryptoJS.enc.Utf8.parse(aesIV);

    var cipherBytes = CryptoJS.enc.Base64.parse(cipherText);

    var decrypted = CryptoJS.AES.decrypt({ciphertext: cipherBytes}, key, {

        iv: iv,

        mode: CryptoJS.mode.CBC

        //padding: CryptoJS.pad.Pkcs7

    });

    var decryptedString = decrypted.toString(CryptoJS.enc.Utf8);

    return decryptedString;

}

//console.log(DecryptAESCBC(EncryptAESCBC('YWFhYWFhYWFhYWFhYWFhYQ', 'h3Hv332dw8JYJcdx', 'aDNIdjMzMmR3OEpZSmNkeA=='), 'h3Hv332dw8JYJcdx', 'aDNIdjMzMmR3OEpZSmNkeA=='));

Screenshot:

Used in OIC Integrations:

TBD

OIC - Extracting File Name Before the Last Dot and Changing extension from .txt to .csv in OIC

Use Case

In Oracle Integration Cloud (OIC), files are often received with a specific naming convention, such as "abc.01287568371133.yyyymmdd.txt" However, some business processes require converting these .txt files into .csv format dynamically before further processing. Instead of hardcoding file names, we can extract and replace the file extension using XPath functions.

Solution Steps

1. Extract the File Name:

   • Use XPath to fetch the file name from the payload.
   • Example: /ns0:execute/ns3:request-wrapper/ns3:ProcessRequest/ns3:FileDetails/ns3:FileName

2. Find the Last Occurrence of a Dot (.):

   • Identify the position of the last dot (.) in the file name to locate the extension.
   • Function used: oraext:last-index-within-string()

3. Extract the Base Name:

   • Use fn:substring() to retrieve the file name without the extension.

4. Concatenate with .csv:

   • Append .csv to the extracted base name using fn:concat().

Final Expression:

fn:concat(fn:substring(ns3:FileName,1, 

oraext:last-index-within-string(ns3:FileName, '.')),
'.csv')

This converts abc.01287568371133.yyyymmdd.txt → abc.01287568371133.yyyymmdd.csv

OIC - Adjusting Dates in XML Transformations Using XSD Date Functions

Use Case:

In enterprise integrations, especially in Oracle Integration Cloud (OIC) or other XML-based transformation engines, there is often a need to manipulate date values dynamically. One such scenario is adjusting a business date by subtracting or adding a specific duration.

For instance, an XML payload may contain a business date, and a downstream system requires processing based on the previous day's date. The transformation logic should ensure the correct date adjustment while maintaining XML schema compliance.

Solution Steps:

1. Identify the Input Field:

   • The source XML contains a BusinessDate field under the request-wrapper node.
   • The XPath expression to access this field is:

     /nstrgmpr:execute/ns20:request-wrapper/ns20:BusinessDate
     

2. Apply the XSD Date Function:

   • To ensure that the extracted value is interpreted correctly as a date, we use the xsd:date() function.
   • This ensures type safety in XML transformations.
   • It supports value as YYYY-MM-DD or YYYY-MM-DDZ

3. Subtract One Day from the Business Date:

   • The xsd:dayTimeDuration("P1D") function represents a one-day duration.
   • Using the subtraction operator (-), we deduct one day from the business date.

4. Final Transformation Expression:

   • This expression dynamically computes the previous day’s date from the given BusinessDate. xsd:date(/nstrgmpr:execute/ns20:request-wrapper/ns20:BusinessDate) - xsd:dayTimeDuration("P1D")

5. Integrate the Expression in OIC or XSLT Transformations:

   • This logic can be used in XSLT mappings, Oracle Integration Cloud (OIC) expressions, or any XML transformation engine that supports XSD functions.

Screenshot:

OIC - Processing files using Multiple File Name Patterns in OIC File or FTP Adapter

Processing files using Multiple File Name Patterns in OIC FTP Adapter

Use Case

We have a requirement in Oracle Integration Cloud (OIC) to poll two different files with specific naming patterns from an FTP or file system. The files have different prefixes but share a common date pattern (YYYYMMDD). The integration should only process the files when both exist. If one of the files is missing, the integration should throw a fault and exit.

Example File Name Patterns:

• File 1: abc_YYYYMMDD*
• File 2: XYZ_YYYYMMDD*
• File Polling Pattern: abc_YYYYMMDD*|XYZ_YYYYMMDD*

Solution Approach

Step 1: Configure File Adapter to list for  Both File Patterns

• We have used a lookup and put the file name pattern as abc_YYYYMMDD*|XYZ_YYYYMMDD*
• configure the ftp adapter with list operation and map the file name pattern from the lookup. We are also replacing the YYYYMMDD with a fileProcessing date.
• This configuration ensures the adapter picks up files matching either of the patterns.

Step 2: Handle Missing Files with a throw New Fault action

• If either file1Count or file2Count is 0, use a Throw New Fault action: throw new Fault("Both files are required for processing, but one or more are missing.")
• This ensures that the integration stops if any of the required files are missing.
• Add the following logic in the Skip Condition to ensure processing only when both files are present: file1Count > 0 AND file2Count > 0.

Detailed screenshots:

FTP list mapping:

Throw New Fault

OIC - How to configure Dynamic Decryption in OIC

Configure Dynamic Decryption in OIC Based on IsEncrypted schedule Parameter

Use Case

A business receives both encrypted and plain files from an SFTP server. To process both type of files, the integration will use a schedule parameter (IsEncrypted), which will be set to:

'Y' → If the file is PGP encrypted and needs decryption.

'N' → If the file is plain text and does not need decryption.

Goal

• Download the file using the SFTP Adapter.
• Dynamically set the "Decrypt File" option in the Stage File action using the IsEncrypted parameter from the mapper.
• If IsEncrypted = 'Y', enable PGP decryption by setting "true".
• If IsEncrypted = 'N', do not decrypt the file ("false").

Step-by-Step Configuration

1. Add the SFTP Adapter to Download the File

• Drag and drop the SFTP Adapter in the integration.
• Select Download File operation.
• Configure SFTP details (host, authentication, file directory, etc.).
• Store the file in Stage File for processing.

2. Add a Stage File Action to Read the File

• Add a Stage File Action after the SFTP Adapter.
• Select Read File operation.

3. Configure Decryption Dynamically in the Mapper

• Click on Mappings in the Stage File Read action.
• Locate the "Decrypt File" field in the mapping.
• Set the value dynamically using the IsEncrypted parameter:

Expression in Mapper (If-Else Condition)

if ($IsEncrypted = 'Y') then 'true' else 'false'

• This ensures that if IsEncrypted = 'Y', decryption is enabled ("true").
• If IsEncrypted = 'N', the file is read as is ("false").

4. Select PGP Key for Decryption (If Needed)

• If decryption is enabled (true), configure:
  • PGP Private Key (Uploaded in OIC Security).
  • PGP Passphrase.

5. Process the Decrypted or Plain File

• Store the file in Object Storage, Database, or another system.
• Use OIC File Adapter, REST API, or another SFTP Adapter to move/process the file.

Screenshots:

OIC - Processing CSV Files with a Header and Unbounded Detail Records in Oracle Integration Cloud (OIC) | CSV file having 2 different types of records

Processing CSV Files with a Header and Unbounded Detail Records in Oracle Integration Cloud (OIC)

Use Case

Organizations often receive CSV files from external sources, such as banks, vendors, or internal systems, that contain structured data. These files usually have a single header record followed by unbounded detail records (i.e., a dynamic number of transaction rows). The challenge is to efficiently ingest, parse, and process these files in Oracle Integration Cloud (OIC) before integrating them with Oracle ERP, HCM, or other enterprise applications.

For example, a company receives a daily transaction report in CSV format, and the data needs to be validated and loaded into Oracle Fusion ERP for reconciliation.

Solution Approach in OIC

Step 1: Receive the CSV File

• The file is received via SFTP, Object Storage, REST API, or a manual upload.
• OIC’s Stage File action is used to read the CSV file.

Step 2: Define the NXSD Schema for Parsing

• Create an NXSD schema in OIC to define the structure of the CSV file.
• The schema consists of:
  • A header record (e.g., file metadata such as date, batch number).
  • Unbounded detail records (i.e., transactions with dynamic row count).

Step 3: Parse and Extract Data

• Use Stage File → Read File action to parse the CSV file based on the NXSD definition.
• The header is extracted and stored separately for reference.
• The detail records are processed in a loop, allowing for dynamic handling of unbounded records.

Step 4: Transform Data for Downstream Systems

• The extracted detail records are mapped to the required format (e.g., XML, JSON, or another CSV structure).
• Validation rules are applied to filter and clean data before sending it to Oracle Cloud applications.

Step 5: Send Data to the Target System

• The transformed data is sent to Oracle ERP, HCM, or other applications via REST, SOAP, or database integration.
• The processed CSV file can also be archived for future reference in Object Storage or SFTP.

Nxsd code: take help of Jdeveloper tool to create the NXSD.

<?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://BOCCDM.com/payments"          targetNamespace="http://BOCCDM.com/payments" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars" nxsd:encoding="US-ASCII" >

 <xsd:element name="Payments">

        <xsd:complexType>

            <xsd:sequence maxOccurs="unbounded">

                <xsd:element name="Payment">

                    <xsd:complexType>

                        <xsd:sequence>

                            <xsd:element name="Header" minOccurs="1" maxOccurs="1">

                                <xsd:complexType>

                                    <xsd:sequence>

                                        <xsd:element name="C1" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;" />

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

                                    </xsd:sequence>

                                </xsd:complexType>

                            </xsd:element>                       

<xsd:element name="Detail" minOccurs="1" maxOccurs="unbounded">

    <xsd:complexType>

        <xsd:sequence>

            <xsd:element name="C1" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;" />

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

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

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

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

            <xsd:element name="C6" type="xsd:decimal" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;" />

            <xsd:element name="C7" type="xsd:decimal" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;" />

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

            <xsd:element name="C9" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;" />

            <xsd:element name="C10" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;" />

            <xsd:element name="C11" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;" />

            <xsd:element name="C12" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;" />

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

        </xsd:sequence>

    </xsd:complexType>

   </xsd:element>

  </xsd:sequence>

 </xsd:complexType>

</xsd:element>

</xsd:sequence>

 </xsd:complexType>

 </xsd:element>

</xsd:schema>

OIC - Source File Validation Using Stage File and Throw New Fault in OIC

Source File Validation Using Stage File and Throw New Fault in OIC

Use Case

In Oracle Integration Cloud (OIC), validating the source file before processing ensures data accuracy and integrity. This implementation:

1. Reads the file using the Stage File action.
2. Validates the data within the Stage Mapper using the defined schema.
3. If validation errors exist, it throws a fault using the Throw New Fault activity.

Solution Steps

Step 1: Configure the Stage File Action

• Add a Stage File action to the integration.
• Select Read File and choose an appropriate read mode (e.g., "Read Entire File" or "Read in Chunks").
• Define the schema to structure the file content.

Step 2: Implement Validation in Stage Mapper

• Open the Stage Mapper and apply validation rules on fields (e.g., mandatory checks, format validation, length constraints).
• Create a Message Count Variable to track the number of validation errors.
• If a validation error is found, increment the message count and populate the Message field in the validation schema.

Example Validation Schema:

{
  "Validation": [
    {
      "Message": "Error description"
    }
  ]
}

Step 3: Throw a Custom Fault if Errors Exist

• Add an If condition after the Stage Mapper:
  • Condition: Message Count > 0
• If the condition is met:
  • Use Throw New Fault activity.
  • Pass the validation messages in the fault payload.

Example Fault Response:

{
  "Validation": [
    {
      "Message": "Missing required field: Employee ID"
    },
    {
      "Message": "Invalid date format: Start Date"
    }
  ]
}

Detailed screenshots:

Integration flow snap:

Read source file using nxsd:




Read raw file - one line as a column:

Write file using stage and validation json sample

Mapping:

Throw new fault:

Few of the validations conditions:

1. 1st record should start from 5310:

substring($ReadRawFile/nsmpr0:ReadResponse/ns22:FileRowSet/ns22:FileRow[position() = 1.0]/ns22:FileData, 1, 4 ) != "5310"

2. Check file structure - one file header exists, one file trailer exists, atleast one batch header and batch trailer abd detail record exist, number of batch trailer = number of batch header.




((count($ReadSourceFile/nsmpr1:ReadResponse/ns:Payments/ns25:FileHeader) != 1) or 

((count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:FileTrailer) != 1) or 

((count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:BatchHeader) = 0) or 

((count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:Details) = 0) or 

((count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:BatchTrailer) = 0) or 

(count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:BatchHeader) != count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:BatchTrailer))))))

3.for 1st time run, business date shouls match with the system date. 

((/nssrcmpr:execute/ns21:request-wrapper/ns21:ProcessRequest/ns21:IsReprocess != "Y") and 

($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:FileHeader/ns25:ValueDate != 

xp20:format-dateTime(fn:adjust-dateTime-to-timezone(fn:current-dateTime(), xsd:dayTimeDuration("PT8H")), "[Y0001][M01][D01] ")))

4.2nd record should start with RH.

substring($ReadRawSourceFile/nsmpr0:ReadResponse/ns23:FileRowSet/ns23:FileRow[position() = 2.0]/ns23:FileData, 1, 2 ) != "RH"

5. Value date is in YYYYMMDD format.

((/nssrcmpr:execute/ns21:request-wrapper/ns21:ProcessRequest/ns21:IsReprocess != "Y") and 

(ns25:ValueDate != xp20:format-dateTime(fn:adjust-dateTime-to-timezone(fn:current-dateTime(), xsd:dayTimeDuration("PT8H")), "[Y0001][M01][D01] ")))

6. Store number should have 004 0r 024 only + store number should be non numeric.

not(contains("004,024", ns25:StoreNumber)) or 

(string(number($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:BatchHeader/ns25:StoreNumber)) = "NaN")

7. Input time ahould be in hhmmss format.

xp20:format-dateTime(concat("2025-01-01T", substring(ns25:InputTime, 1, 2), ":", substring(ns25:InputTime, 3, 2)), "[Y0001][M01][D01][H01][m01]") 

!= concat("20250101", ns25:InputTime)

8.validate if RT record is followed by D1 record.

If substring(ns23:FileData, 1,2) = "RT"

If substring ($ReadRawSourceFile/nsmp0:ReadResponse/ns23:FileRowSet/ns23:FileRow[ns23:FileData = current()/ns23:FileData]/preceding-sibling::ns23:FileRow[1.0]/ns23:FileData, 1, 2 ) != “D1”

9. Validate if last -1 record should be RT

substring ($ReadRawSourceFile/nsmp0:ReadResponse/ns23:FileRowSet/ns23:FileRow[(position() = (last() - 1.0))]/ns23:FileData, 1, 2 ) != "RT"

10. Validate if batch trailer amount are non numeric value.

((string(number(oraext:create-delimited-string($ReadSourceFile/nsmp1:ReadResponse/ns25:Payments/ns25:BatchTrailer/ns25:TotalAmountoftheApprovedDebits, ","))) != "NaN") 

and (string(number(oraext:create-delimited-string($ReadSourceFile/nsmp1:ReadResponse/ns25:Payments/ns25:Details[(ns25:TransactionStatus = "A")]/ns25:TransactionAmount, ","))) != "NaN"))

11. Sum of batch trailer amount equal to sum of all detail amount

sum($ReadSourceFile/nsmp1:ReadResponse/ns25:Payments/ns25:BatchTrailer/ns25:TotalAmountoftheApprovedDebits) 

!= 

sum($ReadSourceFile/nsmp1:ReadResponse/ns25:Payments/ns25:Details[(ns25:TransactionStatus = "A")]/ns25:TransactionAmount)

OIC - Creating Folders in SharePoint Using Microsoft Graph API 1.0 in Oracle Integration Cloud

Use Case

Many businesses need to automate folder creation in SharePoint based on dynamic inputs. A common requirement is to create folders under a given SharePoint site and a specified parent path. The parent path can be a direct drive or a nested structure like A/B or A/B/C, where A is the drive name.

Using Microsoft Graph API 1.0, we can achieve this by sending requests to create folders dynamically based on an input JSON file containing:

• Site Name: The SharePoint site where folders should be created.
• Parent Path: The target location within the document library.
• Folder Name: The name of the new folder to be created.

Solution Steps

Step 1: Prerequisites

Before proceeding, ensure the following:

• Access to Microsoft Graph API 1.0
• Appropriate SharePoint permissions (Files.ReadWrite.All or Sites.Manage.All)
• Azure AD App Registration with API permissions granted

Step 2: Retrieve the Site ID

First, fetch the Site ID using the site name:

API Request:

GET https://graph.microsoft.com/v1.0/sites/{tenant-name}.sharepoint.com:/sites/{site-name}
Authorization: Bearer {access-token}

Response:

{
  "id": "site-id",
  "name": "Your Site Name"
}

Step 3: Retrieve the Drive ID

Every SharePoint document library is associated with a drive. Retrieve the drive ID using:

API Request:

GET https://graph.microsoft.com/v1.0/sites/{site-id}/drives
Authorization: Bearer {access-token}

Response:

{
  "value": [
    {
      "id": "drive-id",
      "name": "Documents"
    }
  ]
}

Step 4: Create Folders Dynamically

To create a folder, use the parent path from the input JSON.

Scenario 1: Creating Folder in Root (A)

If the parentPath is A, the request should be:

API Request:

PUT https://graph.microsoft.com/v1.0/drives/{drive-id}/root/children
Content-Type: application/json
Authorization: Bearer {access-token}

{
  "name": "FolderName",
  "folder": {},
  "@microsoft.graph.conflictBehavior": "fail"
}

Scenario 2: Creating Folder in Subdirectories (A/B or A/B/C)

For nested folders, modify the request to include the path:

API Request:

PUT https://graph.microsoft.com/v1.0/drives/{drive-id}/root:/{parentPath}/FolderName
Content-Type: application/json
Authorization: Bearer {access-token}

{
  "folder": {},
  "@microsoft.graph.conflictBehavior": "fail"
}

Step 5: Handling Errors and Edge Cases

• Folder Already Exists: Use "@microsoft.graph.conflictBehavior": "replace" if you want to replace an existing folder.
• Invalid Path: Ensure that the parentPath exists before attempting to create a folder.
• Permissions Issue: Validate that the API token has the correct scope for SharePoint folder creation.

Detailed steps with screenshots:

Create Rest connection using microsoft graph:

Integration flow:

Configure trigger:

Request:

{ "parentpath" : "DriveName/FolderName", "foldername" : "New Folder Name", 

"site" : "Site Name" }

Response:

{ "status" : "Success", "url" : "<folder url returned from API response>", "Error_Message" : "<Error message information>" }

Get site id:

/sites/{tenant-name}.sharepoint.com:/sites/{site_name}

Get Drive Id:

/sites/{siteid}/drives

Add $filter

Xslt code:

<xsl:template name="tracking_var_3" xml:id="id_76"/>

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

    <nstrgmpr:execute xml:id="id_31">

        <nstrgmpr:TemplateParameters xml:id="id_32">

            <ns32:siteId xml:id="id_32">

                <xsl:value-of xml:id="id_33" select="$GetSiteID/nsmpr0:executeResponse/ns27:response-wrapper/ns27:id"/>

            </ns32:siteId>

        </nstrgmpr:TemplateParameters>

        <nstrgmpr:QueryParameters xml:id="id_47">

            <xsl:choose>

                <xsl:when test="contains(/nssrcmpr:execute/ns18:request-wrapper/ns18:parentpath, &quot;/&quot;)">

                    <ns32:_0x646c72_filter>

                        <xsl:value-of select="concat (&quot;name eq '&quot;, substring-before (/nssrcmpr:execute/ns18:request-wrapper/ns18:parentpath, &quot;/&quot; ), &quot;'&quot; )"/>

                    </ns32:_0x646c72_filter>

                </xsl:when>

                <xsl:otherwise xml:id="id_61">

                    <ns32:_0x646c72_filter xml:id="id_62">

                        <xsl:value-of xml:id="id_63" select="concat (&quot;name eq '&quot;, /nssrcmpr:execute/ns18:request-wrapper/ns18:parentpath, &quot;'&quot; )"/>

                    </ns32:_0x646c72_filter>

                </xsl:otherwise>

            </xsl:choose>

        </nstrgmpr:QueryParameters>

    </nstrgmpr:execute>

</xsl:template>

</xsl:stylesheet>

Create folder

/drives/{drive-id}/root{parentPath}/children

Request:
{
  "name": "Test3",
  "folder": {"name":"Test3"},
  "@microsoft.graph.conflictBehavior": "rename"
}

Xslt code:

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

trgmpr:execute xml:id="id 12">

<nstrgmpr: TemplateParameters xml:id="id_42">

<xsl:choose xml:id="id 119">

<xsl:when xml:id="id_120" test="contains (/nssrcmpr: execute/ns17: request-wrapper/ns17: parentpath, &quot;/&quot;)"> <ns40:driveid xml:id="id_ 121">

<xsl:value-of xml:id="id_122" select="$GetDriveID/nsmpro: executeResponse/ns33: response-wrapper/ns33: value [ns33: name=substring-before (/nssrcmpr: execute/ns17: request-wrapper/ns17: parentpath, &quot;/&quot;)]/ns33:id"/> </ns40: driveid>

</xsl:when>

<xsl:otherwise xml:id="id_123">

<ns40: drive id xml:id="id 124">

<xsl:value-of xml:id="id_125" select="$GetDriveID/nsmpre: executeResponse/ns33: response-wrapper/ns33: value [ns33: name=/nss rcmpr: execute/ns17: request-wrapper/ns17: parentpath]/ns33:id"/> </ns40: driveid> </xsl:otherwise>

</xsl:choose>

<xsl:choose xml:id="id_83">

<xsl:when xml:id="id_84" test="contains (/nssrcmpr: execute/ns17: request-wrapper/ns17: parentpath, &quot;/&quot;)">

<ns40:directory xml:id="id_45">

<xsl:value-of xml:id="id_46" select="concat (&quot;:/&quot;, substring-after (/nssrcmpr: execute/ns17: request-wrapper/ns17: parentpath, &quot;/&quot; ), &quot;:/&quot;)"/> </ns40:directory>

</xsl:when>

<xsl:otherwise xml:id="id_87">

<ns40:directory xml:id="id_45">

<xsl:value-of xml:id="id_89" select="&quot;/&quot;"/>

</ns40:directory>

</xsl:otherwise>

</xsl:choose>

</nstrgmpr:TemplateParameters>

<ns39:request-wrapper xml:id="id_47">

<ns39: name xml:id="id_48">

<xsl:value-of xml:id="id_49" select="/nssrcmpr: execute/ns17: request-wrapper/ns17: foldername"/>

</ns39:name>

<ns39:folder xml:id="id_50">

<ns 39: name xml:id="id_51">

<xsl:value-of xml:id="id_52" select="/nssrcmpr: execute/ns17: request-wrapper/ns17: foldername"/>

</ns39:name>

</ns39: folder>

<ns39: 8x617472_microsoft.graph.conflictBehavior xml:id="id_53">

<xsl:value-of xml:id="id_54" select="&quot; rename&quot;"/>

</ns 39: 0x617472_microsoft.graph.conflictBehavior>

</ns39:request-wrapper>

</nstrgmpr: execute>

</xsl:template>

Map response:

Xslt code:

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

<ns17: response-wrapper xml:id="id_52">

<xsl:choose>

<xsl:when test="$CreateSharePointFolder/nsmpr4: executeResponse/ns28: ConnectivityProperties/ns28: RestAPI/ns28:http.response.status = 201">

<ns17:status>

<xsl:value-of select="&quot; Success&quot;"/>

</ns17: status>

</xsl:when>

<xsl:otherwise xml:id="id_115">

<ns17:status xml:id="id 116">

<xsl:value-of xml:id="id_117" select="&quot; Error&quot;"/>

</ns17:status>

</xsl:otherwise>

</xsl:choose>

<ns17:url xml:id="id 126">

<xsl:if xml:id="id_125" test="$CreateSharePointFolder/nsmpr4: executeResponse/ns28: ConnectivityProperties/ns28: RestAPI/ns28:http.response.status = 201"> <xsl:value-of xml:id="id_127" select="$CreateSharePointFolder/nsmpr4: executeResponse/ns29: response-wrapper/ns29:webUrl"/>

</ns17:url>

</xsl:if>

<xsl:if xml:id="id_129" test="$CreateSharePointFolder/nsmpr4: executeResponse/ns28: ConnectivityProperties/ns28: RestAPI/ns28:http.response.status != 201"> <ns17: Error Message xml:id="id_130"> <xsl:value-of xml:id="id_131" select="$CreateSharePointFolder/nsmpr4: executeResponse/ns28: ConnectivityProperties/ns28: RestAPI/ns28:http.response.reason"/> </ns17: Error_Message>

</xsl:if>

</ns17: response-wrapper> 

</nstrgmpr: executeResponse>

</xsl:template>

Test:

Reference:

https://learn.microsoft.com/en-us/graph/api/driveitem-post-children?view=graph-rest-1.0&tabs=http

OIC - How to download file from sharepoint using Microsoft Graph API 1.0

Downloading Files from SharePoint Using Microsoft Graph API in Oracle Integration Cloud (OIC)

Use Case

In an Oracle Integration Cloud (OIC) implementation, there may be a requirement to download files stored in a SharePoint document library for further processing. Microsoft Graph API provides a secure and efficient way to fetch files from SharePoint Online.

Solution Steps

Prerequisites

• A registered Azure AD application with permissions to access SharePoint
• Client ID, Tenant ID, and Client Secret for authentication
• A SharePoint site with files stored in a document library
• Oracle Integration Cloud (OIC) with REST Adapter

Step 1: Configure Microsoft Azure App

1. Go to Azure Portal.
2. Register a new application under Azure Active Directory (AAD).
3. Assign API permissions:
   • Sites.Read.All or Sites.FullControl.All (Application permissions)
   • Files.Read.All for file access
4. Generate a Client Secret for authentication.

Step 2: Obtain an Access Token

Use OIC to fetch an access token from Microsoft Identity Platform using the following API:

Request:

POST https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token
Content-Type: application/x-www-form-urlencoded

grant_type=client_credentials
&client_id={client_id}
&client_secret={client_secret}
&scope=https://graph.microsoft.com/.default

Response:

{
  "access_token": "eyJ0eXAiOiJKV..."
}

Step 3: Download a File from SharePoint

Use Microsoft Graph API to fetch the file from the document library.

API Request:

GET https://graph.microsoft.com/v1.0/sites/{site-id}/drives/{drive-id}/items/{file-id}/content
Authorization: Bearer {access_token}

• {site-id}: Retrieve using https://graph.microsoft.com/v1.0/sites/{tenant-name}.sharepoint.com:/sites/{site-name}
• {drive-id}: Retrieve using https://graph.microsoft.com/v1.0/sites/{site-id}/drives
• {file-id}: Retrieve using https://graph.microsoft.com/v1.0/sites/{site-id}/drives/{drive-id}/root/children

Step 4: Implement in OIC

1. Create an OIC Integration
   • Use the REST Adapter to invoke the Microsoft Graph API.
2. Configure Authentication
   • Use OIC REST connections to pass the access token dynamically.
3. Download and Store File
• Store the response content in an FTP/SFTP location or pass it to another service.

Highlevel steps implemented:

1. User will feed site name, drivename, file name pattern to download the file from sharepoint
2. Get site id from site name
3. Get drives under the site id
4. Select the drive id for the drive name provided.
5. List the children files or folders under the drive id
6. Exclude the folders and select the file id for the file pattern
7. Download content stream reference for the file id.
8. Upload file content to object storage or other oprion as per requirement.

Detailed screenshots:

Create rest connection:

Connection url: https://graph.microsoft.com/v1.0

Provide client id , client secret, access token, scope and client authentication

Scope: https://graph.microsoft.com/.default

Configure trigger:

Request payload

{

  "ProcessingId": "",

  "SiteName": "",

  "DriveName": "",

  "FileNamePattern": "",

  "bucketName": "",

  "NameSpace": ""

}

Response payload:

{

  "Status": "WARNING/ERROR/SUCCESS",

  "File": [

    {

      "Names": "",

      "Message": "actual error"

    }

  ]

}

Get site id for site name

GET /{tenant}.sharepoint.com:/sites/{site-name}

Get drives under the site

/sites/{siteId}/drives

Add $filter query param and assign 

concat("name eq '",<driveName>,"'")

Assign drive id for the drive name

List drive files

/sites/{siteId}/drives/{driveId}/root/children

For each item

Exclude folders and select the file which match the pattern

Get content stream reference for the file:

/drives/{driveId}/items/{itemId}/content

Testing: