Oracle Express Data Load 2
Home Tutorials Reviews Weblog

Review: Oracle Express is loaded with Wordweb database
Feature: This migration proved challenging with many strings, and CLOBs problematical

This is the second data loading task presented to OracleXE and the rest of our "free" databases - MySQL 5 and DB2 Express C Viper Edition. In this case we concentrate on loading the Wordweb database . Wordweb is a database of over 170 thousand words of the English language. It has additional tables providing synonyms and antonyms plus word types and definitions. The devil in the Wordweb database are the varying length text fields many of which are stored as CLOBs - Character Large OBjects. Several of those CLOB fields contain single quotes and double quotes as data. This is one detail that makes loading up the Wordweb database tricky to say the least. But helping us is the fact that I have the WordWeb database in two formats - text files laid out in CSV-Comma Separated Values and also in a MySQL database.

One of the advantages of working with Oracle is that there is so much software and utilities available from Oracle and third parties supporting the Oracle databases - it is almost embarrassing. For example, Quest has available TOAD for Oracle a free general browsing and database support utility. Oracle itself has the venerable SQL Plus now in HTML as well as command line mode of operation. And within the last two years, SQL Developer, an Oracle Java app that runs on several platforms has been made available. SQL Developer now even supports browsing into any JDBC database which, given the JDBC-ODBC bridge, means just about any database.

However, SQL Developer does not support managing triggers, indexes, keys, constraints, and stored sdd
stored procedures for other than Oracle databases. Also as we can see from the screenshot, SQL Developer only imports from Excel files. Given that SQL Developer exports to CSV, Text, HTML, XML, Excel, and Loader formats with a fairly robust Export dialog to support that - we were surprised that there was so little support on the Import side. And so we had to look elsewhere. This becomes an important issue later. I also checked Oracle JDeveloper but again it does not have any data import facility.

There are dozens of 3rd party converters and transfer utilities and one of the best for price performance I found is the Intelligent Converters. For $99 you get a package of wizards that do the transfer between a broad range of servers and Oracle via the help of a Transfer dialog. I ran the demo versions and though they were restricted to only 5 records - the demos did convert all 12 MySQL tables to Oracle, including some tricky datatype conversions, with no problems. On the data side the demo only wrote 5 records and none of those involved any of the problems with CLOBs, dates and strings with embedded double quotes and single quotes which confounded MySQL and Rapid SQL. So I cannot confirm that the Intelligent Converters would do the data transfers with no problems. But these Intelligent Converters turned out to be vital in our conversions because we used the data schema and then filled the tables as described below.

The Express Data Load Problem

Well users might as well take a look at the problem just as I saw it:
bombs This cryptic message was all that I could find after trying to load the DERIVED and EXAMPLES tables using the LOAD command in Oracle XE. For the ANTONYM and PART_OF tables there was no problem loading data - but they are simple two integer field tables. In contrast the DERIVED and EXAMPLES tables presented large text string fields - CLOBs and they consistently produced the following error messages regardless of how I delimited the text and created the datatypes. Worse I could not find the LOG file for the load to isolate exactly where the problem was. So I abandoned the Oracle XE loader.

Instead I decided to try the Oracle Migration Loader.

The next step was to use a 3rd party tool to load data into OracleXE and the WordWeb database. I tried using Embarcadero's RapidSQL tool which does have a Import utility for reading CSV files. However, even this turned out to have some sly turns as well. The problem was that RapidSQL's import utility requires delimiter around text fields. This gets a little tricky for some of the Wordweb text fields which have legitimate single and double quotes embedded in them. So to save any problems I converted all single quotes- ' to back single quotes -`, and all double quotes to tildes - ~. Then imported the data using RapidSQL with no hitches. Finally I used the following 2 queries to reset the database:
UPDATE DEFINITIONS SET DEFINITION=REPLACE(DEFINITION, '~', '"')
            WHERE INSTR(DEFINITION, '~')>0;
UPDATE DEFINITIONS SET DEFINITION=REPLACE(DEFINITION, '`', "'")
            WHERE INSTR(DEFINITION, '`')>0;
This is a bit tedious but has the virtue of working reliably. But I was still scouting for an easy to use CSV import tool for Oracle.

So with a final attempt I used the SQL Loader(SQLLDR) just like in the first Oracle load test . But in the Baseball database load there were no long text fields as in the Wordweb database. So a good test would be the loading of the DEFINITIONS table with 126,000 records all of which have TEXT CLOBs data to load. However, as seen in the first Oracle Load exercise - using SQLLDR is no simple task. This time setting the .CTL file was easier because there are only two fields in the DEFINITIONS table. However, again the exact coding for the .CTL string fields is plainly tricky; the complication being the use of single and double quotes within the text fields for a few of the records. Fortunately, SQLLDR allows a broader range of delimiters for text fields so I was able to finesse around this problem. After the first try reloading the DEFINITIONS data went without a hitch.

Summary

Once again, importing data into Oracle XE proved problematic. Oracle's import tools are scattered all over the installation as is the documentation. SQL Developer and JDeveloper really do not help on the import front although they are very valuable free tools for database design/tuning and Oracle application development respectively. SQLLDR is a powerful tool but you really have to hunt down the documentation on how to set it up and use it. I just glanced over the surface of the many 3rd party tools available for loading and can say there are many and they are pretty good but do vary in quality. Finally, the LOAD utility built into the OracleXE Web interface is just half-baked. You will only be able to do simple, primarily numeric loads, or text fields with no conflicting delimiters as data. A surprising lack of attention to a show stopping detail that Oracle normally never misses.




Top of Page  Home  Tutorials 

Developers Weblog  Graphics Reviews and Tips