12
Oracle TransX Utility

This chapter contains the following sections:

Overview of the TransX Utility

The TransX Utility simplifies the loading of translated seed data and messages into a database. It also reduces internationalization costs by:

Preparing strings to be translated.
Translating the strings.
Loading the strings to the database.

The TransX Utility minimizes translation data format errors and it accurately loads the translation contents into pre-determined locations in the database. Other advantages of the TransX Utility are:

Translation vendors no longer have to work with unfamiliar SQL and PL/SQL scripts.
Syntax errors due to varying Globalization Support settings are eliminated.
The UNISTR construct is no longer required for every piece of NCHAR data.

Development groups that need to load translated messages and seed data can use the TransX Utility to simplify what it takes for meeting internationalization requirements. Once the data is in a predefined format, the TransX Utility validates its format.

Choosing the correct encoding when loading translated data is automated because loading with TransX takes advantage of XML which describes the encoding. This means that loading errors due to incorrect encoding is impossible as long as the data file conforms to the XML standard.

Primary TransX Utility Features

This section describes the following features of the TransX Utility:

Simplified Multilingual Data Loading

Traditionally, the typical translation data loading method was to switch the NLS_LANG setting when you switch files to be loaded. Each of the load files is encoded in a particular character set suitable for the particular language. This was required because translations must be done in the same file format (typically in .sql script) as the original.

The NLS_LANG setting changes as files are loaded to adapt to the character set that corresponds to the language. The TransX Utility loading tool frees the development and translation groups maintaining the correct character set throughout the process until they successfully load the data into the database using XML.

Simplified Data Format Support and Interface

The TransX Utility data loading tool complies with a data format defined to be the canonical method for the representation of any seed data to be loaded to the database. The format is intuitive and easy to understand. The format is also simplified for translation groups to use. The format specification defines how translators can describe the data to load it in the expected way.

The data loading tool has a command-line interface and programmable API. Both of them are straightforward and require little time to learn.

Loading Dataset in The Standard XML Format

Given the dataset in the canonical format, the TransX Utility loads the data into the designated locations in the database. It does not, however, create objects, including the table that the data is going to be loaded to. In addition to literal values represented in XML, the following expressions can be used to describe the data to be loaded:

Constant Expression

A constant expression allows you to specify a constant value. A column with a fixed value for each row does not have to repeat the same value.

Sequence

A column can be loaded with a value obtained from a sequence in the database.

Query

A SQL query can be used to load a column. A query can use parameter(s).

Handling Existing Data

The data loading tool determines whether there are duplicate rows in the database. It also lets you choose how it processes duplicate rows from one of the options in the following list. A row is considered duplicate if the values of all columns specified as lookup-key are the same. The processing options are:

Skip the duplicate rows or leave them as they are (default)
Update or overwrite the duplicate rows with the data in provided dataset
Display an error

Other TransX Utility Features

The lists describes other TransX Utility features:

Command-line Interface--The data loading tool provides easy-to-use commands.
User API--The data loading tool exposes a Java API.
Validation--The data loading tool validates the data format and reports errors.
White Space Handling--White space characters in the dataset are not significant, unless otherwise specified in various granularity.
Unloading--Based on a query, the data loading tool exports the result into the standard data format.
Intimacy with Translation Exchange Format--Designed for transformation to and from translation exchange format
Localized User Interface--Messages are provided in N languages.

Installing TransX Utility

Here is how to install TransX, and the dependencies of TransX.

Dependencies of TransX

The Oracle TransX utility needs the following components in order to function:

Database connectivity -- JDBC drivers. The utility can work with any JDBC drivers but is optimized for Oracle's JDBC drivers. Oracle does not guarantee or provide support for TransX running against non-Oracle databases.
XML Parser -- Oracle XML Parser, Version 2. The Oracle XML Parser, Version 2, is part of the Oracle8i and Oracle9i installations, and is also available from the Oracle Technology Network (OTN) Web site.
XML Schema Processor -- Oracle XML Schema Processor. The Oracle XML Schema Processor is part of the Oracle8i and Oracle9i installations, downloadable from the Oracle Technology Network (OTN) Web site.
XML SQL Utility-- Oracle XML SQL Utility (XSU). The Oracle XSU is part of the Oracle8i and Oracle9i installation, and is also available from Oracle Technology Network (OTN) Web site.

Installing TransX Using the Oracle Installer

TransX is packaged with Oracle9i. The TransX utility is made up of three executable files:

$ORACLE_HOME/rdbms/jlib/transx.zip -- contains all the java classes which make up TransX $ORACLE_HOME/rdbms/bin/transx -- a shell script to invoke TransX from UNIX command line.
$ORACLE_HOME/rdbms/bin/transx.bat -- a batch file to invoke TransX from Windows command line.

By default, the Oracle9i installer installs TransX on your hard drive in the locations specified above.

Installing TransX Downloaded from OTN

Download the correct XDK for java distribution archive from the Oracle Technology Network (http://otn.oracle.com). Expand the downloaded archive. Depending on the usage scenario, perform the following install tasks:

To use the TransX's front-end or its Java API, you need to:

Set up the environment (that is, set CLASSPATH) using the env.xxx script (located in the bin directory inside the directory created by extracting the XDK download archive):

Unix users: make sure that the path names in env.csh are correct; source the env.csh. If you are using a shell other than csh or tcsh, you will have to edit the file to use your shell's syntax.

Windows users: make sure that the path names in env.bat are correct; execute the file.

TransX Utility Command-Line Syntax

The following describes the command-line syntax for the TransX Utility.

java oracle.xml.transx.loader [options] connect_string username password 
datasource [datasource]
java oracle.xml.transx.loader -v datasource [datasource]
java oracle.xml.transx.loader -x connect_string username password table [column] 
java oracle.xml.transx.loader -s connect_string username password filename table 
[column]

TransX Utility Command-Line Examples

The following are command-line examples for the TransX Utility:

java oracle.xml.transx.loader "dlsun9999:1521:mydb" scott tiger foo.xml
java oracle.xml.transx.loader "jdbc:oracle:oci:@mydb" scott tiger foo.xml
java oracle.xml.transx.loader -v foo.xml 
java oracle.xml.transx.loader -x "dlsun9999:1521:mydb" scott tiger emp 
java oracle.xml.transx.loader -s "dlsun9999:1521:mydb" scott tiger emp.xml emp 
ename job

TransX Utility Command-line Parameters

Table 12-1 shows the command-line parameters.

Table 12-1 TransX Utility Command-line Parameters

Parameter	Meaning
connect_string	JDBC connect string You can omit the connect string information through the '@' symbol. 'jdbc:oracle:thin:@' will be supplied.
username	Database user name.
password	Password for the database user.
datasource	An XML data source.
option	Options in Table 12-2, "TransX Utility Command-line Options".

TransX Utility Command-line Options

Table 12-2 TransX Utility Command-line Options

Option	Meaning	Description
-u	Update existing rows.	When this option is specified, existing rows are not skipped but updated. To exclude a column from the update operation, specify the `useforupdate` attribute to be "no".
-e	Raise exception if a row is already existing in the database.	When this option is specified, an exception will be thrown if a duplicate row is found. By default, duplicate rows are simply skipped. Rows are considered duplicate if the values for lookup-key column(s) in the database and the dataset are the same.
-x	Print data in the database in the predefined format*.	Similar to the -s option, it causes TransX to perform the opposite operation of loading. Unlike the -s option, it prints the output to `stdout`. Note: Redirecting this output to a file is discouraged, because intervention of the operating system may result in data loss due to unexpected transcoding.
-s	Save data in the database into a file in the predefined format*.	This is an option to perform unloading. It queries the database, formats the result into the predefined XML format and store it under the specified file name.
-p	Print the XML to load.	Prints out the dataset for insert in the canonical format of XSU.
-t	Print the XML for update.	Prints out the dataset for update in the canonical format of XSU.
-o	Omit validation (as the dataset is parsed it is validated by default).	Causes TransX to skip the format validation, which is performed by default.
-v	Validate the data format and exit without loading.	Causes TransX to perform validation and exit.
-w	Preserve white space.	Causes TransX to treat whitespace characters (such as \t, \r, \n, and ' ') as significant. Consecutive whitespace characters in string data elements are condensed into one space character by default.

Command-line Option Exceptions

The following are the command-line option exceptions:

-u and -e are mutually exclusive
-v must be the only option followed by data, as in the examples
-x must be the only option followed by connect info and SQL query as in the examples

Omitting all arguments will result in the display of the front-end usage information shown in the table.

For complete details of the Java API for TransX Utility:

Sample Code for TransX Utility

The following is sample code for the TransX Utility:

String  datasrc[] = {"data1.xml", "data2.xml", "data3.xml"};

// instantiate a loader 
TransX  transx = loader.getLoader();
 
// start a data loading session 
transx.open( jdbc_con_str, usr, pwd ); 

// specify operation modes 
transx.setLoadingMode( LoadingMode.SKIP_DUPLICATES ); 
transx.setValidationMode( false );

// load the dataset(s) 
for ( int i = 0 ; i < datasrc.length ; i++ ) 
{ 
transx.load( datasrc[i] ); 
} 

// cleanup 
transx.close();

12 Oracle TransX Utility