Sunday, August 26, 2012

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 "<". So you can not use this scrambled string for much of anything.

<messagePayload>&lt;cws:cwsPaymentEventService xmlns:cws="http://xmlns.calwater.com/cwsPaymentEvent.xsd"> &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@http://www.eclipse.org/eclipselink/xsds/persistence Location of invalid XML: <xml-fragment table="CIS_REQUEST_Q_TBL"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:eclipselink="http://www.eclipse.org/eclipselink/xsds/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
- Start tag of element <adapter-config>

After googling around, I found this post https://forums.oracle.com/forums/thread.jspa?threadID=2311913

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:

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 = #source
AND A.DEP_CTL_ID = B.DEP_CTL_ID
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:

SELECT 
A.TNDR_CTL_ID, B.ACCOUNTING_DT 
FROM 
PS_CI_TNDR_CTL A , PS_CI_DEP_CTL B


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. 

SELECT top 1 PS_CI_TNDR_CTL.TNDR_CTL_ID,
  PS_CI_DEP_CTL.ACCOUNTING_DT
FROM PS_CI_TNDR_CTL
INNER JOIN PS_CI_DEP_CTL
ON PS_CI_TNDR_CTL.DEP_CTL_ID        = PS_CI_DEP_CTL.DEP_CTL_ID
AND PS_CI_DEP_CTL.ACCOUNTING_DT    <= CONVERT(DATETIME,#dt)
WHERE PS_CI_TNDR_CTL.TNDR_SOURCE_CD = #source
AND PS_CI_TNDR_CTL.TNDR_CTL_ST_FLG  = '10'
ORDER BY PS_CI_DEP_CTL.ACCOUNTING_DT DESC

Beats me!


Wednesday, August 15, 2012

Misc. items about MDS and JDEV

1. to publish to MDS from JDEV, check this: http://yatanveersingh.blogspot.com/2011/07/mds-implementation-in-oracle-soa-11g.html

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

2. to delete from MDS, check this: http://neeraj-soa-tips.blogspot.com/2011/06/delete-data-from-mds-single-files.html

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"
                        value="${oracle.home}/integration"/>
              <property name="partition-name" value="seed"/>
            </metadata-store>
          </metadata-store-usage>
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
   http://localhost:8011/sbresource?SCHEMA/...
   http://localhost:8011/sbresource?WSDL/...
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.