Saturday, 28 April 2018

Create an Oracle Database Cloud Service Instance

This article is dedicatedly written to show all the steps which are used to create Oracle Database Cloud Service instance.

Below are simple steps which will help to create Oracle DB instance on Cloud
  • Login to my services console
  • In My Services Dashboard, click Services ->  Database
  • Click on Create Instance button
  • On the Instance page, provide information as below and click Next button
    • Instance Name: Any meaningful name for the instance
    • Description (Optional): A description of the new database deployment which shows why this instance been created
    • Notification Email (Optional): Enter An email address where the updates of newly created DB instance will be sent
    • Region: This would be available only if the identity domain is enabled for regions. We can choose No Preference to let Oracle Database Cloud Service choose an Oracle Cloud Infrastructure Classic region for you
    • Tags: This identifies the instance type (Development, Test, QA, PROD etc..)
    • Bring You Own License: Available only in accounts that use the Universal Credits payment model. Leave this option unselected to use your Oracle Cloud account
    • Software Release:  Select any one of them from the available release:
      • Oracle Database 11g Release 2
      • Oracle Database 12c Release 1
      • Oracle Database 12c Release 2
      • Oracle Database 18c
    • Software Edition:  Select any one of them from the available software package for the new deployment. Please select carefully as it impacts the cost
      • Standard Edition
      • Enterprise Edition
      • Enterprise Edition - High Performance
      • Enterprise Edition - Extreme Performance
    • Database Type: The type of deployment to create. This option varies depending upon the Software Edition selection
      • Select Single Instance
      • Database Clustering with RAC
      • Single Instance for Database Guard Standby
      • Database Guard Standby for Hybrid DR
      • Database Clustering with RAC and Database Guard Standby
  • On the Next page screen, configure the Database Configuration section by providing information about the Oracle Database we want to create
    • DB Name (SID): Database instance name
    • PDB Name: PDB is only for Oracle Database 12c. Enter the pluggable database (PDB).
    • Administration Password: The password for the database administrative users
    • Usable Database Storage (GB): The amount of storage in GB for actual database data.
    • Total Data File Storage (GB): The computed amount of storage in GB that will be allocated to the deployment, including space for operating system and product binaries, supporting files, database data and configuration files, and so on
    • Compute Shape: The number of Oracle Compute Units (OCPUs) and amount of memory (RAM) for each compute node of the new database deployment.
    • SSH Public Key: The SSH public key would be used for authentication when using an SSH client (putty, WinScp etc.) to connect to a compute node that is associated with the database deployment. Click Edit to specify the public key. We can upload a file containing the public key value, paste in the value of a public key, or create a system-generated key pair
    • Use High Performance Storage: Available only if we have a metered subscription
    • Advanced Settings: Listener Port: Specify DB listener port: Default is 1521
    • Advanced Settings: Timezone: Timezone of the new DB deployment
    • Advanced Settings: Character Set: The database character set for the database.
    • Advanced Settings: National Character Set: The national character set for the database.
    • Advanced Settings: Enable Oracle GoldenGate: Configures the database for use as the replication database of an Oracle GoldenGate Cloud Service instance
  • On the same page, complete the Backup and Recovery Configuration section
    • Backup Destination: This controls how backups would be configured:
      • Both Cloud Storage and Local Storage: Backups are configured to be created automatically and stored both on local storage and on cloud storage. If this choice is selected, the Cloud Storage Container, User Name, and Password fields will be displayed.
      • Cloud Storage Only: Backups are configured to be created automatically and stored in cloud storage. If this choice is selected, the Cloud Storage Container, User Name, and Password fields are displayed.
      • None: Backups are not configured for the deployment.
    • Cloud Storage Container: The location where backups to cloud storage are to be stored
    • Cloud Storage User Name: A user with read/write (and creation, if necessary) access to the location specified in Cloud Storage Container
    • Cloud Storage Password: Enter the password of the user specified in Cloud Storage User Name
    • Create Cloud Storage Container: Create Oracle Cloud Infrastructure Object Storage Classic container as part of the database deployment creation
    • Total Estimated Monthly Storage: Storage for data files and backups
    • Create Instance from Existing Backup: Select 'Yes' if you want the new database populated, or “instantiated”, from the data stored in the Database Backup Cloud Service backup. Let the default option 'No'
  • On the confirm page, review the information listed. If satisfied with the information, click Create.

Once click on Create button, it will take approximately 30 minutes to configure the instance. Within 30 minutes, we will have a fully functional 12c database in the cloud with backup/recovery configured, ssh enabled, APEX installed, a specially created DBaaS monitor, and more.

In the next blog, see how to connect Database instance using SQL developer.

Sunday, 22 April 2018

Fitter email Message of a Folder using Microsoft Email adapter in Integration Cloud Service

This blog is the extension of the blog in which we have showcased how to read messages of a specific folder of the Microsoft account using MS email adapter in Oracle ICS.

ICS doesn't allow to put any request parameter while configuring the MS email adapter in the request. However, If we see the Microsoft REST Service documentation, this allows to put request parameters (search, filter, select, order by, top etc..) in the REST APIs.

Let's do some tweak in the Integration and put the request parameter (filter) by editing the  WSDL & JCA files but exporting the Integration

Export the Integration that we created in the previous blog.

Unjar the file using some utility like 7-zip. Once unzipped the Integration, edit two files:
  • ReadEmails_REQUEST.WSDL
  • ReadEmails_REQUEST.jca

Path of the files  would be \READ_EMAILS_01.00.0000\icspackage\project\READ_EMAILS_01.00.0000\resources\application_15\inbound_16\resourcegroup_17

Edit the ReadEmails_REQUEST.WSDL first. Notice the below-highlighted parameter.  


<xs:complexType name="Get-Folder-Messages_GETType">
            <xs:sequence>
               <xs:element maxOccurs="1" minOccurs="0" type="TemplateParameters" name="TemplateParameters" />
            </xs:sequence>
         </xs:complexType>
         <xs:complexType name="TemplateParameters">
            <xs:annotation>
               <xs:appinfo />
            </xs:annotation>
            <xs:sequence>
               <xs:element name="folder_id" type="xs:string" minOccurs="1" maxOccurs="1" />
            </xs:sequence>
         </xs:complexType>

While configuring the MS email adapter, Only Template parameter with the name folder_id got added in the WSDL file. Let's add QueryParameters also in the WSDL file. Replace the above section with below:

<xs:complexType name="Get-Folder-Messages_GETType">

            <xs:sequence>

               <xs:element maxOccurs="1" minOccurs="0" type="TemplateParameters" name="TemplateParameters" />

              <xs:element maxOccurs="1" minOccurs="0" type="QueryParameters" name="QueryParameters" />

            </xs:sequence>

         </xs:complexType>

         <xs:complexType name="TemplateParameters">

            <xs:annotation>

               <xs:appinfo />

            </xs:annotation>

            <xs:sequence>

               <xs:element name="folder_id" type="xs:string" minOccurs="1" maxOccurs="1" />

            </xs:sequence>

         </xs:complexType>

          <xs:complexType name="QueryParameters">

            <xs:annotation>

               <xs:appinfo />

            </xs:annotation>

            <xs:sequence>

               <xs:element name="_0x646c72_filter" type="xs:string" minOccurs="1" maxOccurs="1" />

            </xs:sequence>
            </xs:complexType>

Edit the ReadEmails_REQUEST.jca file and add highlighted property in endpoint-interaction node.

<endpoint-interaction portType="ReadEmails_REQUESTPortType" operation="Get-Folder-Messages_GET">

    <interaction-spec className="oracle.tip.adapter.cloud.CloudInteractionSpec">

      <property name="targetOperation" value="Get-Folder-Messages_GET"/>

      <property name="operationPath" value="https://outlook.office.com/api/v2.0/me/MailFolders/{folder_id}/messages"/>

      <property name="integrationSchemaNamespace" value="http://xmlns.oracle.com/cloud/adapter/microsoftemail/ReadEmails/types"/>

      <property name="ResponseElementNS" value="http://xmlns.oracle.com/cloud/adapter/microsoftemail/ReadEmails/types"/>

      <property name="ResourceURI" value="https://outlook.office.com/api/v2.0/me/MailFolders/{folder_id}/messages"/>

      <property name="QueryParameters" value="_0x646c72_filter"/>

      <property name="generatePartnerLink" value="false"/>

      <property name="HttpVerb" value="GET"/>

      <property name="operation" value="Get-Folder-Messages_GET"/>

      <property name="ResponseElementName" value="messageResource.definitions.messages.Get-Folder-Messages_GET._ResponseDataType"/>

      <property name="integrationWSDLNamespace" value="http://xmlns.oracle.com/cloud/adapter/microsoftemail/ReadEmails"/>

    </interaction-spec>
    
</endpoint-interaction>


Note: We added _0x646c72_ as a prefix in filter property. The actual parameter name is $filter if you see the MS REST API documentation. Since ICS converts the $ sign with _0x646c72_  when we add request parameter in an adapter as a request parameter. That's the reason we already replaced $ sign with _0x646c72_

Save both the files. Open the .iar directly file using 7-zip and replace the updated content in both (WSLD & JCA ) files.

Import the updated integration using Import button. After successful import, Open the ReadEmalis mapper and notice one QueryParamter with name _0x646c72_filter got added


In the query parameter _0x646c72_filter we will assign IsRead eq false. This value will fetch all the email which are unread.

Activate the integration and submit it once. See if we have any unread email in the Inbox

Since there is no unread email in the Inbox as yet, that's the reason there is the blank response.

Let's send one email to the account and run the integration again

Notice one unread email fetched as per the filter added.

This is how more filter can be added directly by editing the WSDL & JCA files

Read messages leveraging Microsoft Email Adapter in Oracle Integration Cloud Service

Microsoft Email Adapter in an Oracle Integration Cloud Service allows retrieving messages in Office 365 and Outlook.com. The Microsoft Email Adapter enables to manage messages, manage folders, and so on.

In this blog, will demonstrate how to read messages from outlook from a specific folder.

Below is the pre-requisite to complete the blog:

  1. Microsoft account: If you don't have MS account, create an account using  https://signup.live.com
  2. Configure Microsoft Email Adapter in ICS: Please see the blog to configure MS Email adapter in ICS
Let's continue and create an Integration which will show step by step procedure to read messages from MS account

1) Create a Scheduled based integration with Oracle ICS


2) Drag & drop the MS Email adapter just below the scheduled activity


3) The configuration wizard will get open. Enter endpoint name and click Next button


4) Next screen will show all the available operation that can be performed on MS account using ICS MS email adapter. For example, Create a folder, Delete a message, Delete attachments, Get messages, Get messages of a folder etc.. For this specific use case, let's select Get messages of a folder operation an click Next button


5) The next page will show the summary of the configuration. Simply Click Done

Notice a Template parameter with name folder_id. This is the folder name from which all the message would be read.

6) Edit the ReadEmails mapper and enter Inbox in the folder_id template parameter


7) Let's iterate all the message using for each activity and print the Subject and sender of the message,. Except these, there are lots of properties which can be taken out from the read email response. Let's drag and drop for each activity just below the ReadEmails adapter and complete configuration:
  • Name: Enter IterateEmails
  • Repeating Element: value is the repeated element which contains all the properties related to a specific message. Drop the value element in Repeating Element text box
  • Current Element Name: Enter CurrentMessage
Once all above options are configured, Click Done button to close the popup


8) Let's print some relevant properties of an email. Drop logger action under foreach loop


9) Let's print Subject & Sender of the message in the logger. Configure below expression in the logger:

concat('Subject is ' ,$CurrentMessage/nsmpr1:value/nsmpr1:Subject, ' Sender of the message ' ,$CurrentMessage/nsmpr1:value/nsmpr1:From/nsmpr1:EmailAddress/nsmpr1:Address)



10) Activate the integration and submit it once


11) Go to Monitoring dashboard, open the instance and open Activity Stream to see the logger activity


If you have 10 messages in your Inbox, the loop will be iterated 10 times and all Subject and Sender of the messages will be printed.

Next blog will show how to filter messages from the inbox.