Data Integration Suite – Oracle Database Support

Querying Oracle relational databases as XML just got a whole lot easier. With the Data Integration Suite, developers treat relational data in Oracle tables and views as XML, making querying, publishing, reporting – and even updating – relational data a snap. The Data Integration Suite runs on any Java platform, and is easily embeddable into almost any Java program. The Data Integration Suite supports multiple versions of Oracle database including:

  • Oracle 11g
  • Oracle 10g (R1 and R2)
  • Oracle 9i (R1 and R2)

Efficient Query Execution

Oracle relational databases often hold massive amounts of data, so the Data Integration Suite uses a very efficient way to query them – but hides this from the query author. Using the Data Integration Suite:

  • To the Data Integration Suite XQuery, a table looks like an XML document
  • To the Oracle relational database, a Data Integration Suite XQuery looks like efficient SQL

Data is not extracted in order to query it; instead, the Data Integration Suite XQuery generates SQL and executes that SQL on the server, enabling developers to retrieve just the data needed to construct the results for a given Data Integration Suite XQuery.

Accessing an Oracle Relational Database as XML – Click to Enlarge

SQL Optimizations

One key advantage of Data Integration Suite over other XQuery processors is that it decomposes XQuery and translates it to SQL. The benefits of such a translation, of course, are enormous, especially in terms of the performance and efficiency with which XQuery applications will run. Once converted to SQL, Data Integration Suite pushes the query to the Oracle database server for execution. In the database environment, the SQL is able to take advantages of the Oracle database's indexes and primary key/foreign key relationships, speeding data access and retrieval.

Other XQuery processors are unable to convert XQuery to SQL, leaving them with no choice but to fetch the entire table from the Oracle database and return it to the client side, for conversion, in its entirety, to XML. This might work fine with trivial queries or very small relational tables, but it is not an approach that scales, or even one that offers reasonable performance for real-world data integration and aggregation.

XML Optimizations

Query results are returned to Data Integration Suite as tabular data for conversion to XML. Once converted to XML, Data Integration Suite uses data streaming and document projection technologies to focus on only those elements and attributes that are relevant to the query, minimizing in-memory processing. This approach allows for fast and efficient processing of very large XML documents (1 gigabyte and larger!). Relational joins and other complex query structures pose no problem for the Data Integration Suite processor.

Easy Oracle Database Connectivity

With Data Integration Suite, configuring and connecting to an Oracle server is easy. The Data Integration Suite integrated development environment quickly defines server configurations and connects to that server, using a simple dialog box like the one shown here:

Defining an Oracle Connection String

And it's easy to create and reuse default database connections, too. Even without an XML IDE like Data Integration Suite, defining Oracle database connections is easily accomplished using the Data Integration Suite API for Java (XQJ), whose interfaces are implemented by Data Integration Suite. Developers simply need to define data source and connection expressions in the Java application, as shown in the following code sample:

Connecting to Oracle from Java

Transparent Access to Oracle Relational Data as XML

Once developers have connected to an Oracle relational data source, Data Integration Suite makes it easy to automatically create XQuery code. Using Data Integration Suite, for example, just drag and drop a table from the File Explorer window, exposing all the tables from that tablespace as potential data sources. Next, drag the desired table to query and drop it on the XQuery Source tab – Data Integration Suite automatically creates perfect XQuery code based on the chosen table. These easy steps are summarized in the following illustration:

Connecting to Oracle from Java – Click to Enlarge

And when done building and testing XQuery code, Data Integration Suite quickly generates Java code that can be embedded in a Java application!

Check out the Data Integration Suite Performance: Generating SQL or download a free trial of the Data Integration Suite today!