Thursday, August 30, 2012

count() vs. countNodes() in BPEL 2.0

I have wrestled with these two since 10g . However, I still mix them up when I use them in BPEL 2.0. The function signature of count() has changed since BPEL1.1.

For the record, here is the scoop on these two functions in BPEL 2.0:

I have created a simple test BPEL based on the sample schema (at the end of this post).

I tested count() and countNodes() like below:

#1 count($outputVariable.payload/client:result)

It shows 0. Please note "result" is optional based on the XSD. Additionally, please note that if you enter an invalid path, such as “$outputVariable.payload/foo/bar”, JDev won’t compile it. So count() only allows valid xpath for the variable type, check #5 of countNodes() below to see more on this.

#2 count($inputVariable.payload/client:input)

It shows 1 as expected.

#3 ora:countNodes('outputVariable', 'payload', '/client:processResponse/client:result')

It shows 0, same as case #1. However, pay close attention to two things: first argument is the variable name as a string, not “$variable” as it appears in count(). Additionally, the 3rd argument uses the full path “/client:processResponse/client:result”. It includes “client:processResponse” section.

 #4 ora:countNodes('inputVariable', 'payload', '/client:process/client:input')

     It shows 1, just like in case #2. However, pay attention that first argument is the string name of the variable. Second argument is the “part” of the message type. Third argument needs the full path like “/client:process/client:input”. In the case of count() function, it appears that it skipped the “root” element “client:process” after payload.

#5 ora:countNodes('outputVariable', 'payload', '/client:input/test_invalid_path)

Finally, this one shows 0 as expected. The point here is to show that you can enter a path that is not defined in your XSD. This may come in handy if you have a payload that does not conform to your input schema, or you simply do not know the schema beforehand.

Here is the schema, please note that I have made "result" optional:

<?xml version="1.0" encoding="UTF-8"?> 
<schema attributeFormDefault="unqualified"
            <element name="process">
                                                <element name="input" type="string" minOccurs="0"/>
            <element name="processResponse">
                                                <element name="result" type="string" minOccurs="0"/>

Sunday, August 26, 2012

Search and Replace in Composite Configuration Plan File

SOA 11g provides a configuration plan file for composite deployment. A common use is to search and replace hosts and ports depends on the platform (dev, test, qa, prod) you are deploying to. The question is when do those search / replacement take place?

If you wonder why do I ask this seemingly obscured question, let's take another look at it.

Suppose you do development on server "", and SOA runs on port 8001. Then you need to migrate your work to "", and SOA runs on port 80 over there.

So your plan file may contain something like:


When you click on "Deploy" and pick your configuration plan file, a common misconception is that "" will be magically replaced, and "" is almost irrelevant. However, if you shut down "soaDEV" server, you will notice that JDEV will fail the compilation.

When you click "Deploy" in JDev, it's actually a two-step process. First Jdev generates the jar (sar) file. Then it uploads (deploys) the jar to the SOA server (you can actually do these two steps manually. First compile / generate the jar file. Then go to EM console to deploy the composite.)

The search / replacement only takes place during the 2nd stage. When SOA server receives the composite jar file, it does a second compilation using BPEL Compiler. So when JDEV compiles the BPEL source code, it still requires your original "" to be up and running. It still needs to reference the WSDL from port 8001on "soaDEV".

Now we know the facts, I still have this question that bugs me all the time. Why can't (won't) JDEV do search and replacement before the compilation? I don't truly know the answer. I'm sure there is a fundamental answer to this question.

I always have this eerie feeling that 9 months after you have finished your production deployment, you still need to keep your soaDEV server running in order to support your soaPRD environment. Because Jdev needs to reference soaDEV in order to compile. What about the potential inconsistency between soaDEV and soaPRD 9 months after or even longer?

Reading XMLType with DB Adapter

My Oracle table has a column with XMLType. I have found out it can be very tricky when reading the XMLType column with DB Adapter.

First of all, if you use TopLink to map the data, BPEL will read in the XMLType column as a string. 

That string variable is no longer an XML element inside BPEL, and you can no longer process it. In fact, it's very trickery to the eyes if you view your DB read output in the EM console, it appears that the XML payload is read in successfully, but that's not true. If you check the raw XML view, you'll see the "fake" XML element is a string with special escaped characters "&lt;". So you can not use this scrambled string for much of anything.

<messagePayload>&lt;cws:cwsPaymentEventService xmlns:cws=""> &lt;cws:cwsPaymentEventDetails PayorAccountID="0956488888" PaymentAmount="12" PaymentDate="2012-08-12T01:02:03" TenderSource="KUBRA-RT"> &lt;/cws:cwsPaymentEventDetails> &lt;/cws:cwsPaymentEventService></messagePayload>

One way to tell if your variable is an XML structure or a string (with "fake" XML structure) is to see if the XML elements are shown as expanded or on one line.

So what if I need to read in the XMLType column and still want to process it as an XML structure? The work around is to use "pure SQL" to read the element in. Then it preserves the XML structure. You can assign the "pure SQL" read output into a variable of any XML structure. It's up to you to make sure that DB field (XMLType) and your BPEL variable have the same structure! Great flexibility, but use with care.

What if you need to do a DB poll with DBAdapter? DB poll doesn't have an option of "pure SQL". My solution: do two reads. First the poller loads the row in, then you need do another regular DB read for the same row (with your self defined DB key), this type you use "pure SQL". This is definitely inefficient to say the least, but it's one way to get around the limitation of DBAdapter and toplink.

I think the alternative to avoid two reads is to use parseEscapedXML() function. My colleague reminded me on that today, I vaguely remember we used that function on a previous project. But I haven't tried it this time.  I believe it should work as the function name indicated. You parse that string, and place the result into your XML element.

Additionally, if it is an option for you, you can use AQAdapter. AQ adapter can load in XMLType just fine. It's a one step solution.

Thursday, August 23, 2012

OSB import DB JCA: "Invalid Toplink Mapping XML, DB sequence..."

while importing JCA DB adapter into OSB, I got this error:

Multiple annotations found at this line:
- Invalid Toplink Mapping XML. Error Message: Attribute not allowed (no wildcards allowed): table in element sequence-
field@ Location of invalid XML: <xml-fragment table="CIS_REQUEST_Q_TBL"
xmlns:xsd="" xmlns:eclipselink=""
- Start tag of element <adapter-config>

After googling around, I found this post

So removing the "table" attribute from <sequence-field> took care of the problem:

<sequence-field name="REQ_ID"/>

Wednesday, August 22, 2012

BPEL Wait, it's synch vs. asynch?

In BPEL process if you use "wait", the result may depend on if it's synch or asynch. I found it out the hard way. I suspect it's documented somewhere by Oracle. I just haven't found it.

Synch: if you use "wait" for 3 seconds in a synch BPEL, you'll get a time out exception. I don't know why it does that. If you wait for 1 or 2 seconds, it simply ignores the wait. That's because the server uses "MinBPELWait" as the threshhold, the default is 2 sec.

Asych: works without any problem.

If you are too deep into your "synch" BPEL process, and doesn't want to start allover, just go to your WSDL, take out the "output" in your operation. Then go to your BPEL, take out the last reply. That converts your synch into "one-way", essentially it's almost the same as "asynch", you just don't have the callback operation.

Additionally, you can go clean out the synch response message etc. Or you can add callback in the WSDL to make it asynch.

Saturday, August 18, 2012

"Pure SQL" problem with JCA Adapter

I have wasted nearly a day chasing down a ghost problem with SOA Jca DB adapter. I thought I'll make a note of it here.

The database is SQLServer,we use SQLservre JDBC4.0 driver. I use "pure SQL" query for the adapter as following:

AND B.ACCOUNTING_DT <= convert(datetime,#date)
and A.TNDR_CTL_ST_FLG = '10' order by B.ACCOUNTING_DT desc

However, when I ran the BPEL test, it generated a nonsense error: 

oracle.sysman.emSDK.webservices.wsdlapi.SoapTestException: Client received SOAP Fault from server : Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'getTenderCtrlId' failed due to: Pure SQL Exception. Pure SQL Execute of SELECT top 1 A.TNDR_CTL_ID, B.ACCOUNTING_DT FROM PS_CI_TNDR_CTL A ,PS_CI_DEP_CTL B WHERE A.TNDR_SOURCE_CD = ? AND A.DEP_CTL_ID = B.DEP_CTL_ID AND B.ACCOUNTING_DT <= convert(datetime,?) and A.TNDR_CTL_ST_FLG = '10' order by B.ACCOUNTING_DT desc failed. Caused by java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Warning: Fatal error 823 occurred at Aug 17 2012 4:29PM

I initially thought this was an issue of multiple input parameters. So I stripped my query to the minimum as:


and I still get that out of range error. That's when I tested the inner join and found a working query.

The query below is logically equivalent to the original version, except this one uses "inner join". And this one works. 


Beats me!

Wednesday, August 15, 2012

Misc. items about MDS and JDEV

1. to publish to MDS from JDEV, check this:

remember: pay careful attention between "application" deployment and "project" deployment!

2. to delete from MDS, check this:

remember you can use wild card to delete.

3. when you create a SOA application, Jdev creates a ".adf" folder where your application .jws file resides. You can find your default adf-config.xml under the meta-inf of that folder. You can view it with a text editor directly, or you can open it with JDEV. With Jdev, you can expand "application resources" tab (default on the left, right below your "project" tab. Navigate to "descriptors -> adf meta-inf" and open adf-config.xml, then click "source" view to see the content.

There are two ways to store and access MDS data. Using DB via SOA server, or store locally in your file system.

In any event, by default, you may see something like

          <metadata-store-usage id="mstore-usage_1">
            <metadata-store class-name="oracle.mds.persistence.stores.file.FileMetadataStore">
              <property name="metadata-path"
              <property name="partition-name" value="seed"/>
be very careful, that if your JDEV is not installed under ${oracle.home}, then you won't be able to reference your local file MDS. So you know for sure where your JDEV is installed, you can find the absolute path to the "integration" folder, and replace "${oracle.home}/integration" with your absolute path (you may need to change "\" to "/" on windows.

Sharing common XSD's between OSB and SOA using MDS

Assume you have a collection of XSD's (canonical) need be shared between OSB and SOA.

Oracle recommends placing common artifacts in MDS. However, that syntax "oramds:/..." doesn't work for OSB.

I can think of two solutions:

1. Deploy your XSDs in MDS under "apps/xsd". The trick is make these XSD's accessible from OSB. Assume you have a SOA process "foo" deployed to SOA server, then you can access the above XSD like http://yourhost:8001/soa-infra/services/your-parition/foo/apps/xsd/myschema.xsd

Please note, "foo" can be a process that has nothing to do with your XSD file collections! In other words, you can use any surrogate SOA process to access MDS artifacts. All of the XSDs in MDS can be accessed this way.

2. place XSD's in OSB only, SOA access the XSDs with something like "http://your-host:your-port/sbresource?SCHEMA/your-proj-name/xsd-folder-name/xsd-resource-name". e.g. http://localhost:8011/sbresource?SCHEMA/canonical/xsd/Account

This approach works, keep in mind, the access to the following might be disabled via OSB configuration
if "sbresource?SCHEMA" access is disabled, then you may need to create a surrogate proxy that references these XSDs, then expose the XSDs via the proxy WSDL.

Another way to do it (not recommended) is to put the XSD file in both MDS and in OSB, There are many problems with this approach, duplicate files in two places can get out of synch. Additionally,you may run into problems with the following scenario:
 You have a BPEL process "foo" uses "oramds:/apps/xsd/myschema.xsd",
  and an OSB proxy "bar" uses the same XSD within OSB locally.
  SOA "foo" process invokes OSB "bar" proxy.
Now your "foo" process will "see" the "myschema.xsd" twice. Once via "oramds:/...", once via "bar" WSDL from OSB, which reference the same schema using  http://localhost/8011/sbresource?SCHEMA/testProj/xsd/myschema.xsd. Jdeveloper will complain about the duplicates and won't compile.

Tuesday, August 7, 2012

XSLT accessing DVM

While I'm at it, let me record it here. This is again a "re-learn" that cost me many hours.

I need to access DVM in my XSLT inside my BPEL process. I have created my DVM file (myfile.dvm) inside the project, so "myfile.dvm" sits in the project folder. The correct way to access the file in xslt is:

 <xsl:value-of select='dvm:lookupValue("myfile.dvm","column1-title", "value1","column2-title","default-for-column2")'/>

Here are the two things that cost me several hours to "re-learn":

1. JDEV UI has that tool you can test XSLT locally, it works great most of the time, but it DOES NOT work with DVM. So don't waste your time testing DVM in jdev locally. You have to deploy it to the server to run your test!

2. I thought my XSLT file sits in "xsl" subfolder, so I tried to access it as "../myfile.dvm" in my XSLT code. Well, that turned out to be wrong. After searching through my old code, I found out that you should access the file as "./myfile.dvm". That's it.