The database we are loading is the Pesticides Data Program from the US Department of Environment. It has a master Pesticides file with fixed 52byte format records (13207 of them) and then a Pesticides Readings database with over a million records for 2004 alone. There are 16 additional cross reference tables that provide expanded descriptors for the codes in the Master and Readings tables. This is a typical information database most likely to be used for reporting and compliance applications.

As noted above, the database is comprised of fixed length records with no delimiter between fields. Checking with the XE's Online Help ( Oracle Database 10g Express Edition | Get Help | Read Online Help are the menus items off Windows XP's start menu) spells out when to

each of the 4 Oracle Loaders - XE Load Wizard, SQL*Loader, Data Pump, and the EXP/IMP command lines utilities. It looks like SQL*Loader is our only tool available. Unfortunately, the SQL*Loader documentation included with XE does not cover reading fixed length records. But not to worry, Oracle's Technology Network does have the documentation available for free - Oracle Database Utilities. Oops the examples cited in the Database Utility PDF illustrating how to do the fixed record load are only available with the full edition of Oracle Enterprise, so I decide to delay the SQLLDR step until after I have created the PESTMASTER table. Decisions made in defining the file and its datatypes may effect the load process.

Creating the PESTMASTER Table

As noted in our Oracle Overview, Oracle XE does not allow users to create separate databases. But one can approximate that by creating a new user - because each user gets a devoted schema in Oracle XE. So I create the user JBPEST and then re-logon as JBPEST into Oracle XE
and start to create the table PESTMASTER.


The process is fairly simple as can be seen in the screenshot above - just click on the Object Browser | Create | Table command.

Again, Oracle provides a fairly complete interface for creating a table as seen below:
However, I make an decision which I will later have to reverse on load, that is to define the PYEAR, PMONTH, and PDAY fields as Integers. Also I have a problem defining the Primary Keys because for PESTMASTER the primary key is a composite of the seven first fields. But Oracle XE only allows me to define two key composites in its Primary Key dialog as shown below.

The Constraints dialog allows me to change the Primary Key constraint, but it only allows 3 primary keys. So I end up copying the SQL , then dropping the table and re-adding it back in with an Alter table command immediately following it. The ALTER TABLE code is:
ALTER TABLE "PESTMASTER" ADD CONSTRAINT "PESTMASTER_PK"
PRIMARY KEY ("PSTATE","PYEAR", "PMONTH", "PDAY", "PSITE", "PCOMMOD", "PSSID")/

For some reason Oracle XE uses "/" as its end of line delimiter instead of semi-colon. However, after this 20 minute side jaunt, I am happy to have the table defined and created. I return to

task of defining the Control File, shown on the left, by reading through the SQLLoader documentation. As noted above, because the file is 600 pages in length and many of the examples are not included in the file - it takes me about 2 hours to ferret out a first guess about the correct syntax.

To my surprise I am remarkably right. I leave in an extra comma at the end, the LOGFILE reference is not allowed, and the INSERT directive cannot follow the INTO TABLE clause as shown in Appendix A. But I am getting ahead of myself. I first had to spend two hour getting SQLLDR - the SQL Loader command to work on a simple test dataset.

If users try SQLLDR from the Run SQL Command Line - Bzzzzzzt! Okay, so just run it from the regular Windows XP command line. No Luck. Okay so lets find out if SQLLDR is even in the Oracle XE distribution. Yep, its at: C:\oraclexe\app\oracle\product\10.2.0\server\BIN. So lets add this to the Windows XP environment variables and retry the Run SQL Command Line. Ooops - Bzzzzzt again. Okay so run from a Windows XP Run command window - partial success!

SQLLDR runs; but it cannot see the Control='c:\oraclexe\pestm.ctl' file. So find out where SQLLDR expects pestm.ctl to be and move it there. Voila - we have partial lift off. Now I spend the next 5-6 runs working out the acceptable syntax in the .CTL file. One problem is that the Integer input fields don't work as expect - so I alter the table and make them character fields for now. Three and a half hours later, it takes SQLLDR less than a second to load up PESTMASTER with 13,207 records.

I spend some extra time making sure the first and last records entered are correct and I am satisfied that the Load deed is done. Whew.

Summary

The British might describe this Load process as a bit of a sticky wicket. I am not so sure. Loading up XML data looks positively intimidating, but yours truly will be trying and reporting the results in the weeks ahead. However, floundering on Composite keys, supposedly legitimate SQL*Loader syntax not working and SQLLDR not working in SQL Command Line Window - that smacks of the Redmond school of "Just Good Enough" software and documentation.

So now I am on the alert - when trying to do things off the beaten track,Oracle XE may have a few surprises. Our next test will be to load up the WordWeb database - all 1million plus records spread over 8 input files. Our next Oracle report will be on how successful that exercise proved to be. But I am also on the alert to the good vibes people are giving for JDeveloper and the Oracle application development tools. So the jury is far from out on the Oracle XE experience - more to come, hopefully sooner than later.