Read emails and email attachments through Email protocol in OSB 12c

Reading emails and processing the information within the mail, is a common requirement in today's business processes. 

I have always found OSB to be easier in implementing any functionality compared to SOA. Email protocol proves it right for email reading.Unlike SOA, where email adapters, drivers need to be created to read mails, OSB provides a simple protocol to take care of this feature.

In my example, I am going to read mails from a gmail account and process the details in the mail. Before proceeding on how to create an OSB for email reading, lets get the mailbox details ready. 

IMAP or POP3 - 

In order to read any mail box, we would need to connect to the mailbox through one of the two protocols - imap or pop3. imap connects over port 993 where as pop3 connects over port 995. Lets check if the gmail box is imap enabled. Else, we would need to enable it.

1) Login to the gmail box. Click on Settings.

2) Next click on "Forwarding and POP/IMAP".

3) Under IMAP Access section, check if Enable IMAP is checked. If not, select the option.

Now the gmail box is IMAP enabled and we can use this protocol to OSB to access. 

OSB project -

1. Create a simple service bus project. I have named it as PGEmailProject.

2. Create a Service Account with static mappings. This service account should contain the username and password to the gmail account you would listen to. Let me call mine as testgmail@gmail.com.

3. Next step is to start with the proxy service. Right click on the services section of the composite.xml. From the Transports section, choose Email.

4. A Proxy service popup would open. Provide a name for the proxy service. Transport is defaulted to email. Generate pipeline is ticked by default. Click on next.

5. The next wizard allows you to choose the Service Type. In my example, I can have any type of input coming. It need not be an xml. Hence, I chose Messaging service with Request as Text and Response None. Go to next section.

6. Final section is the Transport section where you need to enter the end point uri. The end point uri in Email protocol is nothing but the host and port of the mailbox from which the mails have to be read. In our case, it is imap.gmail.com:993. If we are connecting through pop, it would be pop.gmail.com:995. Click on Finish and we are done with the proxy service creation.

7. Now to set the transport parameters right. Click on the proxy service and move to Transport Details section. This section would be empty showing lot of errors. Time to fill up each of the applicable parameters.

1. SSL Required - As gmail is https, check the checkbox.
2. Service Account - Choose the one we created at the start of the project - PGEmailServiceAccount.sa
3. Managed Server - All polling services, be it email, FTP, listen through one managed server of a clustered environment. We need to choose one of the managed servers. Mine being machine1.
4. Polling Interval - Time between each email poll - 10 seconds for me
5. Email protocol - As mentioned in earlier section, you can choose between imap and pop3. I choose imap. 
6. Read Limit - The number of mails to be read at a time - I left it as 10
7. Pass By Reference - When enabled, the body is staged in archive location and the file location is passed as part of header. This is applicable only for Request type xml. As my reference type is Text, I have it disabled.
8. Pass Attachments by Reference - Attachments are staged in archive location and file location is sent as part of headers. This is not applicable in my case and hence goes disabled.
9. Post Read Action - What do you want to do with the mail once read? I want it to be deleted and hence my option is delete.
10. IMAP Move Folder - Empty. As I didnt choose my Post Read Action as Move.
11. Download, Archive and Error directory - The path to which the mails should be archived, downloaded. I have /test/email/download/ and /test/email/error/ locations in my server and hence have given this location. You can give the location based on your file structure.
12. Request Encoding - Left to the default ISO-8859-1

Finally the Transport details page looks as follows -

8. Time to fill up the pipeline with some activities to check the mails that are read. For simplicity, I have added two alerts in the pipeline to print the body and the headers once they are read.

9. Deploy the project to your server and now send a mail to testgmail@gmail.com mailbox. This is the mailbox to which the proxy is listening to.

10. I sent a mail with Subject and Body as "Test Mail".

Once OSB reads the mails, alerts will be published with the following information.

PG Email Body:

This will contain the content of the body in the email.

<soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">Test Email</soapenv:Body>

PG Email Inbound:

All the header information including who sent the mail, subject, etc are returning as part of inbound headers.

<con:endpoint name="ProxyService$PGEmailProject$PGEmailProxyService" xmlns:con="http://www.bea.com/wli/sb/context">
 <con:service/>
 <con:transport>
  <con:uri>mailfrom:imap.gmail.com:993</con:uri>
  <con:mode>request</con:mode>
  <con:qualityOfService>exactly-once</con:qualityOfService>
  <con:request xsi:type="ema:EmailRequestMetaData" xmlns:ema="http://www.bea.com/wli/sb/transports/email" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <tran:headers xsi:type="ema:EmailRequestHeaders" xmlns:tran="http://www.bea.com/wli/sb/transports">
    <tran:user-header name="MIME-Version" value="1.0"/>
    <tran:user-header name="X-MS-Exchange-Organization-MessageDirectionality" value="Incoming"/>
    <tran:user-header name="X-MS-Exchange-Organization-AuthSource" value="BN1AFFO11FD012.protection.gbl"/>
    <tran:user-header name="X-EOPAttributedMessage" value="0"/>
    <tran:user-header name="X-DkimResult-Test" value="Passed"/>
    <tran:user-header name="X-EOPTenantAttributedMessage" value="9274ee3f-9425-4109-a27f-9fb15c10675d:0"/>
    <tran:user-header name="SpamDiagnosticOutput" value="1:99"/>
    <tran:user-header name="SpamDiagnosticMetadata" value="NSPM"/>
    <tran:user-header name="X-MS-Exchange-CrossTenant-Id" value="9274ee3f-9425-4109-a27f-9fb15c10675d"/>
    <tran:user-header name="X-MS-Exchange-Transport-EndToEndLatency" value="00:00:00.9743427"/>
    <tran:user-header name="X-MS-PublicTrafficType" value="Email"/>
    <tran:user-header name="X-MS-Office365-Filtering-HT" value="Tenant"/>
    <tran:user-header name="X-MS-Office365-Filtering-Correlation-Id" value="9948914d-638a-4764-c610-08d4a3f44afd"/>
    <tran:user-header name="Message-ID" value="<CABOwZYhyJ3enfdVQRMXG=3Ha=YFf_fzEeMYFJSat9gVywytRRg@mail.gmail.com>"/> 
    <tran:user-header name="Return-Path" value="prasannarani.g@gmail.com"/>
    <tran:user-header name="X-MS-Exchange-CrossTenant-OriginalArrivalTime" value="26 May 2017 05:01:39.0171 (UTC)"/>
    <ema:To>Prasanna <testgmail@gmail.com></ema:To>
    <ema:From>Prasanna G <testprasanna@gmail.com></ema:From>
    <ema:Date>Fri May 26 01:01:38 EDT 2017</ema:Date>
    <ema:Subject>Test Mail</ema:Subject>
    <ema:Content-Type>multipart/alternative; boundary=94eb2c1499a87e29340550663b14</ema:Content-Type>
    </tran:headers>
    <tran:encoding xmlns:tran="http://www.bea.com/wli/sb/transports">utf-8</tran:encoding>
   </con:request>
  </con:transport>
  <con:security>
   <con:transportClient>
    <con:username><anonymous></con:username>
   </con:transportClient>
  </con:security>
 </con:endpoint>

Reading Attachments in the email :

The way attachments are read using email protocol depends on Pass Attachment by reference option set at the Email Transport Configuration level. 

If the Messaging type is xml, we do get an option to enable Pass Attachment By Reference. In this case, the content in the attachment is staged in archive location and only reference to the location is sent as part of headers.

When the Messaging type is text, Pass By Reference and Pass attachment by Reference options cannot be enabled. In this case, the attachments are sent as part of $attachment variable.

I added two attachments - a png file and an xlsx file as attachments to another mail. The attachment variable looks as follows.

<con:attachments xmlns:con="http://www.bea.com/wli/sb/context">
 <con:attachment>
  <con:Content-Type>image/png; name="Test.png"</con:Content-Type>
  <con:Content-Disposition>attachment; filename="Test.png"</con:Content-Disposition>
  <con:Content-Transfer-Encoding>base64</con:Content-Transfer-Encoding>
  <con:body>
   <con:binary-content ref="cid:-6ed90ee0:15c2ed5f20c:-7b17"/>
  </con:body>
 </con:attachment>
 <con:attachment>
  <con:Content-Type>application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; name="Book1.xlsx"</con:Content-Type>
  <con:Content-Disposition>attachment; filename="Book1.xlsx"</con:Content-Disposition>
  <con:Content-Transfer-Encoding>base64</con:Content-Transfer-Encoding>
  <con:body>
   <con:binary-content ref="cid:2862c084:15c2ed5ba2e:-7a65"/>
  </con:body>
 </con:attachment>
</con:attachments>

Note that for each attachment, the <con:attachment> section is repeated. You can find the Content type, name of the file, etc. But if you notice, the actual attachment is not present in base64 format, it is present as binary-content with reference cid. This is a feature in OSB, where the actual files are returned in binary-content format. 

In order to further use the attachment content, it needs to be explicitly converted into base64 format. I would also quickly show you, on how to do that. I used a Java class to achieve this functionality and passed the binary content from OSB to java callout and got base64 format as response.

Here's the simple java code -

import weblogic.utils.encoders.BASE64Encoder;

public class Base64Converter {
    public Base64Converter() {
        super();
    }
    
    public static String encode(final byte[] bytes) {
        final BASE64Encoder encoder = new BASE64Encoder();
        final String encodedString = encoder.encodeBuffer(bytes);
        return encodedString;
    }
}

1. Create a simple Java class as above. 

2. You need to import webservices-9.2.2.0.jar into the java project in order to make this class work. 

3. Create a java jar of this class and add into OSB project.

4. Now add a Java Callout in the OSB.

5. The encode function of the class takes bytes array as input. From OSB, add the following binary-content as input to the java callout.

$attachments/ctx:attachment/ctx:body/ctx:binary-content

6. String returned as output of java callout will be in encoded base64 format.

Thus, emails and email attachments can be read through OSB email protocol.

Note: If you are using OSB 11g version, make sure that {MsgIdNotFound:READ_TIMEOUT} parameter is set to 0. Changing it any value is throwing a SocketTimeout Exception - Read TImeout exception. Oracle is looking into this issue.

Use of Tokens in SOA 11g

One of the most painful point in deploying SOA composites is changing the URLs.

Today, there are several ways, we try to make this task easy as follows -

1) Generate configuration files (one or many) and choose them during deployment
2) Ant scripts to change the urls based on environment

But SOA has provided another new approach to handle the urls, which is "Use of Tokens".

Say for example, I have a composite which calls two end systems - App1 and App2. The end point urls of both the systems are present in my composite.xml as follows.

Firstly, we will have to change these end points to their referencing variables in the code.

Create an xml file as follows. Make an entry of every url that is being referenced in your composite.
The key attribute in the xml is the referencing name and the value is the actual url.

Once the above xml is ready, you can add it to your SOA project.
The next step is to replace the urls in the composite.xml to their referencing names in the above properties xml.

Go to composite.xml in Jdeveloper, select the reference for which you want to change the url and select the flower braces symbol as shown in the below picture.

The binding urls browser shows you the reference and the existing end point URL of the service. Click on the edit button at the top right.

In the following window, first choose the token properties file that you created and saved to the composite location. Then choose the Token name for each of the current values shown.

Click on the torch beside Host and Port, it will load the entry values from the properties xml file. Choose the appropriate values.

The updated bindings looks as follows.

Go ahead and deploy the SOA composite to the server.

Now, we need to do the configurations at server level. Login to em console. Under SOA-Infrastructure, select SOA Administration and Token Configurations.

There are two ways of adding token.
1) You can either upload multiple tokens (through the xml file we created for composite) by selecting Bulk Append Tokens option. It will load all the entries from file to the server.
2) Use Modify Configuration file and add all the entries here.

Make sure that the Token names are exactly what you are using in composites. Once all entries are made, the screen looks as below.

Server needs to be restarted whenever you add new entries or make any modifications to the tokens.

Note: The urls changes through tokens impact only the location path of composite.xml. The ui:wsdl location or the uris in wsdls on the project cannot be changed through tokens.

Xquery to group elements in OSB

This blog shows an example of how to convert a flat xml into a hierarchy by grouping them based on some element/tag.

Lets consider the following xmls. 

SourceXml - This xml contains list of orders and order lines. OID can be repeated under each OrderList with unique OLineIDs. This xml like Order lineitems for same or different Orders.

<Orders xmlns="http://www.example.org">
 <OrderList>
  <OID>333</OID>
  <OStatus>Active</OStatus>
  <OLineID>3331</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
 <OrderList>
  <OID>222</OID>
  <OStatus>Active</OStatus>
  <OLineID>2222</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
 <OrderList>
  <OID>333</OID>
  <OStatus>Reactivated</OStatus>
  <OLineID>3332</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
 <OrderList>
  <OID>111</OID>
  <OStatus>Inactive</OStatus>
  <OLineID>1112</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
 <OrderList>
  <OID>111</OID>
  <OStatus>Active</OStatus>
  <OLineID>1111</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
 <OrderList>
  <OID>333</OID>
  <OStatus>Reactivated</OStatus>
  <OLineID>3333</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
</Orders>

Now our goal is to convert it into a hierarchy, where we group all the OLineIDs under each OID into Parent-Child format. 

Sample TargetXML 

 <ns1:Order  xmlns:ns1="http://www.example.org"> 
 <ns0:OrderList  xmlns:ns0="http://www.example.org"> 
  <ns0:OrderID>333</ns0:OrderID> 
  <ns0:OrderStatus>Active</ns0:OrderStatus> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>3331</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>3332</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>3333</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
 </ns0:OrderList> 
 <ns0:OrderList  xmlns:ns0="http://www.example.org"> 
  <ns0:OrderID>222</ns0:OrderID> 
  <ns0:OrderStatus>Active</ns0:OrderStatus> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>2222</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
 </ns0:OrderList> 
 <ns0:OrderList  xmlns:ns0="http://www.example.org"> 
  <ns0:OrderID>111</ns0:OrderID> 
  <ns0:OrderStatus>Inactive</ns0:OrderStatus> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>1112</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>1111</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
 </ns0:OrderList> 
</ns1:Order> 

In order to achieve this, we are going to do for loop with distinct values as follows. 

xquery version "1.0" encoding "utf-8";
(:: OracleAnnotationVersion "1.0" ::)

declare namespace ns1="http://www.example.org";
declare namespace ns0 = "http://www.example.org";
(:: import schema at "GroupTarget.xsd", "GroupSource.xsd" ::)

declare variable $wo as element() (:: schema-element(ns1:Orders) ::) external;

declare function local:func($wo as element() (:: schema-element(ns1:Orders) ::)) as element() (:: schema-element(ns1:Order) ::) {
    
     {
 for $woId in distinct-values($wo/ns0:OrderList/ns0:OID)
 let $woList := $wo/ns0:OrderList[ns0:OID=$woId]
 return
            { data($woId) }
            { data($woList[1]/ns0:OStatus) }
            {
            for $woline in $woList return
                    { data($woline/ns0:OLineID) }
                    { data($woline/ns0:OLineStatus) }
                
               }            
        }    
};

local:func($wo)

Note that there is another way of achieving this functionality by using group by.
But group by does not work with XQuery 1.0. Hence, for XQuery 1.0, we need to use distinct-values.

Hope this blog helps.