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 JNDI name dynamically to an Adapter

Recently, I've been trying configure an MQ adapter that could enqueue data of any structure into specified queue based on the specified JNDI, all passed dynamically at runtime. This post decsribes how you can pass the JNDI Name dynamically to an Adapter.

All you have to do is define a String variable (say myJndiName) that stores your JNDI name (eis/MQ/someThing...already configured on Weblogic) and pass this variable into Invoke Activity properties directly in the .bpel source.   

Note that you'll not be able to find this property in the Invoke Activity > Properties tab.

And that's it. You can assign different values on the fly to the variable myJndiName and same adapter can enqueue in different JNDI locations. It also works for FTP and JMS adapters too. 

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.
   
      

Delete Data from MDS - Single Files

In case an artefact is wrongly published into MDS, it can be deleted from the MDS using 2 ways.

1. Deleting the entire folder
2. Deleting the Selective Files

This post details about the latter.
A file can be deleted from the MDS using Weblogic Scripting Tool. However, in upgraded versions of AIA, this may be available through UpdateMetaDataDP.xml also, very much the same way as we update the artefacts in MDS. To delete a file from MDS follow the following steps.


1. Run WLST command from <MW_HOME>/oracle_common/bin or <SOA_HOME/common/bin>. Note there is same command available in Weblogic Server home directory too but MDS related commands can be accessed through wlst.sh/exe residing in above locations only 

    $ cd $MW_HOME/oracle_common/common/bin

  $ wlst.sh

2. Type the connect command to connect to Admin Server 

   $ connect('weblogic', 'welcome1', 't3://localhost:7001')

3. Once it is connected, delete the undesired file by typing the following command (Note the path starting from /apps)

  $ deleteMetadata(application='soa-infra',server='soa_server1',docs='/apps/AIAMetaData/AIAComponents/ApplicationConnectorServiceLibrary/Generic.wsdl')

You can use asterisk(*) to specify more than one files e.g. to delete all files with names starting with Gen use 
deleteMetadata(application='soa-infra',server='soa_server1',docs='/apps/AIAMetaData/AIAComponents/ApplicationConnectorServiceLibrary/Gen*.wsdl')
Exception:
You may get the following exception
MDS-91002: MDS Application runtime MBean for "soa-infra" is not available. "deleteMetadata" operation failure.

Solution:

- Verify that your soa-infra is up and running and is on the correct server (Admin, soa_server1) as specified in the deleteMetadata() command 
- Verify that you've not fired the command to connect to other managed server during the steps to delete the file. If you connected firstly to SOA server, then to Admin Server and now you try to fire deleteMetadata(), it will give above error. Hence start again.


Happy Learning...

Weblogic and MS SQL Configurations to run projects using SQL Server Connections

Creating the SQL Server connection from Jdeveloper is easy. However, after the project is developed, it has to be deployed on the Weblogic Server, a JNDI needs to be created to connect to the MS SQL Server. Oracle has provided 2 default drivers for MS SQL Server.


*Oracle's MS SQL Server Driver (Type 4 XA) Versions:7.0, 2000, 2005, 2008
*Oracle's MS SQL Server Driver (Type 4) Versions:7.0, 2000, 2005, 2008

The difference between 2 drivers is that one is Transaction Aware and other one is not. There are other drivers also present but they would need specific jars to be placed in the server lib directory, but default drivers would work without any additional configuration on the Weblogic Server. Hence a JNDI to connect to MS SQL Server can be created using any of these drivers very much the same way as you create for Oracle Database.

However, following MS SQL Server configurations also need to be done to be able to connect to MS SQL Server from Weblogic Server.

Create the JTA Procedures

Follow the steps as given here.

Also if you are using the XA driver, you also need to enable XA Transactions on MS SQL Server. Follow the following steps for that. 

1. Turn on support for XA transactions: 
• Go to Control Panel > Administrative tools > Component Services.
• Expand the tree view to locate the computer where you want to turn on support for XA transactions: for example, Component Services > Computers > My Computer. 
• Right-click the computer name, then click Properties. 
• Click the MSDTC tab, and then click Security Configuration. 
• Under Security Settings, click the check box for XA Transactions to turn on this support. (Enable XA Transactions) 
• Click OK, and then click OK again. 
2. Create a registry named-value: 
• Use Registry Editor and navigate to registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL. 
• Create a new registry named-value:
• Name is the file name of the XA DLL (in the format dllname.dll).
• Type is String (REG_SZ).
• Value is the full path name (including the file name) of the DLL file. for example, C:\Program Files\Microsoft SQLServer\ MSSQL.1\ MSSQL\ Binn\ sqljdbc.dll. 
3. Go to Control Panel ->Administrative tools->Services. 
•  Start "Distributed Transaction Coordinator."
4. Please restart the server to make these changes take effect.

Note:
While making the JNDI for data sources on WLS Console, specify the value of property platformClassName as oracle.toplink.platform.database.SQLServerPlatform.

And that's all. Happy Development!