Friday, October 7, 2011

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. 

Wednesday, July 13, 2011

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.
   
      

Monday, June 27, 2011

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...

Tuesday, May 10, 2011

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!

Tuesday, April 5, 2011

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!
   

Thursday, March 3, 2011

How to connect to MS SQL Server through Jdeveloper 11.1.1.3

To connect to MS SQL Server you need to follow the following steps.


Pre-requisite: You need to have a sqljdbc4.jar file placed in the jdev/lib/folder. It can be downloaded from this  (1033\sqljdbc_3.0.1301.101_enu.tar.gz) path and extracted to get sqljdbc4.jar. Finally it needs to be at jdev/lib location.


1. Go to New Database Connection and create a Database Connection by providing all the required details. Remember to select SQLServer as Connection Type.


2. In the Library section click Browse and add the Library entry as shown below.


3. Click on Test and it says Success....


However, for the projects using MS SQL Server connection deployed on Weblogic server, some Weblogic and MS SQL Server configurations also need to be done that can be found here.