|Oracle Database Load|
| Review: Oracle XE - how easy is it to load up ? It proves to be challenging.
Feature: There are 4 major utilities for loading Oracle depending on the data format
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 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
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
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.
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.
Top of Page Home Tutorials Developers Weblog Graphics Reviews and Tips