For release specific information, please refer to README.

• What is the Oracle TransX Utility?
• Dependencies and Installation
• Example Usage
• Using the Command Line Front End
• Example of Dataset represented in XML
• Using Java API
• Frequently Asked Questions (FAQs)

What is the Oracle TransX Utility?

TransX is a data transfer utility that allows you to populate your database with multilingual data. It uses XML to specify the data so you can take advantage of easy data transfer from XML to the database, a simple data format that is intuitive for both developers and translators, and validation capability that makes it less error prone.

 Easy multilingual data loading

Traditionally, the typical way to load translated data was to switch the NLS_LANG setting as you switched files to be loaded, each of which is encoded in a particular character set suitable for the particular language. This was required because the translations have to be in the same file format (typically in .sql script) as the original, and NLS_LANG is changed as the files are loaded to reflect the character set corresponding to the language. TransX frees developers and translators from having to maintain the correct character set throughout the process of translating the data and loading them successfully into the database.

 Support for simple data format

TransX defines an XML data format for a canonical representation of data to be transferred to a database. The format makes it intuitive for developers to understand what will happen to the data represented in it and how it is transferred. It is also self-explanatory for translators to identify what data needs to be translated. The format specification defines how to describe the data that it is loaded as expected. The values in the dataset can be represented using scalar values or using expressions such as constant, sequence,  and queries.

Simple interfaces

TransX has a command line interface and programmatic interface. Both of them are straightforward and require little time to get familiar with. Details will be discussed in the next section. All the functionalities of TransX are accessible through both the command line interface and the Java API.

TransX Features at a Glance

TransX has the following features:

• Support loading data in the predefined XML format.
• Support constant expression: A column can be loaded with a value specified by a constant expression.
• Support sequence expression: A column can be loaded with a value obtained from a sequence in a database.
• Support SQL expression: Query and stored procedure/function call.
• Support incremental loading: A transfer mode where existing rows are updated is available in addition to the regular transfer mode for initial loading.
• Support data format validation: The format of an input document can be checked whether or not there is a loading operation. Validation can be disabled upon loading.
• Support data element attributes for translation purposes.
• Support unloading to make it easy to migrate from loading .sql files to .xml files

Dependencies and Installation

Dependencies

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 Oracle9iinstallation, 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 Oracle9iinstallation 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 Oracle9iinstallation, 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:

Example Usage

TransX is especially useful in populating a database with multilingual data. This section discusses how it can be accomplished with a typical use scenario where TransX is used to organize translated application messages in a database.

Choosing Where to Keep Translatable Data

To build an internationalized system, it is essential to decouple localizable resources from the business logic. A typical example of such a resource is translated text information. Data specific to a particular region and shares a common language and cultural conventions needs to be organized using some kind of resource management facility that allows you to retrieve locale-specific information. A database is often used to store them because of easy maintenance and flexibility. Typically they are stored in a table like the following:

Table 1-1: Table Structure for Translated Text Messages

MESSAGE_ID  LANGUAGE_ID  MESSAGE
----------  -----------  ----------------------------------
1           us           Welcome to System X
2           us           Please enter username and password
:           :            :

Notice that the column LANGUAGE_ID is included in this table so that applications can retrieve messages based on the preferred language of the end user. It contains abbreviations of language names to identify the language of messages. Oracle language abbreviations can be found in the appendix of the Oracle9i Globalization Support Guide. Translated messages can be added to this table.

Arrangement in the Predefined XML Format

An XML document that represents the table above would look something like the following example:

Example 1-1 : Original Data in the Predefined XML Format

<?xml version="1.0"?>
<table name="translated_messages">

  <lookup-key>
    <column name="message_id" />
    <column name="language_id" />
  </lookup-key>

  <columns>
    <column name="message_id"  type="number"/>
    <column name="language_id" type="string" constant="us" translate="yes"/>
    <column name="message"     type="string" translate="yes"/>
  </columns>

  <dataset>
    <row>
      <col name="message_id">1</col>
      <col name="message" translation-note="dnt'X'">Welcome to System X</col>
    </row>
    <row>
      <col name="message_id">2</col>
      <col name="message">Please enter username and password</col>
    </row>
    <!-- ... -->
  </dataset>

</table>
 

As shown above in the predefined XML format, the way your dataset is arranged in XML should reflect the structure of the target table. The table is defined as in Example 1-2, and all of the columns are to be populated.

Example 1-2: Table Description for Translated Messages

SQL> desc translated_messages

Name         Null?     Type
-----------  --------  -------------
MESSAGE_ID   NOT NULL  NUMBER(4)
LANGUAGE_ID            VARCHAR2(3)
MESSAGE                VARCHAR2(200)

As always, this XML example starts with the declaration. Its root element table encloses all the other elements. Inside the table element there are three sections, one for the list of lookup keys, one for the table description and one for the dataset. lookup-keys are those columns that are used to evaluate rows if they are already existing in the database. Because we want a pair of message and language ID to identify a unique string, we list the corresponding columns. The columns section should mirror the table's structure because it specifies which piece of data in the dataset section should go to which column in the database. The column names should be consistent throughout your XML dataset and database. The valid values for the type attribute are string, number, date or dateTime. They correspond to the datatypes defined in the XML schema standard, so each piece of data should conform to the respective datatype definition. In particular, it is important to use the ISO 8601 format for date and dateTime datatypes.

Table 1-2: Date Format in the TransX XML Format

Datatype	Format	Example
date	CCYY-MM-DD	2009-05-20
dateTime	CCYY-MM-DDThh:mm:ss	2009-05-20T16:01:37

Example 1-3 shows how a row with a dateTime data would look:

Example 1-3: A Record with a dateTime Datatype Field

<row>
  <col name="article_id">12345678</col>
  <col name="author_id">10500</col>
  <col name="submission">2002-03-09T16:01:37</col>
  <col name="title">...</col>
  <!-- some columns follows -->
</row>

Back to our example, the constant attribute of a column is used to specify a value to be stored into the corresponding column for every row that appears in the dataset section. As we are working on the original language, the language_id column is set to be 'us'.

Translating the Data in XML

When translating messages for applications, it is often the case that certain words or phrases should be left untranslated. As shown in this example, the translation-note attribute may be added to elements with translatable string to help improve the quality of translation. It is used to tell if the column is going to contain translated data. In this example, there are two columns with this attribute and they use it with a slight difference in their meanings. The first one for the language_id means the value of the constant attribute should be translated. The other one for the message column means those data in the dataset section that has a name that matches this column, which is "message", needs to be translated, such as <col name="message">Welcome to System X</col>, <col name="message">Please enter...</col>, and so on.

By using XML transformation, this format can be converted into another format for exchanging translation data among localization service providers for use with XML-based translation tools. This transformation insulates application developers from the inherent complexity of the format for translation data exchange that includes information for keeping track of revisions, categorizing translatable strings into some units, and so on.

If a plain text editor or a traditional text-based translation tool is used during the translation process, it is important to maintain the encoding of the document. After a document is translated, it is likely that it is encoded in a different encoding than the original one. If the translated document is in an encoding other than Unicode, the encoding declaration should be added to the XML declaration on the first line. A declaration for non-Unicode encoding looks like this:

<?xml version="1.0" encoding="ISO-8859-15"?>

To ensure that the translation process did not lose the syntactic integrity, it is important to process the document as XML. If that is not the case, the validation option of the command line option is handy for anybody to check the format. If any syntactic error was brought in, the option prints out the location and what the error is. Errors must be fixed for the data transfer to succeed.

A translated document for our example is shown in Example 1-4:

Example 1-4 : Translated Data in the Predefined XML Format

<?xml version="1.0"?>
<table name="translated_messages">

  <lookup-key>
    <column name="message_id" />
    <column name="language_id" />
  </lookup-key>

  <columns>
    <column name="message_id"  type="number"/>
    <column name="language_id" type="string" constant="e" translate="yes"/>
    <column name="message"     type="string" translate="yes"/>
  </columns>

  <dataset>
    <row>
      <col name="message_id">1</col>
      <col name="message" translation-note="dnt'System X'">Bienvenido al Sistema X</col>
    </row>
    <row>
      <col name="message_id">2</col>
      <col name="message">Porfavor entre su nombre de usuario y su contraseña</col>
    </row>
    <!-- ... -->
  </dataset>

</table>
 

Data Transfer with TransX

Now that you have the original and translated versions of XML, it is time to execute TransX. The following command is an example to connect to the database mydb at port 1521 on myhost as scott/tiger and transfer the data in the XML files.

Example 1-5: TransX Execution with 2 XML Datafiles

transx "myhost:1521:mydb" scott tiger example.xml example_e.xml

The above command transfers two files of data at the same time.

In building a multilingual software system, translations usually become available at a later stage of development.
They also tend to evolve over a period of time. If for any reason some messages need to be added, they can be added as new rows in your dataset definition simply by running TransX again. As we discussed earlier, TransX recognizes which rows are new and it inserts only the new messages based on the column(s) specified in the lookup-key section. If some messages are updated, run TransX with the -u option. Then it updates existing rows with the data specified in XML.

After running TransX with the command in Example 1-5, your table would look like the following:

Table 1-3: Table Populated with Multilingual Data

MESSAGE_ID  LANGUAGE_ID  MESSAGE
----------  -----------  ----------------------------------
1           us           Welcome to System X
1           e            Bienvenido al Sistema X
2           us           Please enter username and password
2           e            Porfavor entre su nombre de usuario y su contraseña

Your application can retrieve a message in a language with a SQL query like this:

SELECT message FROM translated_messages WHERE message_id = 2 AND LANGUAGE_ID = 'e' ;

Using the TransX Command Line Front End

TransX comes with a simple command line which gives users quick access to TransX's functionality.

The command line options are provided through the java class loader. Invoke it by calling:

transx

To use the TransX command line, you first need to specify where the executable is located. To do this, add the TransX java library (transx.zip) to your CLASSPATH.

Because the TransX depends on the the other XDK components, for the TransX to run, you need to make known the location of these components. To do this, your CLASSPATH needs to include the locations of the Oracle XML Parser java library (xmlparserv2.jar), the Oracle XML Schema Processor(xschema.jar) and the JDBC library (ojdbc5.jar).

Here is the syntax of TransX command line:

   transx [ <option> ... ] <connect_string> <username> <password> <datasource> [ <datasource> ... ]

where arguments are specified as follows:

Table 2-1: TransX Command Line Arguments

<option>	One of the TransX options found in Table 2-2
<connect_string>	A JDBC connect string
<username>	A database username
<password>	The password for the database user
<datasource>	An XML data source

Example:

   transx "jdbc:oracle:oci:@mydb" scott tiger foo.xml

If the type of your JDBC driver is Thin, the connect string does not have to be fully specified. Host name, port number, and the SID separated by colons connects you as if you specified the whole connect string.

   transx "myhost:1521:mydb" scott tiger foo.xml
 

• Connects to the database "mydb" as "scott."
• Validates the datasource "foo.xml."
Populates the database as per "foo.xml."

When you specify the -v(validation only) option, arguments for database connection should not be there:

   transx -v <datasource> [ <datasource> ... ]

Example:

   transx -v foo.xml

This example validates the format of foo.xml and exit without database access.

When you specify the -s(unloading) option, the argument list looks like this:

   transx -s <connect_string> <username> <password> <filename> <table> [ <column> ... ]

Example:

   transx -s "dlsun9999:1521:mydb" scott tiger emp.xml emp ename job

This example shows how columns in a table(ename and job in the table emp) are formatted into an XML file(emp.xml) in the predefined format. Column names are optional. By default, all columns are unloaded.
 

TransX Options

Table 2-2 TransX Options

Option	Description
-u Update operation	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 on duplicates	When this option is specified, an exception will be thrown if a duplicate row is found. By default, duplicate rows are simly skipped. Rows are considered duplicate if the values for lookup-key column(s) in the database and the dataset are the same.
-p Print the XML for insert	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	This option causes TransX to skip the format validation, which is performed by default.
-v Validation only	This option causes TransX to perform validation and exit.
-s Save data into an XML file	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.
-x Print data in the predefined XML 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.
-w Preserve whitespace	This option 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.

Example of Dataset Represented in XML

<?xml version="1.0"?>
<table name="i18n_messages">
  <lookup-key>
    <column name="message_code" />
  </lookup-key>

  <columns>
    <column name="message_id"          type="number" sequence="i18n_message_seq" useforupdate="no"/>
    <column name="message_code"        type="number" />
    <column name="message_name"        type="string" translate="yes" />
    <column name="message_description" type="string" translate="yes" />
    <column name="version_created"     type="number" constant="0" />
    <column name="version_updated"     type="number" constant="0" />
    <column name="message_type_code"   type="string" virtual="yes" />
    <column name="message_type_id"     type="number" >
      <query text="select message_type_id from i18n_message_type where message_type_code = :1" >
        <parameter id="1" col="message_type_code" />
      </query>
    </column>
  </columns>

  <dataset>

    <row>
      <col name="message_code" >100</col>
      <col name="message_name" trans-key="stts-name-1" >Continue</col>
      <col name="message_description" trans-key="stts-desc-1" >
        The client should continue with its request.</col>
      <col name="message_type_code" >INFO</col>
    </row>

    <row>
      <col name="message_code" >101</col>
      <col name="message_name" trans-key="stts-name-2" >Switching Protocols</col>
      <col name="message_description" trans-key="stts-desc-2" >
        The server understands and is willing to comply with the client''s
        request (via the Upgrade message header field) for a change in the
        application protocol being used on this connection.</col>
      <col name="message_type_code" >INFO</col>
    </row>

    <row>
      <col name="message_code" >200</col>
      <col name="message_name" trans-key="stts-name-3" >OK</col>
      <col name="message_description" trans-key="stts-desc-3" >
        The request has succeeded.</col>
      <col name="message_type_code" >SUCCESS</col>
    </row>

    <row>
      <col name="message_code" >201</col>
      <col name="message_name" trans-key="stts-name-4" >Created</col>
      <col name="message_description" trans-key="stts-desc-4" >
        The request has been fulfilled and resulted in a new resource being
        created.</col>
      <col name="message_type_code" >SUCCESS</col>
    </row>

    <row>
      <col name="message_code" >202</col>
      <col name="message_name" trans-key="stts-name-5" >Accepted</col>
      <col name="message_description" trans-key="stts-desc-5" >
        The request has been accepted for processing, but the processing has
        not been completed.</col>
      <col name="message_type_code" >SUCCESS</col>
    </row>

    <row>
      <col name="message_code" >203</col>
      <col name="message_name" trans-key="stts-name-6" >Non-Authoritative Information</col>
      <col name="message_description" trans-key="stts-desc-6" >
        The returned metainformation in the entity-header is not the
        definitive set as available from the origin server, but is gathered
        from a local or a third-party copy.</col>
      <col name="message_type_code" >SUCCESS</col>
    </row>

    <row>
      <col name="message_code" >204</col>
      <col name="message_name" trans-key="stts-name-7" >No Content</col>
      <col name="message_description" trans-key="stts-desc-7" >
        The server has fulfilled the request but does not need to return an
        entity-body, and might want to return updated metainformation.</col>
      <col name="message_type_code" >SUCCESS</col>
    </row>

    <!-- ... -->

  </dataset>
</table>

TransX Java API

The following two classes make up the XML-SQL Utility Java API:

• oracle.xml.transx.loader -- provides a method to obtain a TransX instance.
• oracle.xml.transx.TransX -- the TransX API

You can find the full Java API documentation here.

Loading Dataset in XML

The TransX interface makes up the core part of the TransX's API.

Figure 1 illustrates the basic steps in the usage of TransX API.

Perform these steps when loading XML:

• Create an TransX instance
• Start a data loading session by supplying database connection information
• Load the datasets by specifying which XML to load
• Complete the session by invoking the close method

Figure 1 Data Loading With TransX Utility for Java: Basic Steps

1. getLoader()   -----------+
                            |
                            V
                     +------+------------+         ________
2. transx.open()  -->|      :            |        /        \
                     |TransX:   TransX   |        \________/
3. transx.load()  -->|      :            | =====> |        |
                  -->|      :    Core    |        |        |
                  -->| API  :            |        |        |
                  :  |      :  Powerd by |        \________/
                     |      :    XDK     |
4. transx.close() -->|      :            |
                     +------+------------+

TransX Programming Example: Loading Dataset from XML File(s)

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

         // instantiate a transx class
         TransX  transx = loader.getLoader(); 

         // start a data loading session 
         transx.open( "jdbc:oracle:oci8:@", "scott", "tiger" ); 

         // 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();

Here, the connection is made using the Oracle OCI driver.  You can connect to the scott schema supplying the password tiger. It connects to the current database (identified by the ORACLE_SID environment variable). You can also use the JDBC Thin driver to connect to the database. The thin driver is written in pure Java and can be called from within applets or any other Java program.

Connecting With the Thin Driver

Here's an example of connecting using the thin driver.

// start a data loading session with thin driver
transx.open( "jdbc:oracle:thin:@myhost:1521:ORCL", "scott","tiger");
 
 

No Connection Needed When Validating

If you are just validating your data format, you don't need to establish a database connection, since the validation is totally performed by TransX. Consequently your command line for validation only should not have connect information.

transx -v foo.xml

Frequently Asked Questions (FAQs)

General