Monday, 14 January 2019

Redaction policy in Oracle API Cloud Service

Oracle API CS offers an inbuilt feature to limit or remove the certain fields and headers which is the part of request and response payload. This policy is called Redaction policy. The policy can be used in both request and response pipeline.

In the request pipeline, headers, queries and payload content can be controlled before the backend service is invoked. Similarly, in the response pipeline, headers, queries and payload content can be controlled before the response sent to the consumer.

Let's see how to apply the redaction policy in request and response pipeline separately.

To complete this article, we'll assume one service is already configured in the Oracle API CS. If not, kindly follow the blog

Let suppose, one API configured is in the API CS with the below request and response:

Request URL:

https://189.11.11.13:8443/redact

HTTP Verb: GET

Parameter / Header

Parameter / Header
Description
empi
This is of query parameter of string type
key
This is custom request header of string type

Response:

{
    "Employee": {
        "Name": "Ankur",
        "EmployeeID": "122",
        "key": "12"
    }
}


For the particular API, We'll exclude the request header (key) and Employee.Name node from the response one by one using Redaction policy.

Configure the Redaction policy in the Request pipeline
  • Click the API implementation tab of the API
  • Move to the Request pipeline
  • In the Available Policies region, expand Interface Management, hover over Redaction, and then click Apply
  • Enter below information and click the Next button
    • Your Policy Name: Enter the policy name of your choice
    • Comments: Enter comment
    • Place after the following policy: Select the policy after which this policy is placed
  • Select Exclude from the Headers section and enter key in the Header name then click Apply button
  • Click Save to save the changes and redeploy the API
  • Hit the API, pass custom header(key) and see the response, key value will be blank in response as request key header is directly mapped with the key of the response payload

Configure the Redaction policy in the Response pipeline
  • Click the API implementation tab of the API
  • Move to the Response pipeline
  • In the Available Policies region, expand Interface Management, hover over Redaction, and then click Apply
  • Enter below information and click the Next button
    • Your Policy Name: Enter the policy name of your choice
    • Comments: Enter comment
    • Place after the following policy: Select the policy after which this policy is placed

  • Select Exclude from the Field section and enter Employee.Name in the Field name then click Apply button
  • Click Save to save the changes and redeploy the API
  • Hit the API and see the Employee.Name should not come as part of the response

Saturday, 5 January 2019

Oracle Integration Cloud REST API to get the Integration Statistics

Oracle Integration cloud provides a rich monitoring dashboard to see the Integration statistics. But there is a limitation that statistics can't be downloaded from the dashboard in any format (XLS, pdf etc.). To overcome this limitation Oracle Integration Cloud provides RESTFul APIs which provides the statistics of each and every running integration.


In this article, we'll demonstrate how to use Integration Cloud REST API to retrieve monitoring integrations as well as how to generate excel sheet using java code.

Below is the REST API details which is used to retrieve monitoring integrations:

URL:

For ICS:

/icsapis/v2/monitoring/integrations

For OIC / AIC

/ic/api/integration/v1/monitoring/integrations

HTTP Verb: GET

Authentication

Basic Authentication is required
Query Parameter

Parameter Name
Optional
Filter Parameters
q
Yes
timewindow: "1h","6h","1d","2d","3d","RETENTIONPERIOD" For examples: {timewindow: '3d'}

Response:

{
    "items": [
        {
            "code": "TEST_API",
            "id": "TESTP_API|01.00.0000",
            "links": [
                {
                    "href": "https://testinstance.integration.us2.oraclecloud.com:443/icsapis/v2/monitoring/integrations/TEST_API%7C01.00.0000",
                    "rel": "self"
                },
                {
                    "href": "https://testinstance.integration.us2.oraclecloud.com:443/icsapis/v2/monitoring/integrations/TEST_API%7C01.00.0000",
                    "rel": "canonical"
                },
                {
                    "href": "https://testinstance.integration.us2.oraclecloud.com:443/icsapis/v2/integrations/TEST_API%7C01.00.0000",
                    "rel": "integration"
                }
            ],
            "name": "TEST_API",
            "noOfErrors": 0,
            "noOfMsgsProcessed": 1,
            "noOfMsgsReceived": 1,
            "noOfSuccess": 1,
            "successRate": 100,
            "version": "01.00.0000"
        },
        {
            "code": "PRO_CREATION",
            "id": "PRO_CREATION|04.00.0000",
            "links": [
                {
                    "href": "https://testinstance.integration.us2.oraclecloud.com:443/icsapis/v2/monitoring/integrations/PRO_CREATION%7C04.00.0000",
                    "rel": "self"
                },
                {
                    "href": "https://testinstance.integration.us2.oraclecloud.com:443/icsapis/v2/monitoring/integrations/PRO_CREATION%7C04.00.0000",
                    "rel": "canonical"
                },
                {
                    "href": "https://testinstance.integration.us2.oraclecloud.com:443/icsapis/v2/integrations/PRO_CREATION%7C04.00.0000",
                    "rel": "integration"
                }
            ],
            "name": "PRO_Creation",
            "noOfErrors": 2,
            "noOfMsgsProcessed": 35,
            "noOfMsgsReceived": 35,
            "noOfSuccess": 33,
            "successRate": 94,
            "version": "04.00.0000"
        }
    ]
}

Below is the screenshot of POSTMAN where we executed the REST APIs to get data with and without query parameter. If the query parameter will not be passed then the data will come from the first day.



This is how the REST API can be used to get the monitoring instance. Let's come on how to generate excel sheet based on the data received from API using java code.

Below is the snapshot of the Excel sheet which will be generated using JAVA code


Below is the structure of JAVA code developed in JDeveloper


Download the JAVA code which is used to generate an Excel sheet. Import the code into JDeveloper, update the Constants.java file and run CreateReport.java file directly. An excel file will be generated in the configured folder.

Wednesday, 19 December 2018

Upgrade on premise ICS agent

In one of my article, I have showcased how to install the on-premise agent on the Linux machine. This article will show you how to upgrade the existing on-premise agent.

In this blog, we are upgrading the agent from 18.1.3.0.0 to 18.4.3.0.1

Below are the steps to upgrade the on-premise agent
  • Login into the ICS console and move to the Designer -> Agents page
  • Click on the Download -> Connectivity Agent
  • A file will be downloaded with name ics_conn_agent_installer_181022.0000.1074.zip
  •  Move the file on the server where the agent in up and running. In our case, we have moved the file to /home/AgentPatch/
  • Unzip the installer and notice below files:
    • cloud-connectivity-agent-installer.bsx
    • cloud-connectivity-agent-patcher.zip
  • Unzip the cloud-connectivity-agent-patcher.zip and notice below files
    • cloud-connectivity-agent-ics.properties
    • cloud-connectivity-agent-patcher.jar
    • cloud-connectivity-agent-patcher.sh
    • cloud-connectivity-agent-patching-scripts.zip
    • apply-patch.jar
    • apply-patch.sh
    • updateicscredentials.sh
    • cloud-connectivity-agent-checksum.properties
  • Set the JAVA_HOME and PATH parameters. Only Oracle JDK version 1.7 and 1.8 are supported. Any other JDK such as Open JDK does not work.
  • Notice the file cloud-connectivity-agent-patcher.sh. This is the file which needs to run to upgrade the agent with some parameters as below:
Parameter
Is Mandatory
Description
-agentInstallDir
Yes
The installation Agent directory in which agenthome resides
–icsUn
Yes
ICS user name
–icsPwd
Yes
ICS password
-patchZipFile
No
The complete path of the zip file in which BSX file is available. If the parameter not specified, a smaller-sized version of the ZIP file for exclusively upgrading the on-premises agent is automatically downloaded from Oracle Integration Cloud Service.

Run below command:

./cloud-connectivity-agent-patcher.sh -agentInstallDir=/home/ICS_AGENT -icsUn=ankurjain.jain26@gmak.com -icsPwd=Welcome@123 -patchZipFile=/home/AgentPatch/ics_conn_agent_installer_181022.0000.1074.zip

Here: /home/ICS_AGENT is the existing directory of ICS agent

Once the command is fired the patching process will begin. The security certificate is downloaded if not already available and added to the keystore. Go the Agent Group page during patching and notice different statues(Upgrade Pending, Restart Pending, and Up to Date) as shown in the below screenshot




The on-premises agent is automatically restarted after the patch is applied. Once the patch ZIP file is available for extraction (either through the -patchZipFile parameter or through download from Oracle Integration Cloud Service), the remaining process takes around 10 to 15 minutes to complete. This time period includes restarting the on-premise agent. Once the agent is upgraded successfully, go the Agent groups page and see the upgraded version


That's how the on-premise agent can be upgraded from an older version to the newer version.

Wednesday, 5 December 2018

Change connection in the Integrations: Oracle Integration Cloud

There is the situation where we are required to replace the connection in the integrations. Integration cloud doesn't allow to change the connection from the integration canvas directly. So in this blog, we'll demonstrate two ways to solve this problem which will help us to easily switch the connection in the integrations.

Before we proceed further, let me explain the use case where we may require to change the connection in the integrations:
  • Let's suppose we have 10 integrations which are using the single DB connection and the name of the connection is DB_Conn. Considering, in the DB_Conn, we had configured the standard DB schema called apps. Later we realize the 5 integrations should not use standard schema to compile the package or procedures and should use some custom schema (custom_user). Then, in that case, we will have to create one more DB connection with the different name and configure custom_user and then use the DB connection in these 5 integrations.
  • We want to clone the integration and in the cloned integration wanted to use different connection
  • We are migrating the integration which uses one REST connection with name REST_Sample but we have REST connection on the different instance with a different name called REST_Conn
Below limitation need to keep in mind before we replace the connection
  • The integration should not be in locked or activate state
  • Only the connection of the same adapter type can be replaced. For example, if we have an integration using the REST adapter then we can't replace the connection of the SOAP adapter type.
There are two ways to replace the connection in the integrations.
  1. Export the integration and change the connection name in the project.xml file directly and then import
  2. Use Integration Cloud REST APIs to replace the connection
Let's get started and achieve both the ways to replace connection.

For this use case, we are considering below:
  • We have two connection of the REST adapter type with below names and having the role as the trigger
    • REST_Sample
    • REST_Conn
  • Created an integration with name Sample_Integration which initially uses the connection REST_Sample. You can see the connection name by hovering on the adapter

Approach-1 ) Export the integration, replace the connection name in the project.xml and then import back
  • Export the integration by clicking the hamburger menu parallel to the integration
  • Open the .iar file directly in the 7-zip
  • Navigate to the \icspackage\project\SAMPLE_INTEGRATION_01.00.0000\PROJECT-INF\ directory
  • Open the project.xml file and see the REST_SAMPLE. REST_SAMPLE is the REST adapter connection identifier
  • Replace the REST_SAMPLE with REST_CONN  which is another REST adapter connection identifier
  • Save the file and import the integration using the Import button
  • Hover the mouse over the connection and see the integration should show REST_Conn

Approach-2) Using Oracle Integration Cloud REST API
  • First, we will get the details of the Integration (Sample_Integration) using the GET REST API using POSTMAN tool
  • Notice the "dependencies" element in the response. It's showing the connection (REST_CONN) used in the Integration
  • Now, we'll use the update REST APIs to update the connection. Here we'll update the REST_CONN with REST_SAMPLE. Below are the details of update API
URL: /ic/api/integration/v1/integrations/{id}

Headers:

Authorization: Basic EncodeBase64({username}:{password})
X-HTTP-Method-Override: PATCH
Content-Type: application/json

HTTP Verb: POST

Body:

{
"dependencies": {
        "connections": [
            {
                "id": "REST_SAMPLE"
            }
        ]
    }
 }

  • Go back to the Integration list page,  hover the mouse over the connection and see the integration should the "REST_Sample" connection

Monday, 3 December 2018

Poll File from Agent server leveraging File Adapter: Integration Cloud

Oracle Integration Cloud provides a File adapter to deal with the files which reside on the local server.

File adapter allows following operations on an on-premise location, using Integration Cloud connectivity agent:
  • File polling
  • Write 
The in and out directories should be locally accessible from the server where ICS connectivity agent is installed.

Refer the blog to understand the difference between File and FTP adapter

Use Case: For the particular article, we'll leverage the File adapter to poll the file from the server where connectivity agent is installed and transfer the file on FTP server.

Let's get started and see how to achieve the use case
  • Create an Orchestration process in Integration Cloud Service with name PollFile
  • Drop File connection as a trigger point
  • Enter below information and click the Next button
    • What do you want to call your endpoint? PollFileFromServer
    • Do you want to define a schema for this endpoint? No
  • Enter below information and click the Next button
Information
Description
Value
Specify an Input Directory
Enter the input directory for File reading
/home/PollDir
Specify a File Name Pattern
Specify the input file name pattern
*.*
Maximum Files
Specifies the number of files to be processed in a single poll operation
25
Polling Frequency
Specifies the polling operation frequency
60
Processing Delay
Specifies the polling operation frequency delay
5
Delete Files After Successful Reading
When selected, files are deleted after they are successfully read
Checked

  • Select Done button

  • Drop the FTP connection
  • Enter the endpoint name of your choice and click the Next button
  • Enter below information and click the Next button
    • Select Operation: Write File
    • Select a Transfer Mode: ASCII
    • Specify an Output Directory: /home/opc/tempfiles
    • Specify a File Name Pattern: *
    • Enable PGP security: No
  • Enter below information and click the Next button
    • Select the Do you want to define a schema for the endpoint radio button
    • Select the Select an existing schema from the file system radio button
  • Create a xsd file with below element and browse
<?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>

Note: An opaqueElement element is created of type base64Binary because while polling the file from the File server, it returns the element of base64Binary only.

  • Click the Done button
  • Edit the mapper and map below fields:
    • fileName -> fileName
    • element0 -> opaqueElement

  • Configuration is completed now. Close and Activate the integration
  • Keep three files on agent server at /home/PollDir directory
  • Open Integration Cloud monitoring dashboard and notice three instances should be created, one for each file

  • Open the FTP /home/opc/tempfiles directory and see three files should be there

Saturday, 1 December 2018

Implement Pagination in REST Service: Integration Cloud

There are situations in which we need to fetch a large number of records from the on-premise application in the Integration Cloud. For example, there are 100k+ records resides in the on-premise database table which we need to fetch and expose them into the REST service response.

In this particular scenario, we are considering the database is installed behind the firewall. So to communicate with the on-premise application, connectivity agent is required.

Connectivity agent has some limitation in which agent can parse payload of 10MB only for a single request. Exceeding the limit may cause stack overflow error in the agent which may bring the agent down.

So to handle a large volume of data from the on-premise application, we have to keep this limitation in mind and implement the solution accordingly.

There might be multiple ways to handle such a situation. Here I'm gonna to handle this situation by implementing the pagination into the REST service which will fetch the data into the chunk.

To complete this article we should have below
  • Oracle Database 12c
  • Oracle Integration Cloud instance
  • Connectivity agent installed
Let's get started with step by step. Before we move forward, below must be the object which should be compiled in the Oracle Database
  • A table with name test_table containing below columns
    • FIRST_NAME
    • LAST_NAME
    • PERSONAL_EMAIL_ADDRESS
    • EMPLOYEE_NUMBER
  • A package with a single procedure. Below are the package specification and body script
Package specification

create or replace PACKAGE  TEST_PAGINATION_PKG
AS
PROCEDURE TEST_PAGINATION_PROC(  I_PAGE IN NUMBER,
I_LIMIT IN NUMBER,
I_PAGE_SIZE OUT NUMBER,
P_DATA OUT SYS_REFCURSOR);
END;

Package body

CREATE  OR  replace package body TEST_PAGINATION_PKG AS PROCEDURE TEST_PAGINATION_PROC(i_page IN number , i_limit IN number, i_page_size out number, p_data out sys_refcursor) AS count1 number;BEGIN
  SELECT Count(*)
  INTO   count1
  FROM   test_table ;   
  I_PAGE_SIZE:=ceil(count1/i_limit);
  OPEN p_data for
  SELECT   *  FROM TEST_TABLE
  ORDER BY employee_number offset nvl((i_page-1),0)*i_limit rows
  FETCH next i_limit rows only;
END;END;

The procedure takes two input parameter:
  • i_page IN: The parameter specifies which page number data we are looking for
  • i_limit: The parameter specifies the number of record set we want to limit in a single run
And two output parameter:
  • i_page_size: The output parameter gives the total number of pages depending on the limit we set in the i_limit input parameter. For example, if TEST_TABLE contains 150 records and we wanted to limit 10 records at a time. So, in that case, the procedure will return the value of i_page_size as 15. 
  • p_data: Contains the record set
Let's see how to achieve the pagination and get records in chunks
  • Create an Orchestration process in Integration Cloud Service with name Pagination_Int
  • Drop a REST adapter as a Trigger End point
  • Configure the below properties and click the Next button
    • What do you want to call your endpoint: GetPaginatedData
    • What is the endpoint's relative resource URI?: /employees
    • What action do you want to perform on this endpoint: GET
    • Check the checkbox Add and review parameter for this endpoint
    • Check the checkbox Configure this endpoint to receive the response
  • Add a new parameter with the name page of type integer and click the Next button
  • Select JSON Sample and then click the <<< inline >>> link, then enter below sample json
{
   "Employees":[
      {
         "FirstName":"",
         "LastName":"",
         "EmailAddress":""
      },
      {
         "FirstName":"",
         "LastName":"",
         "EmailAddress":""
      }
   ],
   "PageSize":1,
   "Limit":1,
   "Page":1
}

  • Click the Done button
  • Drop database adapter just above the mapper
  • Enter the endpoint name as GetData and select Invoke a Stored Procedure from the operation drop down
  • Select the below information and click the Next button
    • Select Schema: Select the schema in which package is compiled. In our case package is compiled under apps schema so selected the apps schema
    • Select Package: Select package name as TEST_PAGINATION_PKG
    • Select Procedure: Select procedure as TEST_PAGINATION_PROC
  • Click Done button
  • Click edit icon of GetData mapper
  • Map the request parameter page to I_PAGE and set the value of I_LIMIT to 100. For the instance, we would like to fetch 100 records at a time that's the reason we set the value of I_LIMIT to 100
  • Edit the GetPaginatedData mapper and map the response as below
    • Drop I_PAGE_SIZE onto PageSize
    • Set Limit to 100
    • Drop  page onto Page
    • Map FirstName, LastName, and EmailAddress
  • The configuration is completed. Save the integration, close and Activate.
  • See the number of records into the TEST_TABLE. We have 999 records in the table.
  • Let's try to hit the service from POSTMAN
HIT-1: Set the page=1 and hit the run button. Since the total number of records in the DB is 999 and we set the limit size to 100. So the total number of pages would be 999/100 = 10 in round figure. Notice the PageSize and Limit values


HIT-2: Set the page=2 and hit the run button


That's the way we can achieve pagination in REST services and handle he large number of records of on-premise application.