Exult Oracle User Guide

Table of Contents

1. Introduction
1.1. Benefits of using Exult Oracle
1.2. Shredding XML vs Raw XML Import
1.3. Importing Data from Multiple XML Files
1.4. Processing Large XML Files
1.5. Complex XML Files
1.6. Batch Processing
1.7. Update and Delete Imported Data
2. Using Exult Oracle
2.1. Prerequisites
2.1.1. Oracle Client Installation
2.1.2. Using TNSNAMES.ORA
2.2. Usage Summary
2.3. The Welcome Page
2.4. The Trial Version Page
2.5. The Registration Page
2.6. The Connection Page
2.6.1. Connection Strings
2.7. The Database Actions Page
2.8. The XML File Selection Page
2.8.1. Simple File Selection Box

List of Figures

2.1. The Welcome Page
2.2. The Trial Version page
2.3. The Registration Page
2.4. The Connection Page
2.5. Connection Error
2.6. Login Error
2.7. The Actions Page


Chapter 1. Introduction

Exult Oracle is an easy-to-use Wizard Application for importing data from XML files into an Oracle Database. Exult Oracle automatically shreds the XML file into tables and columns depending on the XML structure. The tables and columns are created as required and data is imported into these tables. Since the data is shredded it is also very easy for legacy downstream applications to consume the XML data.


1.1. Benefits of using Exult Oracle

  • Easy import of XML data into Oracle.
  • XML is shredded into relational data and makes it easy to query and update using SQL.
  • Merge data from multiple XML files.
  • Handles arbitrarily complex XML without problems.
  • Optimized for large XML files.
  • Includes a Command Line interface for easing scheduled XML import.


1.2. Shredding XML vs Raw XML Import

Recent versions of Oracle databases provide facilities for importing XML into the database and using XQuery/XPath to query and extract data from XML. In contrast to this approach, Exult Oracle processes an XML file and builds regular relational tables with relationships among these tables depending on the XML structure. While the former approach might be more suitable for some usage scenarios, shredding the XML into tables is also useful in other cases. Please decide which approach you would like to take to process your XML before proceeding further.


1.3. Importing Data from Multiple XML Files

Exult Oracle can import data from multiple XML files in a similar way. When importing multiple XML files, existing tables are re-used for populating the database. This provides a way for combining data from multiple XML files and using this data in SQL queries and updates.


1.4. Processing Large XML Files

Exult Oracle uses a streaming approach to process an XML file which makes it possible to process large XML files without running into memory issues on the database server. Exult Oracle has been specially tuned for performance when importing large XML files into the database.


1.5. Complex XML Files

While some approaches to importing XML data into Oracle may fail due to the complexity of the XML, Exult Oracle has no problems with importing arbitrarily complex XML. The XML is processed and parent-child relationships are mirrored using primary and foreign keys in the database.


1.6. Batch Processing

Exult Oracle includes a command line program in the distribution which enables you to import XML files into the database on a schedule. It is easy to create a Windows Scheduler task using Exult Oracle Command Line to import the data into Oracle.


1.7. Update and Delete Imported Data

Exult Oracle includes the ability to delete all data from one or more imported XML files. This makes it easy to update (or refresh) data from updated XML. Just delete the data from previously imported XML and re-import from updated XML.


Chapter 2. Using Exult Oracle


2.1. Prerequisites

The following pre-requisites are required for using Exult Oracle.

  • An Oracle client installation is required on the computer where Exult Oracle is being used.
  • Access to an Oracle database.
  • Username and password for connecting to Oracle.

2.1.1. Oracle Client Installation

The Oracle client libraries must be installed on the computer where Exult Oracle is being used. The client installation could be an Oracle Instant Client installation or a full-fledged ORACLE_HOME installation. You can download the Oracle Instant Client libraries from Oracle’s site at: http://www.oracle.com/technetwork/database/features/instant-client/index.html.

You could also have a full fledged ORACLE_HOME installation on the computer and use the client libraries from that installtion. For this please make sure ORACLE_HOME environment variable is set to the correct value before running Exult Oracle.

2.1.2. Using TNSNAMES.ORA

To use TNSNAMES.ORA to locate an Oracle database to connect, please make sure that TNS_ADMIN or ORACLE_HOME is set appropriately as follows:

To use TNS_ADMIN, set this environment variable to point to the directory where TNSNAMES.ORA is located.

To use ORACLE_HOME, set this variable to point to the ORACLE_HOME location. TNSNAMES.ORA is then expected to found under the directory: $ORACLE_HOME/network/admin.

Once TNSNAMES.ORA is located, you can use the entries specified therein to specify a connection to the Oracle database.


2.2. Usage Summary

Exult Oracle is a wizard application which takes you through the steps needed to import XML data into Oracle. The wizard application has Next and Back buttons which allow forward and backward navigation. Each step consists of a wizard page which requires interactions from the user. Once all the required input is collected, the XML data is then imported. The following is a summary of using Exult Oracle.

  • The Welcome page is the first screen shown in the application.


2.3. The Welcome Page

The welcome page is the first screen shown by the application. In addition to a summary of the capabilities of the software, it also shows the software version ( [image] ) which is required to be quoted when requesting support.

Figure 2.1. The Welcome Page


2.4. The Trial Version Page

The Trial version screen is shown in the trial version of the software and shows the amount of trial period left. For user convenience the trial period is the later of a certain period such as 15 days or number of uses left ( [image] ). For example suppose a user installs the software and uses it once or twice and then is unable to use it again for a month. Based on trial period the software would have expired. However since the number of allowed uses has not been reached, the user can still use the software.

Figure 2.2. The Trial Version page

In addition to showing the trial period information, the screen also provides a link ( [image] ) to allow the user to purchase a license to use the software beyond the trial period.


2.5. The Registration Page

Once the trial period expires, you will not be able to use the software until a valid name and license key is entered. The name and license key is issued after the purchase of the software is completed. The Registration page is used to enter the name and the license key to be able to use the software beyond the trial period.

Figure 2.3. The Registration Page

In some cases, the allowed trial period is not sufficient to fully evaluate the software. For these cases, we issue a trial extension key on special request. The name/key combination must be entered here to continue the evaluation.


2.6. The Connection Page

Figure 2.4. The Connection Page

The Connection page is where the connection parameters for connecting to the database is entered and a connection performed.

  • An optional connection name is entered ( [image] ) for saving the connection parameters. If a name is not entered, you will have to enter the connection parameters the next time you use the software.
  • A valid connection string is required to be able to connect to the database ( [image] ). Please see below for the format of the connection string.
  • A username ( [image] ) and password ( [image] ) may be required for connecting to the database depending on how your Oracle database is set up.
  • Select the checkbox ( [image] ) to save the password so you do not have to enter it everytime you use the software. The password is saved unencrypted in the registry so we recommend that you do not select this checkbox for sensitive passwords.

After entering the required parameters, please click Next to connect to the database. If the connection succeeds you will be taken to the next screen. If there was an error, it is shown in an error dialog and you can reenter the parameters if necessary.

Figure 2.5. Connection Error

Figure 2.6. Login Error

2.6.1. Connection Strings

A valid Oracle connection string must be entered for connecting to the Oracle database. The format of the connection string depends on whether TNSNAMES.ORA is being used or not.

Using TNSNAMES.ORA

  • If TNSNAMES.ORA is found, you can enter just the name from TNSNAMES.ORA.
  • TNSNAMES.ORA is found in the directory specified by an environment variable TNS_ADMIN. If this variable is not set, but ORACLE_HOME is set, the file is found in $ORACLE_HOME/network/admin.

SQL Connect String

If TNSNAMES.ORA is not found and you are using the Oracle Instant Client to connect to oracle, you can enter the connection string as an SQL Connect String in the following format:

//host:[port][/service name]

For example,

//dbase-server-5:4321/ORDERS

Using Oracle-Net Keywords

You can also enter connection information as Oracle-Net keyword-value pairs. For example:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=dlsun242)
(PORT=5521))
(CONNECT_DATA=(SERVICE_NAME=bjava21)))


2.7. The Database Actions Page

Figure 2.7. The Actions Page

Once the connection to the database is successful, you can select the action to perform on the database from the following:

  • Import XML: Select this option to import data from one or more XML files or URLs.
  • Delete XML Data: Allows you to delete data from previously imported XML files.
  • Cleanup Database: For completely removing all objects created by Exult Oracle from the database including all imported data.


2.8. The XML File Selection Page

After selecting the IMPORT action, you can specify the XML files for shredding and importing into the database. Exult Oracle provides the following methods for specifying the XML files to import.

2.8.1. Simple File Selection Box

A single file or a small number of files can be specified using this method.