Oracle-FMW

Thursday, May 9, 2013

Sorting xml based on a tag value

If we want to sort an xml based on a field's value, we can use the following approch

Say for e.g This is the source xml

<?xml version="1.0" encoding="UTF-8"?>
<OrganizationList>
   <Organization>
      <Name>Bharath</Name>
      <System>automotive</System>
      <main>Y</main>
   </Organization>
   <Organization>
      <Name>ATP</Name>
      <System>supply</System>
      <main>N</main>
   </Organization>
   <Organization>
      <Name>BSNL</Name>
      <System>service</System>
      <main>Y</main>
   </Organization>
</OrganizationList>

If we want to sort the above xml based on the value of main tag we can use the following approach

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" exclude-result-prefixes="xsl">
   <xsl:template match="node()">
      <xsl:copy>
         <xsl:apply-templates />
      </xsl:copy>
   </xsl:template>
   <xsl:template match="OrganizationList">
      <xsl:copy>
         <xsl:apply-templates>
            <xsl:sort order="descending" select="main" />
         </xsl:apply-templates>
      </xsl:copy>
   </xsl:template>
</xsl:stylesheet>

The result will be as shown below

<?xml version="1.0" encoding="UTF-8"?>
<OrganizationList>
   <Organization>
      <Name>Bharath</Name>
      <System>automotive</System>
      <main>Y</main>
   </Organization>
   <Organization>
      <Name>BSNL</Name>
      <System>service</System>
      <main>Y</main>
   </Organization>
   <Organization>
      <Name>ATP</Name>
      <System>supply</System>
      <main>N</main>
   </Organization>
</OrganizationList>

xquery : increment index in for each loop

Unlike java we cannot increment a variable's value in xquery while using for loop.

Say for e.g if we declare the following count variable in xquery and try to use it in the mapping, it will never work as we expect

let $count := 0
for $record at $position in $collection/books
let $count := $count + 1

return
<ns1:books>
<ns1:name>{ $record/name }</ns1:name>
<ns1:sequence>{ $count) }</ns1:sequence>
</ns1:books>

Instead we can use the following approach.

While declaring the for loop also declare a position variable.

Use this position variable in the place of count as follows

for $record at $position in $collection/books
return
<ns1:books>
<ns1:name>{ $record/name }</ns1:name>
<ns1:sequence>{ $position) }</ns1:sequence>
</ns1:books>

Monday, March 25, 2013

How to connect to a remote database using sqlplus

Often we might need to connect to remote database servers using sqlplus.

In such cases we can use the following connection string format

Open command prompt and execute the following line after giving proper values for

1)remotehostname
2)portnumber
3)sid
4)username
5)password

sqlplus username/password@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= remotehostname)(PORT=portnumber)))(CONNECT_DATA=(SID=sid)))'

Wednesday, March 13, 2013

OSB : Dynamically Change the end point for Business Service

There could be a requirement to change the endpoints dynamically while invoking external service from OSB.

This can be achieved by injecting the endpoint just before the service invocation.

Configure the endpoints in an .xq file as shown

Assign the content of xq file to a variable

In the message flow add an xpath expression and assign the target endpoint value to a variable

In the service-callout/publish/routing RequestAction block , add a Routing Option and update the URI using the above created variable

Table is mutating, trigger/function may not see it

If you write a trigger to act on the same table, we could end up with the following error.

table TRANSACTION is mutating, trigger/function may not see it

This is due to the trigger is trying to act on the record which is getting inserted.

So if use an AFTER INSERT trigger, it will always fail.

Instead we can use BEFORE INSERT construct as shown below.

Here the source application trying to insert a record with status value either PROCESSED or INPROCESS

But if you have a requirement to get the status as 'NOT PROCESSED' for your application to process that record, we can update the source record before it commits to DB

================ Trigger Start=============================

create or replace
TRIGGER "ORAUSER"."TRANSACTION_INSERT"
BEFORE INSERT
ON ORAUSER.TRANSACTION
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_event_seq NUMBER;
s_error VARCHAR2(256);

BEGIN

IF :new.STATUS ='PROCESSED' OR :new.STATUS ='INPROCESS' THEN
:new.STATUS :='NOT PROCESSED';
END IF;

EXCEPTION
WHEN OTHERS THEN
BEGIN

s_error := SUBSTR(TO_CHAR(SQLCODE)||' '||SQLERRM,1,256);
SELECT event_seq.NEXTVAL
INTO v_event_seq
FROM dual;
INSERT INTO ERROR_TABLE
VALUES (v_event_seq,s_error,:new.TRANSACTION_ID,'ERROR','TRANSACTION_INSERT',1,SYSDATE,0,NULL);

END;
END TRANSACTION_INSERT;

=================Trigger End==============================

DB Polling Issue in Cluster

DB Polling is always a challenging requirement. Often we encounter various issues with polling, be it not picking up the record or not updating the status. Some times we can see there are duplicate BPEL instances created for the same record.

The duplicate instances mainly happens in clustered environment where as each node will try to poll the same record.

This can be avoided in two ways

One by choosing the Distributed Polling option while configuring the DB Adapter. Once this is selected it means the record is locked by the reading instance. No other instance can read the record. In that way duplicate instances can be avoided

The second approach is to set a singleton parameter for the JCA in the composite.xml

<binding.jca config="triggerTransaction_db.jca">
<property name="singleton">true</property>
</binding.jca>

In this case too only one node will poll the record and duplicates will be avoided