Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: sqlldr questions

Re: sqlldr questions

From: Rod Corderey <>
Date: Tue, 31 Oct 2000 14:29:05 +0000
Message-Id: <>


I appreciate that you are loading a substantial amount of data, but the approach I always use is to do that load into a load table which is a simple mirror image of the incoming data file, ie assuming that everything is just meaningless junk text.

Then a second process performs the actual data load to constrained tables, performing validation and cleansing as part of the process.

In this way apart from situations as Stephane Faroult mentioned of format issues and spurious separation characters, the actual loader process usually succeeds leaving the raw data available to more sophisticated PLSQL processes, and the sqlldr rejects separated off in the bad file.

If post load the raw table has a status set to - say - 'L' for loaded and the plsql processes move that status forward to - say - 'P' for processed, then firstly any rows that are rejected by the PLSQL process can be marked as 'B' for bad and reprocessed after correction, but also the SQLLDR rejects in the bad file can be separately loaded - coming in with a status 'L' in a later session.

If the data is time oriented and needs to be serially applied then this can be handled by a composite of the PLSQL processes knowing which rows remain in the sqlldr bad files in order as part of their processing, related later records can be posted back for later processing.


Rod Corderey

Lane Associates

Charlie Mengler wrote:

> I need to use sqlldr to load about 32GB worth of data
> as a one-shot deal. I ran an initial test over this
> last weekend. The test revealed some "dirty" data.
> Can sqlldr be configured such that it simply does a
> sanity test on the data to verify that all the records
> are in acceptable format WITHOUT actually going thru
> the overhead of loading the data? If so, how?
> I'm running the instance in archive mode. The test
> load job consumed all the disk space where the redo
> logfiles get deposited. Is there any way to configure
> sqlldr so that logging is DISBALED? If so, how?
> --
> Charlie Mengler Maintenance Warehouse
> 10641 Scripps Summit Ct
> 858-831-2229 San Diego, CA 92131
> Your call is important to us. Please continue to hold!
> --
> Please see the official ORACLE-L FAQ:
> --
> Author: Charlie Mengler
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Tue Oct 31 2000 - 08:29:05 CST

Original text of this message