Re: SQL*LOADER problem

From: David Scott <dscott_at_is.net>
Date: 1995/11/18
Message-ID: <dscott-1811952149000001_at_dscott.is.net>#1/1


In article <48i6mm$qgq_at_athos.cc.bellcore.com>, parris_at_walleye.esp.bellcore.com (Parris Geiser) wrote:

> Hi,
> I have a table with over 100 columns that I'm trying to load.
> When I do the load, on one of the data entries I get a message
> from the loader that one of the columns is too long. But, it
> doesn't tell me which one. To go thru all of the columns will
> be rather time consuming and I suspect that I might have more than
> one record with this problem.
>
> Does anyone know of an easy way to find out which entry is
> actually in error?

Unfortunately, you'll have to worry through this, but I use a strategy to help me find the column in error. Try this:

Use -- to comment out the last 50 of your columns, then rerun the load. If it still fails, your problem column is in the first 50; if it works, the first 50 are ok; comment these and uncomment the last 50.

Once you've found the split group that contains the bad column, just keep splitting the group in half until you find the column that gives the error. Using this strategy, you should find the problem quickly, probably within 5 or so iterations of the load. Of course if you set the number of rows loaded to be about 500 or so, this runs even faster.

The other possibility is that your control file is OK, but the data is giving you fits. In this event, make sure that you specify a "bad file" to catch the data rejected from the load, and analyze that. You might need to revert to the previous strategy to help with this. Something else that I've done (on the Mac in Hypercard) is to write a custom program to display the data file according to the control file specs. You'd be surprised how many errors that it catches.

Best of luck in killing your bugs.



David Scott
...how can I keep from singing?
email: dscott_at_is.net
Received on Sat Nov 18 1995 - 00:00:00 CET

Original text of this message