Database Adapter: Hidden Treasure-1

Dealing with special characters

Problem: On working over one of my clients project I faced a peculiar issue. The issue was, I was trying to map my source column database(My case the database was AS/400) table to the destination column database(Oracle). My source table column name was containing $ signs like P$id,P$name.
This blog is maining written to deal with such problem.

Solution:I raised this issue on oracle forum and parallely I also did some research on this. What I came to know is not all special characters are allowed in XSD element/attribute names e.g. $, #, etc..
But finally I solved the problem.Let me show this with an example:

1) Create a dummy table with column name containing $ sign.

create table test(
p$id int,
p$name varchar2(20)
);

2) Create a BPEL or ESB project. Use database adapter in your project. You should be sure, what your project is going to do.

3) When the database adapter configuration is done. Goto the project folder and look for corresponding toplink and XSD files. We can find two files corresponding to it in our project folder.It will be basically <Service name>_table.xsd file and <Service name>_toplink_mappings.xml file.

4)Open the <Service name>_table.xsd file and change the element name for p$id and p$name to p_id and p_name. Save it.

5) Open the <Service name>_toplink_mappings.xml. Look for the tag <opm:attribute-mappings>. Only replace the "$" sign with an "_" for column name. I have made that text bold for better visibility.
Here I am pasting my piece of code.

<opm:attribute-mappings>
<opm:attribute-mapping xsi:type="toplink:direct-mapping">
<opm:attribute-name>p_id</opm:attribute-name>
<opm:field table="TEST" name="P$ID" xsi:type="opm:column"/>
<opm:attribute-classification>java.math.BigDecimal</opm:attribute-classification>
</opm:attribute-mapping>
<opm:attribute-mapping xsi:type="toplink:direct-mapping">
<opm:attribute-name>p_name</opm:attribute-name>
<opm:field table="TEST" name="P$NAME" xsi:type="opm:column"/>
<opm:attribute-classification>java.lang.String</opm:attribute-classification>
</opm:attribute-mapping>
</opm:attribute-mappings>

6) Save it. Thats it.

Note:
No toplink mapping is used in case you are dealing with custom SQL. Conversion from $ value to _ is done automatically. So when DB adapter creates XSD file, it creates attributes with p_id and p_name.

Happy Learning...

File Adapter : Hidden Treasure-1

Working on Logical Name Option of File adapter

This blog entry is for those who have worked with File Adapter a bit. In this blog I will mainly focus on showing you all the working of Logical Name option under Read operation for ESB.
We all are aware that while dealing with File Adapter Read operation, we can provide the input directory path in the form of either Physical Path or Logical Name.

ESB: The main problem in case of ESB is that we dont have files like bpel.xml to give details for the working of Logical Name.
Below I will show you how to deal with Logical Name with ESB.

1) Create an ESB project. Create a read file adapter and give Logical Name detail as shown below (Note: You can give Logical Name for Archive files also. Give different logical Names for incoming files and Archive. You cannot use same name)



Complete the read Adapter wizard.

2) Now automatically one routing service will be created by the wizard. Double click to open it.

3)The inbound file adapter window similar to the one shown below will be displayed with name like <SystemName_ServiceName>.esbsvc



4)When you click the plus sign (+) in Endpoint Properties section, the Endpoint Property Chooser dialog box will be displayed.



5) Select the InputDirectory property and click OK. Now you can specify the physical directory name in the Value field as shown below



6) Save it and proceed with your project.

Note: If you want to use Logical Name for Write operation of File Adapter same steps should be followed. Try it...

BPEL: If you are working on File Adapter Read operation and choosing the Logical Name option for Input Directory, you need to manipulate the bpel.xml file and provide the directory path there.

Thats it...You have learnt to use Logical Name option under File Adapter Read and write operation of ESB..

Happy Learning...

High Availability for Files

We can use the file adapter's high availability feature provided under SOA Suite 10.1.3.3. The following steps shown below will guide you to configure the file adapter for this feature:

1. Create a share folder on highly available file system.
Note:- This folder should have the write permission and should be accessible from all the systems that are running the file adapter.

2. Goto the following location:
SOA_HOME\bpel\system\service\config directory.

3. Open the file pc.properties.

4. Set oracle.tip.adapter.file.controldirpath to the shared folder name.
For example:
oracle.tip.adapter.file.controldirpath := Z:\\mysharedrive
or
oracle.tip.adapter.file.controldirpath := \\Myshare\Mydir

4. Restart the SOA Suite or Oracle BPEL Process Manager server.

Happy learning...

Future of Oracle SOA after BEA aquisition

Oracle's aquisition of BEA is making everybody curious. The obvious questions coming to our mind are: what about the future of SOA suite, whats new in SOA from Oracle etc.etc.
Seeing Oracle’s vision towards SOA, it has been clear for the last few years that Oracle is not going to leave it like that. According to me, Oracle SOA is gonna rock. For Oracle's aquisition of BEA, I must say: "Its a match made in Heaven"!

Highlights:

1) Oracle has integrated WebLogic into both JDeveloper and the Application Server, replacing OC4J. This will be good for performance and stability.

2) Oracle has announced convergence of Oracle ESB and AquaLogic Service Bus towards Oracle Service Bus (will contain strong points of both ESB and AquaLogic-SB).

3) Now JRockit, the world’s leading VM, will be the standard VM for Oracle.

4) Coherence will be the central component for dehydration. So we get zero latency in the VM and unparallelled performance for the dehydration store.

5) Integration of BPM Studio into the BPA Suite is the another good news. I must say that the Oracle BPA Suite, in conjunction with the Oracle SOA Suite (BPEL PM in particular) is the world’s best-of-breed for process management, design and execution. The added functionality from BPM Studio will create a comprehensive set of tools to support both process architecture, design and execution in a fully supported roundtrip engineering environment.

Playing with XSLT- Part 2

Hmmm..Again I am back with some more XSLT stuff.

Problem:
Importing some data from a .csv file(containing date as one field) and inserting it into the database.
If the date field (from csv file) is passed in the format 'YYYY-MM-DD', it works fine. But the problem comes when you try to pass the date value in format 'DD-MM-YYYY' or in any other formats.

Solution:
To explain the above case I have taken the format 'DD-MM-YYYY' as subject.
Weel well..so to acheive it you need to do is to create some embedded java code or use the following in the assign or transformation activity:(subsitute the tns:date with your variable)

concat(substring(tns:Date,orcl:last-index-within-string(tns:Date,"-") + 2.0,4.0),"-",substring(tns:Date,orcl:index-within-string(tns:Date,"-")+2,2.0),"-",substring-before(tns:Date,"-"))


Bingo.....

ORACLE AIA

Today instead of posting something on technical side, I thought of sharing something on Oracle AIA (again), which is the current buzz in the market.

FIRST LOOK:
Oracle AIA (Application Integration Architecture) is developed to widely-accepted open standards. Like everything new from Oracle, AIA revolves around the service-oriented architecture (SOA) concept. It thus represents a first step toward Fusion Applications – one that can be taken without the need for massive conversions or retraining. Hundreds of specific cases where sophisticated application integration is needed have already been identified.
Most IT environments, especially within larger organizations, consist of a variety of applications from a mix of vendors, not to mention custom–made software. It is increasingly necessary to automate business processes that cross the boundaries between these applications. However, creating solutions that obtain data and use the logic of multiple divergent applications tends to be time consuming and complex. Application Integration Architecture (AIA) is Oracle’s proposed approach to reducing the cost and complexity of doing so.

WHAT IS IT?
Let’s define AIA: AIA is a set of standards and rules by which Oracle and its partners can create elegant bridges between applications. Oracle will use AIA to insure that all of them are built the same way using a common approach and set of standards .This is a framework which contains the functionality of various ERP applications owned by Oracle like - Oracle E-Business, Siebel, JD Edwards and PeopleSoft which can be recombined and tailored to enterprise needs.

Technical products inside Application Integration Architecture (Till date*)
1. BPEL Process Manager
2. BPEL Repository
3. E-Business Suite
4. Siebel
5. Adapters
6. Oracle Warehouse Builder
**More in AIA framework regarding JDE and PeopleSoft will be added soon.

COMPONENTS OF AIA:
• Best practices Processes
• Foundation Pack
• PIP (Process Integration Packs)

DISCOVER THE TREASURE:
To make your AIA treasure hunt much easier, here I am providing you with some of the useful links:
• Oracle AIA:
http://www.oracle.com/applications/oracle-application-integration-architecture.html
• AIA Documentation:
http://www.oracle.com/technology/documentation/intarch.html
• PIPs:
http://www.oracle.com/applications/process-integration-packs.htm
• Oracle AIA Foundation Pack:
http://www.oracle.com/applications/oracle-foundation-pack.html
• List of validated integrations:
http://www.oracle.com/partnerships/isv/integration/search.html

Happy learning...

Playing with XSLT: Convert string to dateTime

Seems that most of us feel difficulty in dealing with date & time in XSLT.
PART-1
Problem: This post tells you about how to map a string source column to a date destination column.
Assumptions:
To acheive it I created dummy table in Database,
Table name: Test
SourceColumn name: a_str
SourceColumn Value: 20080514
Destination column name: a_dt

Solution:
Lets say,
XPath Expression: /top:testCollection/top:test/top:a_str=$d

To make the database understand about the date, we need to transform the source value. For that we need to use chain of functions.
Analyse the solution given below:

<ns2:a_dt>
<xsl:value-of select='concat(substring($d,1.0,4.0),"-",substring($d,5.0,2.0),"-",substring($d,7.0,2.0))'/>
</ns2:a_dt>

Bingo...It works.


PART-2
Problem: To map a string source column (containing date and time) to a date destination column.
Assumptions:
To acheive it I created dummy table in Database,
Table name: TestSource
Column name: a_strSource
Column Value: 20080514111213
Destination column name: a_dt


Solution:
Lets say,
XPath Expression: /top:testCollection/top:test/top:a_str=$d

To make the database understand about the date and Time, we need to transform the source value. For that we need to use chain of functions. Notice that a "T" is concatinated in the code. This one is for adding the Timezone.
Analyse the solution given below:

<ns2:a_dt>
<xsl:value-of select="concat(substring($d,1.0,4.0),"-",substring($d,5.0,2.0),"-",substring($d,7.0,2.0),'T',substring($d,9.0,2.0),":",substring($d,11.0,2.0),":",substring($d,13.0,2.0))"/>
</ns2:a_dt>

Bingo...It works.

XSLT:Examples of Date and Time Formatting-Part-2

Example: Non-Gregorian Calendars

The above example image is for calendars other than the Gregorian calendar.These examples use non-Latin characters which might not display correctly in all browsers, depending on the system configuration.(Source: w3c xslt doc)

XSLT:Examples of Date and Time Formatting

Example: Gregorian Calendar

The following image above shows a selection of dates and times and the way they might be formatted (Source: w3c transformation doc)