Wednesday Sep 11, 2013

What customers have to say about Oracle Data Integrator – Hear from Trendsetters in their Industries

We are just two short weeks away from Oracle OpenWorld 2013!

The Data Integration Product Management Team is excited to present a panel of Oracle Data Integrator customers during the event. I have the pleasure of hosting this session with guest speakers from Infinity Insurance, Paychex Inc., and Vertex Pharmaceuticals. Our guests will be sharing their experiences and successes with Oracle Data Integrator - Oracle’s flagship product for bulk/batch data movement and data transformation with high performance. Oracle Data Integrator delivers success in enterprise integration across a variety of scenarios such as business intelligence, data warehousing, SOA-based architectures, and many more. Come discover out how Infinity Insurance, Paychex Inc., and Vertex Pharmaceuticals utilize Oracle Data Integrator to meet both their IT and business needs with the fastest and most modern paradigm around!

Customer Perspectives: Oracle Data Integrator (CON8515)

Wednesday, September 25, 2013 at 1:15PM-2:15PM PT, Moscone West – 2012

We look forward to seeing you there!

For more customer related information, check out our Oracle Data Integration Resource Kit and, read a study done by Forrester, which explains how Oracle Data Integrator delivered an ROI of approximately 167% in under 8 months and improved batch running times by a factor of nine times: Forrester Economic Impact Study: Maximize ROI with High Performance, Low TCO Data Integration.

Lastly… don’t forget about our other Data Integration sessions at Oracle OpenWorld - we have a variety of topics and speakers … an overview can be found here.

Friday Sep 06, 2013

Sneak Peek into Data Integration Sessions at Oracle OpenWorld 2013

As we are getting ready for another great OpenWorld I'd like to give you sneak peek into what we have planned for data integration topics this year. As in the last few years, the Data Integration track is again full with great customer speakers, product updates, and hands-on-labs. While I am not able to share everything we will cover in these sessions I can say that you will get to hear about new solutions and new features for both Oracle Data Integrator and Oracle GoldenGate.

On Monday at 10:45am our VP of Development Brad Adelberg will open the data integration track by presenting the direction of Oracle Data Integrator and Oracle GoldenGate in the Accelerating Your Business with Oracle Data Integration Solutions (Moscone West 2024) session. Brad will explain how Oracle's data integration products enable organizations to get the most value out of big data,  their investments into Oracle Applications, and cloud deployments. The session will cover the key themes of the upcoming releases of our products.

Following Brad's great overview into overall Oracle Data Integration, GoldenGate product management will present  Oracle GoldenGate New Features and Product Strategy at 12:15pm in the same location. You will hear about new features of the product. Following GoldenGate PM, Oracle Data Integrator product management will present Oracle Data Integrator's product vision and new features in the session:
Oracle Data Integrator Product Update and Future Strategy at 1:45pm in the same room: Moscone West 2024.

The last session of Monday is a must-attend session. You will get to meet the winners of  the Oracle Excellence Awards for Oracle Fusion Middleware Innovation and learn about their impressive success stories across all Fusion Middleware products. I highly recommend adding Oracle Fusion Middleware: Meet This Year’s Most Impressive Innovators to your OpenWorld schedule. It will take place at 4:45pm  at Yerba Buena Center for the Arts, Lam Research Theater.

On Tuesday data quality product strategy sessions will kick off the day for our track with Oracle Enterprise Data Quality: Product Overview and Roadmap at 10:30am (Moscone West 2024) . Right after this session you will have a chance to hear from GoldenGate customers in the A Panel of Masterminds: Perspectives from Oracle GoldenGate Customers session at 3:30pm in the same room. This panel brings us presenters from Smuckers, Land o Lakes and OTV France. I will discuss this session in more detail next week. For now I will mention that customers will discuss how they achieved zero downtime upgrades and migrations and improved performance using GoldenGate with their Oracle Applications.

At the end of Tuesday you can attend another informative Oracle Data Integrator session about Extracting Strategic Insights from Business Data with Oracle Data Integrator (5:30pm, Moscone West 2012)

Wednesday starts with a great customer panel, Real-World Data Quality: Customers Using Oracle Enterprise Data Quality, at 10:15am in Moscone 2024. The must-attend session of Wednesday is Customer Perspectives: Oracle Data Integrator.  Paychex, Infiniti Insurance are among the customers presenting in this session and will be discussing best practices in implementing Oracle Data Integrator. Don't miss this session at 1:15pm in Moscone 2012.  I also recommend checking out at 3:30pm Making the Move to Oracle Data Integrator. ODI PM will be joined by Oracle partners Rittman Mead and sumIT AG. Together they will share great tips for a phased migration path from Oracle Warehouse Builder to Oracle Data Integrator.

Thursday is packed with many in-depth technical product deep dive sessions from experts. The two that I want to highlight here are: Configuring Oracle Business Intelligence Applications on Oracle Data Integrator: A Deep Dive, which will explain best practices for configuring and tuning Oracle Business Intelligence Applications' latest release which embeds Oracle Data Integrator.  This one is at 12:30pm at Moscone South 303. The second session I recommend, Deploying Oracle GoldenGate with Multitenant Container Databases, will share details on how to set up GoldenGate in the context of container databases (CDBs). You can attend it at 2pm at Moscone South 200.

Even though I listed 12 session in this blog post for you to review, there are many more, very informative sessions available this year at OpenWorld. Please make sure to review the Focus On Data Integration to discover other sessions that may interest you.

Stay tuned for more blogs on the some of the key sessions I mentioned above.

Friday Jul 26, 2013

The Best Data Integration for Oracle Exadata Comes from Oracle

In a previous blog post I talked about about how Oracle Exadata customers can migrate/consolidate their systems without downtime. In that blog post I mentioned that Oracle Data Integrator and Oracle GoldenGate offer unique and optimized data integration solutions for Oracle Exadata. For example, customers that choose to feed their data warehouse or reporting database with near real-time throughout the day, can do so without decreasing  performance or availability of source and target systems. And if you ask why real-time, the short answer is: in today’s fast-paced, always-on world, business decisions need to use more relevant, timely data to be able to act fast and seize opportunities. A longer response to "why real-time" question can be found in a related blog post.

If we look at the solution architecture, as shown on the diagram below,  Oracle Data Integrator and Oracle GoldenGate are both uniquely designed to take full advantage of the power of the database and to eliminate unnecessary middle-tier components. Oracle Data Integrator (ODI) is the best bulk data loading solution for Exadata. ODI is the only ETL platform that can leverage the full power of Exadata, integrate directly on the Exadata machine without any additional hardware, and by far provides the simplest setup and fastest overall performance on an Exadata system.

We regularly see customers achieving a 5-10 times boost when they move their ETL to ODI on Exadata. For  some companies the performance gain is even much higher. For example a large insurance company did a proof of concept comparing ODI vs a traditional ETL tool (one of the market leaders) on Exadata. The same process that was taking 5hrs and 11 minutes to complete using the competing ETL product took 7 minutes and 20 seconds with ODI. Oracle Data Integrator was 42 times faster than the conventional ETL when running on Exadata.This shows that Oracle's own data integration offering helps you to gain the most out of your Exadata investment with a truly optimized solution. 

GoldenGate is the best solution for streaming data from heterogeneous sources into Exadata in real time. Oracle GoldenGate can also be used together with Data Integrator for hybrid use cases that also demand non-invasive capture, high-speed real time replication. Oracle GoldenGate enables real-time data feeds from heterogeneous sources non-invasively, and delivers to the staging area on the target Exadata system. ODI runs directly on Exadata to use the database engine power to perform in-database transformations. Enterprise Data Quality is integrated with Oracle Data integrator and enables ODI to load trusted data into the data warehouse tables. Only Oracle can offer all these technical benefits wrapped into a single intelligence data warehouse solution that runs on Exadata.

Compared to traditional ETL with add-on CDC this solution offers:

  • Non-invasive data capture from heterogeneous sources and avoids any performance impact on source
  • No mid-tier; set based transformations use database power
  • Mini-batches throughout the day –or- bulk processing nightly which means maximum availability for the DW
  • Integrated solution with Enterprise Data Quality enables leveraging trusted data in the data warehouse

In addition to Starwood Hotels and Resorts, Morrison Supermarkets, United Kingdom’s fourth-largest food retailer, has seen the power of this solution for their new BI platform and shared their story with us. Morrisons needed to analyze data across a large number of manufacturing, warehousing, retail, and financial applications with the goal to achieve single view into operations for improved customer service. The retailer deployed Oracle GoldenGate and Oracle Data Integrator to bring new data into Oracle Exadata in near real-time and replicate the data into reporting structures within the data warehouse—extending visibility into operations. Using Oracle's data integration offering for Exadata, Morrisons produced financial reports in seconds, rather than minutes, and improved staff productivity and agility. You can read more about Morrison’s success story here and hear from Starwood here.

I also recommend you watch our on demand webcast on Zero-Downtime Migration to Oracle Exadata Using Oracle GoldenGate: A Customer Case Study and download free resources on Oracle Data Integration products to learn more about their powerful architecture and solutions for data-driven enterprises.

Friday Jun 21, 2013

What Comes Next After You Decide on Using Oracle Exadata

As Oracle Exadata continues to expand its footprint for both transaction and analytical processing, moving existing systems to Exadata and feeding it with enterprise data on an ongoing basis have become important discussion topics for Exadata customers. Consolidation and migration is the first step of this powerful journey with Exadata, and I'd like to start there in today's blog post.   

The systems that benefit from Exadata's extreme performance and reliability are typically business-critical systems that carry major risks when it comes to migration. Any downtime or data loss can have significant impact to the business in terms of revenue generation, customer loyalty, and productivity. As Oracle GoldenGate user community knows well, GoldenGate's heteregenous, real-time, and bidirectional replication capabilities enable very strong zero downtime migration and consolidation solutions for major databases and platforms including Oracle, IBM DB2 (zOS, iSeries, and LUW), HP NonStop, SQL Server, Sybase ASE, MySQL, and Teradata.

We discussed GoldenGate's zero downtime migration to Exadata offering and best practices with our customer IQNavigator in a webcast that is now available on demand:

Zero-Downtime Migration to Oracle Exadata Using Oracle GoldenGate: A Customer Case Study

If you have not watched it, I highly recommend listening to the discussion, as it clearly explains there should be no concerns around causing business interruption when moving to Oracle Exadata using GoldenGate.  GoldenGate's failback option to the old environment is a great tool for minimizing risk and many organizations adopt that approach for their business-critical systems.  

In addition to migration to Oracle Exadata, customers use GoldenGate, and Oracle Data Integrator, with Exadata in a variety ways leveraging the natural fit between these technologies:

  • Active-active database synchronization across the globe for data distribution, continuous availability, and zero downtime maintenance purposes.
  • Real-time or near real-time data loading to data warehouse, or consolidated database, on Oracle Exadata from heterogeneous sources. Oracle Data Integrator plays the major role in this use case as it integrates with GoldenGate and loads data warehouse in near real-time after performing transformations within the Exadata machine. This use case will be another blog topic soon as it is a strong best practice for performing ETL/ E-LT for Exadata.
  • Moving change data from an OLTP application running on Exadata in real time, for downstream consumption by other systems including supporting service integration.

As additional resources on best practices for migrating to Exadata I'd like to point you to couple of great white papers: Zero-Downtime Migration to Oracle Exadata Using Oracle GoldenGate and Oracle GoldenGate on Exadata Database Machine.

Wednesday Oct 17, 2012

And the Winners of Fusion Middleware Innovation Awards in Data Integration are…

At OpenWorld, we announced the winners of Fusion Middleware Innovation Awards 2012. Raymond James and Morrison Supermarkets were selected for the data integration category for their innovative use of Oracle’s data integration products and the great results they have achieved.

In this blog I would like to briefly introduce you to these award winning projects.

Raymond James is a diversified financial services company, which provides financial planning, wealth management, investment banking, and asset management. They are using Oracle GoldenGate and Oracle Data Integrator to feed their operational data store (ODS), which supports application services across the enterprise. A major requirement for their project was low data latency, as key decisions are made based on the data in the ODS. They were able to fulfill this requirement due to the Oracle Data Integrator’s integrated solution with Oracle GoldenGate.

Oracle GoldenGate captures changed data from different systems including Oracle Database, HP NonStop and Microsoft SQL Server into a single data store on SQL Server 2008. Oracle Data Integrator provides data transformations for the ODS. Leveraging ODI’s integration with GoldenGate, Raymond James now sees a 9 second median latency (from source commit to ODS target commit).

The ODS solution delivers high quality, accurate data for consuming applications such as Raymond James’ next generation client and portfolio management systems as well as real-time operational reporting. It enables timely information for making better decisions.

There are more benefits Raymond James achieved with this implementation of Oracle’s data integration solution. The software developers and architects of this solution, Tim Garrod and Ryan Fonnett, have told us during their presentation at OpenWorld that they also reduced application complexity significantly while improving developer productivity through trusted operational services. They were able to utilize CDC to generate alerts for business users, and for applications (for example for cache hydration mechanisms).

One cool innovation example among many in this project is that using ODI's flexible architecture, Tim and Ryan could build 24/7 self-healing processes. And these processes have hardly failed. Integration process fixes the errors itself. Pretty amazing; and a great solution for environments that need such reliability and availability. (You can see Tim and Ryan’s photo with the Innovation Awards trophy above.)

The other winner of this year in the data integration category, Morrison Supermarkets, is the UK’s 4th largest grocery retailer. The company has been migrating all their legacy applications on to a new-world application set based on Oracle and consolidating all BI on to a single Oracle platform. The company recently implemented Oracle Exadata as the data warehouse engine and uses Oracle Business Intelligence EE.

Their goal with deploying GoldenGate and ODI was to provide BI data to the enterprise in a way that it also supports operational decision making requirements from a wide range of Oracle based ERP applications such as E-Business Suite, PeopleSoft, Oracle Retail Suite. They use GoldenGate’s log-based change data capture capabilities and Oracle Data Integrator to populate the Oracle Retail Data Model. The electronic point of sale (EPOS) integration solution they built processes over 80 million transactions/day at busy periods in near real time (15 mins). It provides valuable insight to Retail and Commercial teams for both intra-day and historical trend analysis.

As I mentioned in yesterday’s blog, the right data integration platform can transform the business. Here is another example: The point-of-sale integration enabled the grocery chain to optimize its stock management, leading to another award: Morrisons won the Grocer 33 award in 2012 - beating all other major UK supermarkets in product availability. Congratulations, Morrisons,on another award!

Celebrating the innovation and the success of our customers with Oracle’s data integration products was definitely a highlight of Oracle OpenWorld for me. I look forward to hearing more from Raymond James, Morrisons, and the other customers that presented their data integration projects at OpenWorld, on how they are creating more value for their organizations.

Monday Oct 08, 2012

The most challenging part of blogging about OpenWorld is…

...not knowing where to start. Do I talk about the great presentations from our partners and executives in our keynote sessions; do I write about the music festival, or many great sessions we had in the Data integration track? A short blog can never do justice. For now I will stick to our data integration sessions for those who could not attend with so many other sessions running concurrently. And in the coming weeks we will be writing more about what we talked in our sessions and what we learned from our customers and partners.

For today, I will give some of the key highlights from Data Integration sessions that took place on Wednesday and Thursday of last week  On Wednesday, GoldenGate was highlighted in multiple Database and Data Integration sessions. I found particularly the session about Oracle’s own use of GoldenGate for its large E-Business Suite implementation for supply chain management and service contract management very interesting. In 2011, Oracle implemented a new operational reporting system using GoldenGate real-time data replication to an operational data store that leverages data from E-Business Suite.The results are very impressive. Mark Field, VP of Applications Development in the IT organization shared with us that data freshness improved by 2,210X while report run performance improved by 60X. For more information on this implementation and its results please see the white paper: Real-Time Operational Reporting for E-Business Suite via GoldenGate Replication to an Operational Data Store

Other sessions that provided very rich content were: "Best Practices for Conflict Detection and Resolution in Oracle GoldenGate for Active/Active", "Tuning and Troubleshooting Oracle GoldenGate on Oracle Database", "Next-Generation Data Integration on Oracle Exadata" and "Accelerate Oracle Data Integrator with Advanced Features, SOA, Groovy, SDK, and XML". Below is a slide presented by Stephan Haisley in the Tuning and Troubleshooting Oracle GoldenGate session. If you missed them during OpenWorld, I highly recommend downloading the slides. We will continue to blog about these topics and related resources.


Wednesday Sep 26, 2012

Data-Driven SOA with Oracle Data Integrator

By Mike Eisterer,

Data integration is more than simply moving data in bulk or in real-time, it is also about unifying information for improved business agility and integrating it in today’s service-oriented architectures. SOA enables organizations to easily define services which may then be discovered and leveraged by varying consumers. These consumers may be applications, customer facing portals, or complex business rules which are assembling services to automate process. Data as a foundational service provider is a key component of today’s successful SOA implementations.

Oracle offers the broadest and most integrated portfolio of products to help you define, organize, orchestrate and consume data services.

If you are attending Oracle OpenWorld next week, you will have ample opportunity to see the latest Oracle Data Integrator live in action and work with it yourself in two offered Hands-on Labs. Visit the hands-on lab to gain experience firsthand:



Oracle Data Integrator and Oracle SOA Suite: Hands-on- Lab (HOL10480)

Wed Oct 3rd 11:45AM Marriott Marquis- Salon 1/2


To learn more about Oracle Data Integrator, please visit our Introduction Hands-on LAB:

Introduction to Oracle Data Integrator (HOL10481)

Mon Oct 1st 3:15PM, Marriott Marquis- Salon 1/2

If you are not able to attend OpenWorld, please check out our latest resources for Data Integration.

Tuesday Sep 11, 2012

Oracle Data Integrator at Oracle OpenWorld 2012: Sessions, Demos and Hands-On Labs

 By Mike Eisterer

 Oracle OpenWorld is just a few weeks away and the Oracle Data Integrator team would like to introduce you to the sessions, demos and hands-on labs we will be offering this year. We will be out in force at the show with four demo pods and two hands-on labs, plus numerous speaking sessions.

Sessions this year will provide valuable information towards the use and direction of Oracle Data Integration solutions, including:

  • Tackling Big Data Analytics with Oracle Data Integrator, October 1, 2012 - 12:15 PM, at Moscone West – 3005
  • Real-Time Data Integration with Oracle Data Integrator, October 1, 2012 - 4:45 PM, at Raymond James Moscone West – 3005
  • Future Strategy, Direction, and Roadmap of Oracle’s Data Integration Platform, October 1, 2012 - 10:45 AM, at Moscone West – 3005
  • Customer Perspectives: Oracle Data Integrator Marriott Marquis, October 3, 2012 - 1:15 PM, at Marriot Marquis GoldenGate C3
 To see the full list of sessions for data integration topic please check out our Focus-on for Data Integration.

Demos this year will be running Monday through Wednesday in Moscone South and we will be showcasing:

· Oracle Data Integrator for Big Data (Moscone South, S-236)

· Oracle Data Integrator for Enterprise Data Warehousing (Moscone South, S-238)

· Oracle Data Integrator and Service Integration (Moscone South, S-235)

· Oracle Data Integrator and Oracle GoldenGate for Oracle Applications (Moscone South, S-240)

Hands-on labs will feature instructor lead exercises providing direct experience with Oracle Data Integrator, including:

· “Introduction to Oracle Data Integrator” where students will learn how to define sources and create mappings to extract, load and transform data.

· “Oracle Data Integrator and Oracle SOA Suite” where students will define integration flows as web services, access web services as a transformation and integrate ODI sessions into a BPEL process.

If you are not able to attend OpenWorld, please check out our latest resources for Data Integration.

In the coming weeks you will see more blogs about our products’ new capabilities and what to expect at OpenWorld.

 I hope to see you at OpenWorld and stay in touch via our future blogs.

Sunday Jul 22, 2012

Is Big Data just Super Sexy Batch?

One of the key expectations we have for big data and our information architecture is to yield faster, better and more insightful analytics. That appeal of processing so much information quickly is why the Hadoop technologies may have originally been invented. But is it nothing more than a super sexy batch? Yes – on sexy. But there’s definitely an important real-time element involved. Read the rest of the article to see more on our take on the intersection of batch, real-time, big data, and business analytics. [Read More]

Friday Jun 01, 2012

Looking for the latest Data in Integration Reading? Read on..

Looking for some exciting reading on data integration?  With the hundreds of books in the market on data integration, there really isn't a book that is heads-down, focused on Oracle Data Integrator.  Not any more.  Recently published is the first book truly dedicated to Oracle Data Integrator.  The title of the book, which is published by Packt Publishing is:

Getting Started with Oracle Data Integrator 11g – A Hands-On Tutorial
Authors: Peter C. Boyd-Bowman, Christophe Dupupet, Denis Gray, David Hecksel,  Julien Testut, Bernard Wheeler

I would like to extend my hearty Congratulations to everyone who contributed to this book!

You can get more information about 'Getting Started with Oracle Data Integrator 11g – A Hands-On Tutorial' including the table of contents and a sample chapter at

The book is now available on,, Barnes & Noble and Safari Books Online. Order your copy today!

Wednesday Mar 14, 2012

New Feature in ODI Smart Export and Import

By Jayant Mahto

Oracle Data Integrator introduces a major new feature called Smart Export and Import. This post will give you an overview of this feature.

ODI export and import feature has been used in previous releases to move ODI objects in and out of ODI repository. Smart Export and Import builds on top of the existing ODI capabilities to avoid common pitfalls and guide end users through the process.

[Read More]

Monday Jun 20, 2011

Oracle Data Integrator Complex Files as Sources and Targets


ODI adds the new Complex File technology for use with file sources and targets. The goal is to read or write file structures that are too complex to be parsed using the existing ODI File technology. This includes:

    • Different record types in one list that use different parsing rules
    • Hierarchical lists, for example customers with nested orders
    • Parsing instructions in the file data, such as delimiter types, field lengths, type identifiers
    • Complex headers such as multiple header lines or parseable information in header
    • Skipping of lines
    • Conditional or choice fields

Similar to the ODI File and XML File technologies, the complex file parsing is done through a JDBC driver that exposes the flat file as relational table structures. Complex files are mapped to one or more table structures, as opposed to the (simple) file technology, which always has a one-to-one relationship between file and table. The resulting set of tables follows the same concept as the ODI XML driver, table rows have additional PK-FK relationships to express hierarchy as well as order values to maintain the file order in the resulting table.


The parsing instruction format used for complex files is the nXSD (native XSD) format that is already in use with Oracle BPEL. This format extends the XML Schema standard by adding additional parsing instructions to each element. Using nXSD parsing technology, the native file is converted into an internal XML format. It is important to understand that the XML is streamed to improve performance; there is no size limitation of the native file based on memory size, the XML data is never fully materialized. The internal XML is then converted to relational schema using the same mapping rules as the ODI XML driver.

How to Create an nXSD file

Complex file models depend on the nXSD schema for the given file. This nXSD file has to be created using a text editor or the Native Format Builder Wizard that is part of Oracle BPEL. BPEL is included in the ODI Suite, but not in standalone ODI Enterprise Edition. The nXSD format extends the standard XSD format through nxsd attributes. NXSD is a valid XML Schema, since the XSD standard allows extra attributes with their own namespaces.

The following is a sample NXSD schema blog.xsd:

<?xml version="1.0"?>

<xsd:schema xmlns:xsd="" xmlns:nxsd="" elementFormDefault="qualified" xmlns:tns="" targetNamespace="" attributeFormDefault="unqualified"

nxsd:encoding="US-ASCII" nxsd:stream="chars" nxsd:version="NXSD">

<xsd:element name="Root">


<xsd:element name="Header">


<xsd:element name="Branch" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy=","/>

<xsd:element name="ListDate" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}"/>



<xsd:element name="Customer" maxOccurs="unbounded">


<xsd:element name="Name" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy=","/>

<xsd:element name="Street" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," />

<xsd:element name="City" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" />






The nXSD schema annotates elements to describe their position and delimiters within the flat text file. The schema above uses almost exclusively the nxsd:terminatedBy instruction to look for the next terminator chars. There are various constructs in nXSD to parse fixed length fields, look ahead in the document for string occurences, perform conditional logic, use variables to remember state, and many more.

nXSD files can either be written manually using an XML Schema Editor or created using the Native Format Builder Wizard. Both Native Format Builder Wizard as well as the nXSD language are described in the Application Server Adapter Users Guide. The way to start the Native Format Builder in BPEL is to create a new File Adapter; in step 8 of the Adapter Configuration Wizard a new Schema for Native Format can be created:


The Native Format Builder guides through a number of steps to generate the nXSD based on a sample native file. If the format is complex, it is often a good idea to “approximate” it with a similar simple format and then add the complex components manually. The resulting *.xsd file can be copied and used as the format for ODI, other BPEL constructs such as the file adapter definition are not relevant for ODI. Using this technique it is also possible to parse the same file format in SOA Suite and ODI, for example using SOA for small real-time messages, and ODI for large batches.

This nXSD schema in this example describes a file with a header row containing data and 3 string fields per row delimited by commas, for example blog.dat:

Redwood City Downtown Branch, 06/01/2011
Ebeneezer Scrooge, Sandy Lane, Atherton
Tiny Tim, Winton Terrace, Menlo Park

The ODI Complex File JDBC driver exposes the file structure through a set of relational tables with PK-FK relationships. The tables for this example are:

Table ROOT (1 row):


Primary Key for root element


Name of the file


Path of the file


Date of load

Table HEADER (1 row):


Foreign Key to ROOT record


Order of row in native document




Order of Branch within row




Order of ListDate within row

Table CUSTOMER (2 rows):


Foreign Key to ROOT record


Order of rows in native document




Oder of Name within row




Order of Street within row




Order of City within row

Every table has PK and/or FK fields to reflect the document hierarchy through relationships. In this example this is trivial since the HEADER and all CUSTOMER records point back to the PK of ROOT. Deeper nested documents require this to identify parent elements. All child element tables also have a order field (HEADERORDER, CUSTOMERORDER) to define the order of rows, as well as order fields for each column, in case the order of columns varies in the original document and needs to be maintained. If order is not relevant, these fields can be ignored.

How to Create an Complex File Data Server in ODI

After creating the nXSD file and a test data file, and storing it on the local file system accessible to ODI, you can go to the ODI Topology Navigator to create a Data Server and Physical Schema under the Complex File technology.


This technology follows the conventions of other ODI technologies and is very similar to the XML technology. The parsing settings such as the source native file, the nXSD schema file, the root element, as well as the external database can be set in the JDBC URL:


The use of an external database defined by dbprops is optional, but is strongly recommended for production use. Ideally, the staging database should be used for this. Also, when using a complex file exclusively for read purposes, it is recommended to use the ro=true property to ensure the file is not unnecessarily synchronized back from the database when the connection is closed. A data file is always required to be present at the filename path during design-time. Without this file, operations like testing the connection, reading the model data, or reverse engineering the model will fail.

All properties of the Complex File JDBC Driver are documented in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator in Appendix C: Oracle Data Integrator Driver for Complex Files Reference.

David Allan has created a great viewlet Complex File Processing - 0 to 60 which shows the creation of a Complex File data server as well as a model based on this server.

How to Create Models based on an Complex File Schema

Once physical schema and logical schema have been created, the Complex File can be used to create a Model as if it were based on a database. When reverse-engineering the Model, data stores(tables) for each XSD element of complex type will be created. Use of complex files as sources is straightforward; when using them as targets it has to be made sure that all dependent tables have matching PK-FK pairs; the same applies to the XML driver as well.

Debugging and Error Handling

There are different ways to test an nXSD file. The Native Format Builder Wizard can be used even if the nXSD wasn’t created in it; it will show issues related to the schema and/or test data. In ODI, the nXSD will be parsed and run against the existing test XML file when testing a connection in the Dataserver. If either the nXSD has an error or the data is non-compliant to the schema, an error will be displayed.

Sample error message:

Error while reading native data.
[Line=1, Col=5] Not enough data available in the input, when trying to read data of length "19" for "element with name D1" from the specified position, using "style" as "fixedLength" and "length" as "". Ensure that there is enough data from the specified position in the input.

Complex File FAQ

Is the size of the native file limited by available memory?
No, since the native data is streamed through the driver, only the available space in the staging database limits the size of the data. There are limits on individual field sizes, though; a single large object field needs to fit in memory.

Should I always use the complex file driver instead of the file driver in ODI now?
No, use the file technology for all simple file parsing tasks, for example any fixed-length or delimited files that just have one row format and can be mapped into a simple table. Because of its narrow assumptions the ODI file driver is easy to configure within ODI and can stream file data without writing it into a database. The complex file driver should be used whenever the use case cannot be handled through the file driver.

Should I use the complex file driver to parse standard file formats such as EDI, HL7, FIX, SWIFT, etc.? 
The complex file driver is technically able to parse most standard file formats, the user would have to develop an nXSD to parse the expected message. However, in some instances the use case requires a supporting infrastructure, such as message validation, acknowledgement messages, routing rules, etc. In these cases products such as Oracle B2B or  Oracle Service Bus for Financial Services will be better suited and could be combined with ODI.

Are we generating XML out of flat files before we write it into a database?
We don’t materialize any XML as part of parsing a flat file, either in memory or on disk. The data produced by the XML parser is streamed in Java objects that just use XSD-derived nXSD schema as its type system. We use the nXSD schema because is the standard for describing complex flat file metadata in Oracle Fusion Middleware, and enables users to share schemas across products.

Is the nXSD file interchangeable with SOA Suite?
Yes, ODI can use the same nXSD files as SOA Suite, allowing mixed use cases with the same data format.

Can I start the Native Format Builder from the ODI Studio?
No, the Native Format Builder has to be started from a JDeveloper with BPEL instance. You can get BPEL as part of the SOA Suite bundle. Users without SOA Suite can manually develop nXSD files using XSD editors.

When is the database data written back to the native file?
Data is synchronized using the SYNCHRONIZE and CREATE FILE commands, and when the JDBC connection is closed. It is recommended to set the ro or read_only property to true when a file is exclusively used for reading so that no unnecessary write-backs occur.

Is the nXSD metadata part of the ODI Master or Work Repository?
No, the data server definition in the master repository only contains the JDBC URL with file paths; the nXSD files have to be accessible on the file systems where the JDBC driver is executed during production, either by copying or by using a network file system.

Where can I find sample nXSD files?
The Application Server Adapter Users Guide contains nXSD samples for various different use cases.

Friday Jun 03, 2011

Oracle Data Integrator - Key to Success on Data Integration Projects

Data is the lifeline of any enterprise.  Now, figuring out how to integrate that data so that it is available to you in the systems that require it -- well, that's a different story.  Data integration projects are notoriously known for being challenging due to:  multiple siloed systems, plethora of home-grown systems and interfaces that were created eons ago, mismatched data formats, different souce and target database and this is just a few of the issues.

Oracle Data Integrator was designed to to help tackle these issues and help maximize success on data integration projects. Built and designed using an ELT architecture, ODI is the most complete solution for bulk data movement and data transformation.  High performance, heterogeneity and developer productivity, reduced cost and faster time-to-value are just a sampling of what ODI has to offer.  Take a look at the latest whitepaper on what makes data integration projects successful and how Oracle Data Integrator is integral to helping organizations trust that their data is where it needs to be while saving time, money and extending their existing IT investments. Read here

Tuesday May 24, 2011

What’s New in the Data Integration Market?

Data integration is essential to many strategic IT initiatives including master data management (MDM), modernization, and service-oriented architecture (SOA). That’s why it is not surprising that the data integration market continues to grow in its size, importance and impact on enterprise information systems. The technology has evolved from simple extract, transform, and load (ETL) procedures to include real time data movement, bi-directional replication/synchronization along with data quality and data profiling and data services.

 The user base for data integration tools has also evolved from low-level technology for developers and DBAs to high-level environments for operations staff, data stewards, business analysts and enterprise architects. In short, what was once merely the background “plumbing” underlying our information systems now influences just about every aspect of IT, and touches just about every type of stakeholder.

 In a new white paper we have examined the state of the data integration market including the trends in business intelligence, data warehousing, data quality, consolidation, cloud computing and IT modernization initiatives that are driving data-intensive projects. You can access this brand new white paper along with other data integration focused white papers and resources here.

Sunday Apr 17, 2011

Make Oracle Your Choice for ETL

[Read More]

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« March 2015