Oracle ICS, quering a database with parameter

In this post we will query database and will execute the query with the parameter provided by the rest interface.  This post require a multiple setup to be completed.

1. We should have rest integration with parameter  - Use this link to create. 

2. Database connection should be configured and created. - Use this link to create


Once above 2 configuration is completed, we can now move towards creating a integration with the database connectivity.


1.  Now  open the integration created in the Step 1.  Once integration open the click on the + Sigh in the integration flow between the map and first activity,  and choose the database connection. Once done it will open the database adapter configurations.  In this provide

     1. Name - Name of the database adapter.
     2. Description - Not mandatory.
     3. When we want to do with this database connection - I opt for select with table.

2.  Now select the schema and table table and also table name for the selection you can select multiple table and then you can import table, post selection. 

3. Now query is been built and you can use the expression builder to add and use parameter for the query. I have opt amt to and amt from in the query. 

4. Once done review the configurations and then click on save. 

5. Now map the query parameter, these query parameter is coming from the rest interface. Hence queryParam1 is been mapped to amount from and queryParam2 to amountTo.  Once done close it. This is how we provide query parameter value to query. 

6. Now last configuration is we need to map the result to output, for this we need to open the last map option and then need to map the node and then attribute under the node as given below. 

7. Now integration is ready for the testing, save the integration and come to the first page. switch the integration for active. Once integration is active, its not possible to make any change.  It will take some time and it will be ready to service.  If it will take some time manually refresh the page. 

 8. Now integration is ready for testing, use the URL coming for the testing. There is a requirement of the credentials, these credentials are same which is been given in point 1 during the configuration of rest service.

I have used postman to test the REST API, you can choose some other tool also for testing. 

REST Service Interface for ICS - Configuration for REST parameters.

Objective :

    In ICS we know REST is the preferred protocol due to the flexibility of message provided by the service and performance. This post talks about how to create a integration which can be invoked as a rest service.


REST Integration

    you need to create a rest connection as given in the previous post.  Once connection is created, prerequisite are ready and we can proceed with integration creation.


  1. Go to integration screen and click on create option. It will open a popup to create a integration based on the option available, for this usecase select "App Driven Orchestration" .  Please click on select.

 2. Now integration will ask for some mandatory parameter, please provide the name and package. Other options will either be filled based on the name.  Once done click on create option. Please remember cant change the name post this.

 3. This screen will open and now select the first activity and click on edit. This will open a popup for the configurations.

 4. First screen will ask you basic information like name  and then second line will ask about the operation this rest service will provide. You can choose get, post, patch, merge. Also please select for the parameter for request and response based on the requirement.

5. Now provide the parameter for the query, I have provided 2 parameter. You can provide single of multiple based on your requirement. 

6. Now provide the response parameter, response parameter will be sent to caller post execution of the ICS process.

7. Now everything is done review things been configure here. once done then you are finished with a integration need to be invoked with 2 parameter. If you have changed then your parameter will be different.

8. OIC require a set of tracking parameters, to provide one tracking parameter is mandatory in ICS.  Click on the tracking.

9. Now from the available parameters, we can choose the parameter which we want to track for the integration, once done close the window.

REST Service - Creating Reusable connection for inbound services (ICS).

Objective:

 In this post we are configuring a re-usable rest connection, which can be used by multiple OIC services to invoke integrations. 

REST 

1.  First we need to create a rest connection. To do this first go to connections and choose to create a new. Once screen is visible select for REST adapter and select it.

    2. Now give a name I have given a name "ORCU16_E001_REST_INBOUND and choose the role trigger and Invoke. You can choose any other name based on your wish, once done click on create button.

3. Now provide the information for configuration, this page will provide you all options of filed to configure. In first you need to provide connectivity.

4. Now provide the connection URL and choose other details as given in the picture, URL you should provide the host name coming in your browser.

 5. Now provide the connection information, this information will be required to trigger the rest service. Once its done save the connection information.

6. Now test the connection, once tested, it will be available to use in any integrations.

Now close the screen as we need to move to integration creation screen. 

This kind of connection as its inbound can be used in multiple integrations please click on Next to create integration and connection. 

Creating REST Connection in Oracle integration Cloud Service (ICS)

• REST Connection Supports for uploading complex XML schema definitions as a zipped archive to define data definitions for XML content during REST Adapter configuration. See Complex Schema Support.
• REST Connection Support for uploading sample XML documents to define data definitions for XML content during REST Adapter configuration. The following XML documents are supported for schema generation:

  • XML with no namespace.
  • XML with a homogenous namespace.
  • XML files up to 3 MB in size.
• Supports configuration of the following:
  • Relative resource URI.
  • Support for HTTP methods GET, PUT, POST, DELETE, and PATCH.
  • Template and query parameters.
  • Support for a request/response payload.
    • Support for JSON, XML, binary (inline and unstructured), and URL-form-encoded payloads.
    • Support for homogenous JSON arrays including top-level arrays.
    • Support for multidimensional JSON arrays (see Homogenous Multidimensional Array Support in JSON Documents).
  • REST APIs exposed using the REST Adapterare secured using Basic Authentication, OAuth token-based authentication, and JWT-based authentication.
  • REST APIs implement the HTTPS protocol, thereby enforcing all incoming requests to have transport level security.
  • REST APIs exposed using the REST Adapter are protected using Basic Authentication and OAuth token-based authentication.

• Login to Integration Cloud Service and click on connections. Once it start displaying all connections. Click on create button. 

• Search for REST adapter and click on select button. It will open the screen to provide the different parameter for REST. 

• Provide Connection Name, and Role I have opt for trigger and invoke both. Once done click on create button. 

Configurations 

• In the next screen provide the information about the connection, I have used a open REST API available. If you are just doing to test or learn use the same, or can use any api available openly. Please use following page to check API available. 

Security 

• Please provide any user name password required to login to this API. I am using open API hence there is no security configuration required. Hence I used "No Security Policy". 

Test Connection. 

   Now everything is done, please save and test the connection.  Now connection is ready to be used.

Configuring an FTP Adapter in Oracle Integration Cloud Service (ICS)

Oracle FTP Server provide a long list of the functionalities to upload the file to any available server over the internet.  Its very much similar to ftp adapter in the Oracle SOA suite. But its different from File Adapter because file adapter was capable of working on the file on same server, where soa suite is running.  Here are some features of the file adapter. 

• It supports  upload of an XSD file without a target namespace. it add a surrogate namespace is added to the XSD file that all messages then use:

  http://xmlns.oracle.com/cloud/adapter/nxsd/surrogate

• Transfers (reads and writes) files to any publicly accessible server in either binary or ASCII format. The FTP Adapter can connect to FTP and sFTP servers that are publicly available on the internet. The adapter cannot be used to connect to FTP and sFTP servers behind company firewalls. 
• Streaming support for transferring large files. Which mean your file will be divided into chunks and trasferred. 
• Enables the integration developer to build an orchestration pattern-based flow for retrieving files from remote FTP and sFTP servers as well as for uploading the files onto remote FTP / sFTP servers.Oracle Integration provides the capability for scheduling the time and the frequency at which these integration flows will run.
• Supports a synchronous one-way request message exchange pattern. There is no response from the FTP Adapter.
• Enables you to create a schema file format to use for the files to transfer. The schema can be created (sampled) from either a comma-separated value (CSV) file or from an existing schema file. Supported delimiters in the file are single spaces, commas, semicolons, or tabs.
• Enables the integration developer to encrypt a file that is being uploaded to remote FTP / sFTP servers using Pretty Good Privacy (PGP) cryptography.
• Enables the integration developer to decrypt a file that is being read or downloaded from a remote FTP / sFTP server using Pretty Good Privacy (PGP) cryptography.
• Supports explicit FTP over SSL. Implicit FTP over SSL is not supported.
• Supports FTP over OpenSSL through the upload of a Public-Key Cryptography Standard (PKCS) format certificate. The FTP Adapter supports both sFTP and FTP over SSL. When using FTP over SSL, note that only explicit TLS/SSL encryption is supported.
• Supports self-diagnosing connectivity issues that may occur when integrating with external sFTP servers.

Configuring an FTP Server 

1. Login to your OIC server and now clock on the connections, once this screen visible, click on the create. 

2. Now it open up the connection crate page, in the adapter select ftp adapter and provide the connection name like I have provided ORCU16_E001_FTP_ADP

3. Provide the connection name like I have provided ORCU16_E001_FTP_ADP, keep the Trigger and invoke to as it is.

     

It will open up the page to configure the connection parameter. Here thing to remember it connection is normally a reusable thing, hence we are just providing generic parameter not all the parameter. 

Connection Properties

   Provide the information about your server and its location and where does it exists, port information and also if its sftp. If you want to connect using any keys then also options are there. Minimum information required is 

1. Host 
2. Port
3. SFTP - Yes or No

Close once all the information been key in.

Credentials 

 Last part of the configurations is credentials mapping, provide minimum is username for the ftp server and password. If using any other way then other details also required.

Once all the information is done, its ready for testing.  

Please save and test it, and connection is ready to be used.