How to serch composites for BPEL process defined index values in soa suite 11g

Problem:

I have migrated my 10g BPEL processes to 11g. Few BPEL processes, based on input values they receive, sets the 1 to 6 index values. For a process where instance volume is higher/large, it is easy to search the instance using the index value. In 10g, I used to search on BPEL Console --> Instances tab, using "Index/Custom Key" text box. But as soon as I went to 11g, I found there is no option on em(Enterprise Manager) console to search BPEL instance using index.

After research, I found out that in 11g Oracle has not provided option to serach BPEL instance using those indexes. You can search for composites using composite indexes, but not the BPEL instances using BPEL process defined indexes. However, the values are still being stored in CI_INDEXS table under soainfra database schema.

Some simple but very useful 10g functionality is omitted even after the release of 11.1.1.4 release.

solution:

Simplest way, fire a query and get the instance ID from the ci_indexes table. But wait, it is not that easy. Let us see what exactly happens there.

I have a composite TestCompsite1. My bpel process TestHelloWorld is part of the composite TestComposite1. During execution, the bpel process setting values in ci_indexes table, say index_1 = "OracleWorld".

You can search the ci_indexes table using below query

select * from ci_indexes where index_1 like 'OracleWorld' (Add more OR conditions as you may want to search on other index keys)

You will get bpel instance number as cikey column value. But you cannot search using that output on em console as it only allows you to search based on the compsoite instance number of composite instance index values. The solution is below query, join ci_indexes with cube_instance

select a.cmpst_id cube_composite_id, a.cikey cube_cikey, b.cikey index_cikey

from cube_instance a, ci_indexes b

where a.cikey = b.cikey

and index_1 like 'OracleWorld'

the column value of cube_composite_id is what exactly you will search on em console for. This will open composite. If you have multiple bpel execution, then enable "show Instance ID" option. Use the index_cikey column value to reach the correct bpel instance.

I thought this would be easy when I saw values in ci_indexes table, but it required to look further in soainfra db. For simplicity, one may like to wrap this query in report to make the life easier.

Hope this will help

Cheers

Nirav

List of SOA Suite versions and patch numbers

The page is http://www.oracle.com/technology/products/soa/soasuite/collateral/downloads.html is available on OTN describes the all the versions 10.1.3.x and 11gR1.

All the versions for SOA Suite, related patch numbers and those details are available on the page.

Take a tour of that page...

TNSListener not starting after re-installation of Oracle Database XE 10g

In certain cases we need to install the Database XE again. Specially when I am installing a new SOA Suite server. While uninstalling the XE, if any of your XE services are UP, your re-installation gives problem with TNSListener service startup.

you can do this,

1) Delete any of the following services if they exist in your Control Panel -> Administrative Tools -> Services.

• OracleServiceXE
• OracleMTSRecoveryService
• OracleXEClrAgent
• OracleXETNSListener

To delete the service type "sc delete [service_name]" on your command prompt.

2) Go to Registry, and delete any reference to your XE database. You can search on the folder name where XE was installed, mostly "oraclexe" to get all the references.

Now, reboot your system and then install XE again. The installation should be fine now and TNSListener service should work properly.

Cheers

FTP Adapter failed to archive the files

JDev n SOA Suite 10134.

The reason that FTP Adapter failes to archive the files may be absense of archive directory or lack of privileges to write in that directory.

In such cases,

1) Check the logs at domain.log

2) SOA Suite in case of failure of archiving to given directory, archives the file at SOA_HOME/j2ee/home/fileftp/defaultArchive/

3) the file name will be stored with a suffix of hostname_domain_processname_version_timestamp format.

Cheers

Upgrade to SOA Suite 11g, will I go for that quickly?

Hiii there,

It has been an opportunity for me now to start writing again in my blog with SOA Suite 11g release.

I have already installed SOA Suite 11g successfully and created few processes on that, but that gave me a thought that why I should upgrade from 10.1.3.4 to 11g quickly ?

I think it is not wise to make hurry and move to 11g platform. Few reasons for that may be,

• Moving to 11g platform can require substantial change or upgrade to your current 10.1.3.x infrastructure.
• It was certainly easy to migrate from 10.1.3 to 10.1.3.1 to 10.1.3.3 (many would have faced lot of isses mvoing to 10133) to 10.1.3.4, but 11g is a change of application server, and Java Container. JRockit(i.e. BEA's JDK) and weblogic server (server container) had certainly been the best available in the market in BEA era, and they must be understand well before we really go for upgrade. There are more logging options and more then that a lot more configuration files (a bunch of .xml) to be understood with weblogic server domains(like instance in SOA Suite).
• Fusion Middleware 11g Platform can bring substantial benefits if understood properly and implemented in right direction. So, it is not suggestable that just in order to upgrade SOA Suite, we upgrade our production servers. FMW 11g platform can bring your web applications, integration components, portal and document control components together in unified Developer studio. A good upgrade policy can save lots of bucks but still gives organization a bunch of new dimensions to do the business. Like, today organizations wants to close their multiple E-Business Suite instances just to have one single unique E-Business 12 version(preferably on-demand) and get a new life in their business, similarly platform FMW 11g can add new life in the organization if implemented with thoughtful design.
• We still can go for only SOA Suite Upgrade. But remember SOA Suite 11g has many new good features like composite application concept, Entity creation concepts in BPEL projects, Business Rules component as a part of your composite application project, send notification activity, Oralce ESB as new component Median. So, you may want to plan and upgarde your current SOA Suite processes with some exciting new features before you deploy them to your SOA Suite 11g production server.

How about having Devlopment SOA Suite 11g server in parallel to your current 1013 SOA Suite development server. That would be really a good idea as it will help to understand the new functionality easily and will give us a chance to test new version for stability, performance and deployment perspective.

If you want to upgrade, please remember that

• Upgarde can be carried out on all the SOA Suite Release 10.1.3.x release.
• For current SOA Suite 10.1.2, we first need to upgrade to 10.1.3 and then to upgrade to 11g.

More details on 11g Upgrade is available at OTN, find supported upgrade points here and find here the details how to upgrade.

Have a Happy Upgrade,

Cheers,

Nirav

"Activities" tab in BPEL PM 10133 shows exceptions

I agree that working with SOA Suite 10133 with Basic installation and configuration based on Olite keeps you busy searching for solutions to the issues you face.

On my Vista Enterprise I installed 10131 basic installation and applied the patch for 10133. Also completed the post-installation task. When I opened my BPEL Console, as I expected I could not see the flow in the instances. But the problem which annoyed me a lot was with the "Activities" tab.

I assured that all my BPEL and ESB processes were still working fine. But when I clicked on "Activities" tab after installation I got the screen with the exception message as below

Oracle BPEL Process Manager Full Cycle

An unexpected error has occurred while executing your request. This is most likely related to a defect in the Oracle BPEL Process Manager product. We apologize you can post the error to the OTN forum and we will get back to you as soon as possible.

Attachments:

Build Information:

*****************************************************************************

Oracle BPEL Server version 10.1.3.3.0

Build: 0

Build time: Fri Jun 15 06:05:31 PDT 2007

Build type: release

Source tag: PCBPEL_10.1.3.3.0_GENERIC_070615.0525

Exception Message:
http://javax.servlet.ServletException
Cannot find work items.
An attempt to fetch the work items using the where condition "" from the datastore has failed. The exception reported is: ODBC S1002 invalid column number
Please check that the machine hosting the datasource is physically connected to the network. Otherwise, check that the datasource connection parameters (user/password) is currently valid.
sql statement: SELECT * FROM admin_list_wi WHERE ci_domain_ref = 0

Exception Trace:

javax.servlet.ServletException: Cannot find work items.
An attempt to fetch the work items using the where condition "" from the datastore has failed. The exception reported is: ODBC S1002 invalid column number
Please check that the machine hosting the datasource is physically connected to the network. Otherwise, check that the datasource connection parameters (user/password) is currently valid.
sql statement: SELECT * FROM admin_list_wi WHERE ci_domain_ref = 0

...


The solution I got was to re-run the scripts to the post-installation of 10133 patch. I could find that wi-fault table and three other views were missing from the database though they were there in the scripts.

Thanks Sash for pointing this out. You can find more information @ http://forums.oracle.com/forums/thread.jspa?threadID=837295&tstart=0.

Cheers

Nirav

Generating Remote and Binding Exception in Oracle BPEL

I was testing Remote exception and Binding exception for my sample BPEL Process and thought to put the post here.

My BPEL process "exceptionRB" looks like below and calls another BPEL Process "Hello1".

To create the Remote and Binding exception I played with the "Hello1" BPEL Process.

1. To generate Remote Exception, turn the process state of "Hello1" process to "OFF" from manage tab on BPEL console (The exception description would be that the process state is OFF). Or undeploy the "Hello1" process (exception : cannot read the WSDL).
2. To check Binding Exception, remove the operation "process" in "Hello1" which is called by BPEL process "exceptionRB". In my case I have change the operaion "process" to "process2" and redeployed the BPEL Process "Hello1".(Exception message would be "No such operation.")

Hope this will be helpful.

Cheers

Nirav

Calculating difference between 2 datetime values in Oracle BPEL

If you are require to use lots of datetime values, then you may also need to manipulate those different datetime values. Manipulating XPath 2.0 functions may be a problem. For this, You may refer to blogs by Ramkumar Menon and Antony Reynolds.

I'm using XPath 2.0 functions to get date and time in BPEL Process. You can get more information about XPath 2.0 here. Below is the example I have created based on Ramkumar's blog to calculate the duration between two datetime values in Oracle BPEL.


First create two schemas,

input.xsd

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.example.org"
targetNamespace="http://www.example.org"
elementFormDefault="qualified">
<xsd:element name="input">
<xsd:annotation>
<xsd:documentation>
A sample element
</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="date1" type="xsd:dateTime"/>
<xsd:element name="date2" type="xsd:dateTime"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

and output.xsd

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.example.org"
targetNamespace="http://www.example.org"
elementFormDefault="qualified">
<xsd:element name="output">
<xsd:annotation>
<xsd:documentation>
A sample element
</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="duration" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>


As schemas are ready, we will create the BPEL process now.

In BPEL Process, the namespace for XPath 2.0 is declared like

xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"

also xmlns:ns1="http://www.example.org"


and the the this xp20 functions can be used as below to extract the datetime values.

• xp20:current-dateTime() - returns a dateTime element that has the current date and time as formatted above. (e.g. 2008-11-15T11:32:28.256). I'm using this function and I am to calculate difference for variable holding this value.
  
• xp20:current-date() - returns a date element that has the current date as formatted above without the time portion.
• xp20:current-time() - returns a time element that has the current time formatted as above without the date portion.

In BPEL Process, create two variables,

<variable name="inputdate" element="ns1:input"/>
<variable name="outputValue" element="ns1:output"/>

Create the transformation activity,

<assign name="Transform_1">
<bpelx:annotation>
<bpelx:pattern>transformation</bpelx:pattern>
</bpelx:annotation>
<copy>
<from expression="ora:processXSLT('Transformation_1.xsl',bpws:getVariableData('inputdate'))"/>
<to variable="outputValue"/>
</copy>
</assign>


Let's look at how the XSL file is to be designed. We are creating the template "timeDifference" and using the same to find the time difference.

In the starting of the XSL file, definition of the source and target XSD.

<?xml version="1.0" encoding="UTF-8" ?>
<?oracle-xsl-mapper
<!-- SPECIFICATION OF MAP SOURCES AND TARGETS, DO NOT MODIFY. -->
<mapSources>
<source type="XSD">
<schema location="input.xsd"/>
<rootElement name="input" namespace="http://www.example.org"/>
</source>
</mapSources>
<mapTargets>
<target type="XSD">
<schema location="output.xsd"/>
<rootElement name="output" namespace="http://www.example.org"/>
</target>
</mapTargets>


XSL stylesheet version to be changed to 2.0 and looks like

<xsl:stylesheet version="2.0"

...
xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ns0="http://www.example.org"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
exclude-result-prefixes="xsl ns0 xsd ora ...">

In next phase define the template and use it to calculate the difference as below

<xsl:template match="/"> <!-- Suggests that template is in this file only -->
<ns0:output>
<ns0:duration>
<xsl:call-template name="timeDifference">
<xsl:with-param name="date1" select="/ns0:input/ns0:date1"/>
<xsl:with-param name="date2" select="/ns0:input/ns0:date2"/>
</xsl:call-template>
</ns0:duration>
</ns0:output>
</xsl:template>

<xsl:template name="timeDifference">
<xsl:param name="date1"/>
<xsl:param name="date2"/>
<xsl:value-of select="(xsd:dateTime($date1) - xsd:dateTime($date2))"/>
</xsl:template>
</xsl:stylesheet>

If you inputs are
2008-11-15T11:32:28.256
2008-11-15T11:33:32.256

the output will be
T1M4.0S



Hope this helps. Let me know if you are facing any issues.

Cheers

Nirav

Connecting SQL Server from JDeveloper

Here is a small example on how to create a SQL Server Connection on JDeveloper.

First of all you will need the sqljdbc.jar. You can download it from the Microsoft site. sqljdbc.jar is available across different versions SQL Server 2000, SQL Server 2005, etc. I'm connecting to SQL Server 2K here using JDeveloper 10133.

Once downloaded, we need to add it to the JDeveloper Libraries. I have listed here the steps to create a user library from JDeveloper Help:

• From the main menu, choose Tools Manage Libraries.

• In the Manage Libraries dialog, select the Libraries tab, then select the User node, and click New.

• In the Create Library dialog, enter a library name, select the Source Path node, and click Add Entry .

• In the Select Path Entry dialog, browse to the location of the drivers for the database you are connecting to. Select the driver files, and click Select.

• In the Create Library dialog, click OK, and in the Manage Libraries dialog, click OK .

We can add this library while creation of databse also, but adding first using Manage Libraries is a good practice.

Now in JDeveloper, go to Connection Navigator, Right-Click on Database and select "New Database Connection" from the context menu. Follow the wizard as displayed below:



Select the last option "Thid Party JDBC Driver" from the drop-down.

On the next page, provide valid UserID and Password.



On step 3

For Driver Class, name is com.microsoft.sqlserver.jdbc.SQLServerDriver, and select the library sqljdbc.jar from Manage Libraries as we have added above.



Define the URL as below
jdbc:sqlserver://<db_instance>:<port>;databaseName=<db_name>;user=<userid>;password=<password>


Click Next and Test the connection.

Let me know if you are facing any issues.

Cheers

Nirav

Note : Before deploying BPEL process, we need to deploy sqljdbc.jar file to two different locations on the server: soa_home/bpel/lib and soa_home/j2ee/home/applib. For more info..., refer to http://forums.oracle.com/forums/thread.jspa?threadID=978304&tstart=0

Oracle Webcast : Fusion Middleware for E-Business Suite

As a follow-up to OpenWorld, for those of you that could not attend, Oracle is hosting a live Webcast next week on Tuesday, 7-October 9AM PDT (5PM BST, 21:30 India Standard Time) "Leveraging the Enterprise Service Bus with E-Business Suite".

You can find further details here on oracle site.

I got this info as I've subscribed to Juliana Button's Oracle blog.

See you in the WebCast.

Cheers

Nirav