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!

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!
   

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.

Validating incoming String of Date format using Regular Expression

I was recently trying to restrict the format of the incoming variable so as to adhere it to the date format like 27-Nov-2010 04:17:37 PM. Well, this had to be pretty simple by using the xp20:matches function that can be used to match a string pattern against a regular expression. I used my function as

xp20:matches(bpws:getVariableData('inputVariable','payload','/ns3:PropertyDetails/ns3:PropertyValueToUpdate'), '^(0[1-9]|[12][0-9]|3[01])[- /.](Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[- /.](19|20)\d\d (0[1-9]|1[012])[:](0[1-9]|[12345][0-9])[:](0[1-9]|[12345][0-9]) (AM|PM)$')

Ooops. This was not working and but telling me Internal Xpath Error. Well the reason was that getVariableData() function gets the data from the XML schema element in Object format rather than in String. Finally, all I did was that I used the string() function to convert the object from variable data and it worked without any issue.

xp20:matches(string(bpws:getVariableData('inputVariable','payload','/ns3:PropertyDetails/ns3:PropertyValueToUpdate')), '^(0[1-9]|[12][0-9]|3[01])[- /.](Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[- /.](19|20)\d\d (0[1-9]|1[012])[:](0[1-9]|[12345][0-9])[:](0[1-9]|[12345][0-9]) (AM|PM)$')

Just a small learning…

Provider ABCS not able to read the data while transform, Default Part Name not correct

I started working on AIA 3.0 with SOA 11.1.3.3 and developed a simple Worker interface that fetches the data from a table and transfers it to Provider ABCS.

But while running it, I faced the 2 errors.

The first error that I faced was at the time of compilation. Well, that was seemingly a bug which was worked around.

Later, when I was testing the process, it behaved strange in a way that the all records travelled till Provider ABCS Receive activity properly but did not get Transformed. The Transform activity payload looked empty as shown below. 

  <Invoke_WriteAllWorkersFile_InputVariable>

      <part  name="XxbbIntgWorkersCollection">

           <XxbbIntgWorkersCollection />

      </part>

  </Invoke_WriteAllWorkersFile_InputVariable>

  

I tried various things for this. But later found that the problem was with Provider ABCS. While constructing the PABCS using service constructor, it created the SyncWorkerProvABCSImpl.WSDL (<ProjectName.WSDL>) message part as

  

  <message name="SyncWorkerReqMsg">

        <part name="SyncWorkerEBM"   

              element="eboebo:SyncWorkerListEBM"/>

  </message>

However, looking at the transformation I noticed that actual data is coming into the part with name SyncWorkerListEBM instead of SyncWorkerEBM. Hence as my transform by default was trying to access the data from the SyncWorkerEBM part, it was not getting any data resulting in empty transformation output variable. I changed the above code as following and it worked like a charm.

  <message name="SyncWorkerReqMsg">

     <part name="SyncWorkerListEBM" 

           element="eboebo:SyncWorkerListEBM"/>

  </message>

The same has been raised to Oracle but till they come up with something, we need not stop. Let’s be going…

Oracle Fusion Middleware Console not showing up properly : Screen Reader Setting

Yesterday I was looking at my friend’s FMW and it was not displaying the details properly. It showed something like below with radio buttons and partially visible table content.

Well, this is not any problem but just an accessibility option. The solution is pretty simple. To correct this, go to Oracle FMW Console > Setup >  My Preferences Menu Item > Accessibility Menu Item > Uncheck I use a screen reader. This will solve the problem.