Re: SQL Loader

I tried loading 700k records with a Delphi4 program. I estimated that it would take approx. 10 hrs to load into Oracle table. With SQL*Loader loading the same records it took about 1.5 Minutes! Below is the control file I used. The truncate command(good for bulk deletes) is what I think you want to use before you load data so your table is clean.

Sample control file:

• Requires CR/LF on last line of control file.

OPTIONS (DIRECT=TRUE, PARALLEL=FALSE)
UNRECOVERABLE
LOAD DATA
INFILE 'aithw.dat'
TRUNCATE
INTO TABLE AIT.AITHW ("STATE" POSITION (01:02) CHAR,

"AREA" POSITION (03:09) CHAR,
"IMPORT_DAT" POSITION (10:15) CHAR,
"CPR" POSITION (16:21) CHAR,
"FRC" POSITION (25:29) CHAR,
"FLOOR" POSITION (30:31) CHAR,
"FRAME_ID" POSITION (32:40) CHAR,
"YR" POSITION (41:42) CHAR,
"QTY" POSITION (43:50) CHAR,
"MAT_COST" POSITION (51:62) CHAR,
"INPL_COST" POSITION (63:74) CHAR,
"ECN" POSITION (75:79) CHAR,
"ECN_INPL" POSITION (80:91) CHAR,
"BASIC" POSITION (94:105) CHAR,
"PART" POSITION (106:132) CHAR)

Sql*Loader WinNT Dos command(for sample data file):

sqlldr80 userid=username/password_at_alias control=controlfile.ctl discard=discardfile.dsc data=datafile.dat

HTH, IIFThen

<bgumus_at_my-deja.com> wrote in message news:7mdoke$ju$1_at_nnrp1.deja.com...
> As well as I know, you can not use SQl Loader if there is data in the
> table. Is that right? If that is the case, is there any other way of
> loading flat files into tables that already has some records?
>
> The last question: Is SQl loader faster than sending individual sql
> statements from a CGI script? If yes, how much faster?
>
> Thanks
> Bulent Gumus
> Software Engineer
> OnBoard Software Inc.
>
>
> In article <378A4981.23F21920_at_boyne.u-net.com>,
> John Griffin <jgriffin_at_boyne.u-net.com> wrote:
> > Don't worry its not a problem. All the good records will be loaded
> and the
> > dodgy ones rejected. You can verify this by checking the log file
> which will
> > be generated in the same directory as the control file by default.
> >
> > You can set SQL*Loader up to keep loading until a certain number of
> errors
> > occur ( I think this is 50 by default). If you only have one or two
> dodgy
> > records at the end of the file you will not hit this limit. Even if
> you did
> > you could increase the limit.
> >
> > Everything is detailed quite well in the manual.
> >
> > IIFThen wrote:
> >
> > > Hello. I'm loading Oracle 8 db with variable length records. How
> can I
> > > prevent the following error from happening if the datafile
> (aithwsub.data)
> > > has junk on a record like eof character, etc:
> > >
> > > SQL*Loader-524: Partial record found at end of datafile
> (aithwsub.dat)
> > >
> > > Is this even a problem? Will the good records be loaded and the bad
> ones
> > > discarded? I looked through the SQL reference manual and nothing
> jumped out
> > > at me as an obvious solution.
> > >
> > > Thanks!
> > >
> > > Michael
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Jul 13 1999 - 01:18:51 CEST