soalicious

Friday, August 11, 2023

OIC - add custom HTTP headers invoking to external SOAP service

Usecase: Here, we will show you how to send or configure a custom HTTP header while invoking a External SOAP service.

The following highlighted Action header need to pass:

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005...>

<s:Header>

<a:Action s:mustUnderstand="1">http://www.testsoftwate.com/dataservices/bidata/2/testdataservice</a:Action>

<a:To s:mustUnderstand="1">http://www.servivehost/services/testdataservice</a:To>

</s:Header>

<s:Body>

...

</s:Body>

</s:Envelope>

Implementation Steps:

Step1: Create header schema for the custom SOAP headers as below:

 
<xsd:schema elementFormDefault="qualified"
targetNamespace="http://www.w3.org/2005/08/addressing"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <xsd:element name="Action" type="xsd:string"/>
</xsd:schema>

Step2: save this as ActionHeader.xsd and checked the custom header option for request and upload the file to invoke configure adapter.

Step3: Map the Header value in the mapper.

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/soap-adapter/configure-custom-soap-headers-soap-adapter.html#GUID-D21009B4-1E69-4D57-3EA892EF31F3

Friday, August 4, 2023

Excel links

Excel links:

OIC XSD - How to validate if any field is empty while reading the file | add restriction to a schema field

Usecase: Here, we are reading a file. During reading, we need to validate field C1 if it is empty . If the any of the field C1 is empty, we have to fail it from further processing.

Steps:

Add attribute nxsd:fieldValidation="true" to the schema.
Add the restriction minLength value="1" block to the field to validate.

Schema used:

<?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:fieldValidation="true"

nxsd:encoding="US-ASCII">

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

<xsd:complexType>

<xsd:sequence>

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

<xsd:complexType>

<xsd:sequence>

<xsd:element name="C1" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy=""">

<xsd:simpleType>

<xsd:restriction base="xsd:string">

<xsd:minLength value="1"/>

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

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

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Thursday, August 3, 2023

Excel - How to sum of all positve numbers or negative numbers from a range of values

Usecase: we have a range of numbers in excel file, we have to sum all positive numbers and negative numbers separately from that range.

Steps: Use SUMIF() function to achieve this requirment:

To sum positive numbers

=SUMIF(G2:G366,"<0")

Output: -2590

To sum positive numbers

=SUMIF(G2:G366,">0")

Output: 2590

Friday, July 21, 2023

OIC - How to use DB sequence while insert or merge to database table in Oracle Integration.

Usecase: We will develop an integration with on premise oracle database and insert employee data into database table using a sequence as a unique id as a Primary key.

Logic steps:

Create a sequence in the database.
Create a Rest Trigger and DB connection.
Configure the rest adapter to get the eomployees data.
Configure the database adpater >> select operation as "Perform an Operation on a table" >> select Insert or Merge(this 2 only valid) >> select Advanced Options Edit >> select the created Sequence to fetch the sequence id and insert into the table.

This is only valid for the insert and merge operarions.

Implementation steps:

Notes:

The insert or merge table should have a primary key field that will be assigned from the sequence.
The adapter generates sequence numbers in a batch of 50. Configure sequence in increments of 50. This issue only applies to the oracle database adapter.

Example of a sequence created:

Create Sequence SD_Sequence Start with 1 increment by 50 nominvalue nomaxvalue nocycle nocache;

If we dont follow the notes >> 2nd step (increment by 50 in sequence), we will get below error:

The sequence named [SD_Squence] is setup incorrectly. Its increment does not match its pre-allocaton size.

Wednesday, July 19, 2023

OIC - How to access Database sequence value in Oracle Integration

Usecase: Here, I will show you how to get the database sequence value in Oracle Integration.

Logic steps:

Create a sequence as required in the database.
Create a database connection
In the integration canvas , drag and drop the db adapter and configure as follow:

Choose operation as "Run a SQL Statment"
Provide sql query as select OIC_USER."Invoice_SEQ".nextval from dual and validate

Implementation steps with screenshots:

For create sequence , you can follow below blog:

https://soalicious.blogspot.com/2020/06/database-create-sequence.html

Tuesday, July 18, 2023

OIC - Exposing rest service supporting a file as attachment and JSON request using Oracle Integrtion Cloud

Usecase: Here, we will expose a rest service which take a file as attachment and also JSON data as request payload using Oracle Integration Cloud.

While configuring the rest trigger, select Request is multipart with payload option for sending attachment and keep 2nd option as unchecked to send request payload as required.

Implementation steps: