Polling files on-demand : File Based Triggers

Many a times there is a need to trigger the polling of File/FTP Adapter on the basis of some event e.g. after process A has finished writing the file F1, process B should poll for the file F1 OR just process the files only between 10.00 AM to 11.00 AM. While the above could be accomplished to some extent using Sync Read operation, but it requires the knowledge of the File Name in advance (wild cards can't be used).

Well, the solution is File-Based Triggers. File-based triggers can be used to control the File/FTP adapter activation. File Triggers are nothing but the files with .trg extension. The content of trigger file doesn't matter. When File-based triggers are used, File/FTP adapter checks for the existence of a Trigger file (e.g. ReadFile.trg) before reading the actual file. If the trigger file is found, it reads and processes the actual file else it does nothing. You can configure how frequently the adapter checks for the trigger file and hence can control the processing of the actual files.
To use a File-Based trigger, follow the below steps.
1.) Define a File/FTP adapter with Read Operation.
2.) On the File Polling page, Check the option Use trigger file and provide the Trigger File details as shown below.

3.) Complete the adapter wizard.
4.) Follow the normal steps to create a bpel process to poll the file. Deploy this process.

As soon as the process is deployed, File/FTP adapter looks for the trigger file in the C:/TriggerFileDir location. If it finds the triger file, it will poll and process the file. How frequently it looks for the trigger file depends on the property TriggerFileStrategy (we didn't specify this property anywhere because the property is set already by default to value EndpointActivation).

The various possible values for TriggerFileStrategy are:
OnceOnly: The adapter will looks for the trigger file only once in its lifetime. Once it finds the trigger file, it remembers that across restarts and redeployments. This could be very risky as the adapter would never look for a trigger file again in future.

EveryTime: The adapter looks for the trigger file everytime it goes for polling the file. At every polling interval, it checks if the trigger file is there or not. If the trigger file is present, it picks the actual file else it will do nothing and will check for the existence of trigger file at next polling cycle.

EndpointActivation (Default): The adapter looks for the trigger file every time the composite is activated that means everytime you Restart/Activate/Redeploy your composite.

Add the property in .jca file of your File Adapter as shown below and it's done.

Note: You may want to delete the Trigger file after your file has been successfully processed. For this, there is no Delete operation. But you can use Sync Read operation to read the trigger file and check Delete Files after successful retrieval option.

Happy to hear back if any suggestions or comments!

Sending attachment using Email Activity in BPEL 11g

Sending an attachment using Email Activity (in SOA 11.1.1.5) is an easy task. All you have to do is drag and drop an email activity and double click on it. Assign the following parameters and the attachment will be sent along with the email.

Name- Name of the attachment as it should appear

MimeType- This MimeType is for attachment. Choose appropriate MimeType from here.

Value - Write what you would like to see in the attachment. Simply hard coding.

Well, sometimes the requirement may be to send some custom data, may be you fetched some data at runtime from the DB and would lke to send it as attachment. This isn't complex either, all you have to do is modify the ContentBody parameter as follows. You can do this successfully from the EmailParamsAssign Activity but don't forget to verify the same in the bpel source too.

Extending this a bit further, if you'd like to send some files (image,pdf,doc,xls,csv) that are already stored at some server location or you may have written them from your process, follow these  steps.

1.) Set the appropriate MimeType for the file.

2.) Add the following line in the .bpel source under appropriate BodyPart.

3.) Add another copy operation in the bpel source to assign string('base64') to ContentEncoding in BodyPart[2].

4.) Modify the  ContentBody  parameter to read the file at runtime using ora:readFile() function as follows.

Key Points to Note:

• The file location and name in ora:readFile() above are case-sensitive.
• Specifying the correct MimeType is very important. If the MimeType is incorrect, either the mail won't go or you may get encrypted characters in the attached file.
• file:/// (three slashes mean absolute path and two slashes mean relative path)

The above solution works with all the files. Should anyone need the working project, I can mail the same. 

Inserting a New Line character in message using BPEL XSLT

In a requirement, we were trying to format the data fetched from DB (in XML format in BPEL) into a String variable with a new line inserted after each record. So that the data below would appear in the String variable like Following is the simple transformation to achieve the same.

<xsl:for-each select="/ns0:SelectFromLookupOutputCollection/ns0:SelectFromLookupOutput">
<xsl:value-of select='concat(ns0:property_name, ",", ns0:property_value,"&#10;")'/>
</xsl:for-each>

Key Points to Note:

• The magic lies in the word &#10; which serves as a new line character in XSLT (11g). In above case, it gets appended at the end of each record resulting in new line at the end of each record.
• The function somehow doesn't work in SOA Suite 11.1.1.3 (BUG-13602524, thanks to my colleague Nivea for identifying this) but works in 11.1.1.4 and older versions.

How to pass the queue name dynamically to MQ Adapter

Requirement: To pass the queue name dynamically to MQ Adapter from a variable


Use-case: I have a process that is required to enqueue the data in one of the two MQs at runtime based on certain condition using single MQ Adapter. So if the condition is true the data is to be enqueued in queue A else in queue B. However, the payload schema remains the same in both the queues. (using SOA Suite 11.1.1.3)


Solution: Use the jca.mq.ISpec.EnqueueMsgToQ property appearing in the Properties tab of Invoke Activity. Assign the value of the property to the variable name which contains the MQ name.

Passing Queue Name from a variable

Setting the value to this variable will allow you to set the queue name dynamically in the process or from outside the process.


Advantage: MQ Adapter can be loosely coupled and kept in a separate composite which can be called by any caller composite. Also a single MQ Adapter can be used for en-queuing to multiple queues. 

Pass multiple parameters, dynamic query in Pure SQL in Database Adapter

I came across a case in which I was required to pass the bind parameters values on the fly. The values of parameters were not fixed and hence I couldn't define all of them as the bind parameters. Hence in the search of the solution I wasted considerable time until I was directed to see this great solution. The solution was fantastic and I customized it further to make it work with SOA Suite11g. This can be further extended with Joins to do wonders as required. 


Use Case : I want the salaries of all the employees whose first names I'd pass at run time. Hence it can be single name or n names coming as parameters. The very simple query, as it comes to our mind, would be


SELECT SALARY FROM EMPLOYEES WHERE F_NAME IN (?) 
Where ? is the argument that is passed dynamically. 


However, the same query doesn't work in DB adapter for any combination passed dynamically such as ('NEERAJ', 'JACOB') or "('NEERAJ', 'JACOB')" or 'NEERAJ', 'JACOB' for the adapter tries to interpret the incoming argument as a bind parameter and not SQL string.


Solution :  If the parameters can be passed as a result of an inner query the problem can be solved. The following query uses CONNECT BY LEVEL clause to separate the individual parameter values separated by a : and represents them as individual result row.

SELECT SALARY FROM EMPLOYEES WHERE F_NAME 

IN (WITH BIND_PARAM_LIST AS

(SELECT #val FROM dual)
SELECT SUBSTR(#val1, (decode(LEVEL, 1, 0, instr(#val2, ':', 1, LEVEL -1)) + 1), (decode(instr(#val3, ':', 1, LEVEL) -1, -1, LENGTH(#val4), instr(#val5, ':', 1, LEVEL) -1)) -(decode(LEVEL, 1, 0, instr(#val6, ':', 1, LEVEL -1)) + 1) + 1) a
FROM BIND_PARAM_LIST CONNECT BY LEVEL <=
(SELECT(LENGTH(#val7) -LENGTH(REPLACE(#val8, ':', NULL)))
FROM BIND_PARAM_LIST) + 1)


To understand the query one needs to be familiar with the CONNECT BY clause which is used for hierarchical queries. If you simply do 


SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=5, it prints 1 to 5 in each row like

LEVEL
=====
1
2
3
4
5


The same logic is being used in the above query to separate the various F_Names. It just sub-strings the incoming string NEERAJ:JACOB:ROBERT, : wise for each LEVEL to feed the result in the IN clause as 


NEERAJ
JACOB
ROBERT


Usage : Just add this query in the DB Adapter. Be careful to use # instead of ? as in 11g the bind variable name can be defined as #bind_variable, in our case #val1, #val2 ... #val8. Also you'll have to assign the same input string to these multiple vals. The val variables get exposed as input parameters while assigning and can be assigned as 'NEERAJ:JACOB:ROBERT'(your parameter list). You can use any other separator too e.g. ; instead of : by customizing the query a bit. 

Note:The above solution requires to assign the same value to bind parameter #val 9 times. This limitation is addressed by a more-refined Query as suggested here.

Just deploy the project and it is done. The same thing can also be achieved by using query-database() function as described here.
   
      

How to check the Email Notification History in SOA Suite 11g FMW Console

Problem : I want to check email notification history in SOA Suite 11g FMW Console. When was an email sent, to whom and from whom and blaw blaw blaw.


Solution : Just go to FMW Console > Farm_Domain > User Messaging Service > Right Click usermessagingserver(soa_server1) > Message Status. It shows you the email notifications details. You can use the search criteria also to filter the data.

How to get XML output from the oraext:query-database() function

oraext:query-database() is a wonderful function that can be used to

1. Fetch the data in the Assigns/Transformations on the fly without the use of DB Adapter
2. Can be used to run any Dynamic Custom SQL including variable IN parameters and GROUP BY clauses.

However, the output returned from this function is always a long string depending on the no.of resultant records. But we can always transform the same into XML format to traverse it in an easier way across the process.

1. While calling the function, use the 2nd and 3rd parameters as true() rather than false(). It will ensure that ROWSET and ROW tags get generated in the resultant String.

2. Now create a schema with ROWSET as root element and ROW as unbounded repeating element. The best way to do this is to copy the resultant XML string into an XML file and use Schema from XML file option to create the schema. This will create the schema for your resultant output string. Now you can create a variable of this schema to store the returned string in the XML format. 
Note: The returned string from query-database() function does not contain any namespace, whereas the schema created above would contain a namespace. Hence remove the following attributes explicitly from your schema so created. It is also advisable to validate/create your schema using tools like XMLSpy etc. for better confidence.

3. Now in the Assign activity use this function as follows to assign the result to Schema element just created in above step. You can use any different query or the variable that contains your query in the below example.

oraext:query-database('select first_name, last_name from employees', true(),true(),'jdbc/EBS_BOLINF')

And you get the result in the XML format in the TempEmpResults variable that you can use anywhere in the process!