Re: How/Where do I use COMMIT in SQL*Loader

[Quoted] I tried that but it ran too slow for 2.7M records. BTW, I'm using Direct Path.
[Quoted] Is there another alternative?

IIFThen

Sybrand Bakker <postmaster_at_sybrandb.demon.nl> wrote in message news:933183973.4732.0.pluto.d4ee154e_at_news.demon.nl...
> rows=1 on the command line.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> IIFThen <msessions_at_aldensys.com> wrote in message
> news:nnDn3.5559$i5.48073_at_news4.mia...
> > I'm still trying to solve my problem loading my data with that has
partial
> > records at the end of the file. How do I commit each record one at time?
> > What is the syntax? Where is used? CTL file or SQL*Loader command line?
> >
> > Thanks,
> > IIFThen
> >
> > See responses below for background on my problem:
> >
> > Joseph E. Steinhauser <jsteinha_at_us.oracle.com> wrote in message
> > news:3794E411.85693F85_at_us.oracle.com...
> > > > A few suggestions
> > >
> > > ...although I would ad that I have usually run SQL*Loader on unix...
> > >
> > > 1) If you think that your control file is doing fine, then focus on
> your
> > data
> >
> > that is where I think the problem is. I have a junk character at the end
> of
> > my data file and SQL*Loader is reporting that all records were loaded
> > successfully but when I query the Oracle table for these records they do
> not
> > exist. Weird..huh?
> >
> > > 2) Try creating a 10 record file of 'test' data, that you have set up
> by
> > hand,
> > > and then see how successfully you can load that into the table.
If
> > you
> > > can load that finne, then your .CTL file is working.
> >
> > I've verified this and for records that do not contain any eof chars it
> > works fine.
> >
> > > 3) For testing, use the Loader option to commit EACH record, one at a
> > time.
> > > This way, any problems you have with records later in the data
file
> > cannot
> > > hurt you with the records before that.
> >
> > I'll try this.. this might be a good workaround. Where is a good place
to
> > put this COMMIT in my control file[see 8)]?
> >
> > > 4) examine your 'log' files in detail
> >
> > Check...
> >
> > > 5) verify the exact record lengths, paying specific attention to bytes
> > used as
> > > [EOL] characters (ie: record delimiters & CR/LF )
> >
> > Check..
> >
> > > 6) check you data, to make sure that no tool/app that you are using
> would
> > have
> > > modified the formatting at all/ or added any sort of "header
info"
> > etc.
> >
> > Check. data looks fine.
> >
> > > 7) Try using the options "skip" and "load" to tell SQL*Loader to skip
> over
> > > a few records in the file - then load several records for
testing -
> > and
> > > quit
> > > before you get to the [EOF]
> >
> > I'll try this if 3) doesn't do it for me.
> >
> > > 8) If you haave any further questions from the newsgroup - try to
> include
> > > further details about the structure of the data,
> > >> Variable records possible
> > where it came from,
> > >> mainframe
> > the
> > > control file & command line options that you are using, error
logs,
> > etc.
> >
> > 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)
> >
> > command line(run from NT server):
> > sqlldr80 userid=ait/admin_at_aldenorcl control=aithw.ctl discard=aithw.dsc
> > data=%1
> >
> > >
> > > ... I hope this helps...
> >
> > Me to. thanks!
> >
> > IIFThen
> >
> > >
> > > Joseph
> Steinhauser
> > >
> > >
> > >
> > > > > > Can SQL*Loader (used on NT server) handle partial records at the
> END
> > of a
> > > > data file?
> > > > > >
> > > > > > SQL*Loader log file reports records successfully added but when
I
> > query
> > > > the data with
> > > > SQL Worksheet it returns a count of 0. Can anyone out there
try
> > > > inserting a character at
> > > > the end of their datafile and then run SQL*Loader against
it.
> > Then
> > > > query to see if ALL
> > > > non-partial records were loaded.
> > >
> > > > 1) SQL*Loader reports Error 524: Partial records found at the
> end
> > of
> > > > the data file,
> > > > 2) SQL*Loader log file says that all records were successfully
> > loaded.
> > > > 3) When I query the table, no records are there.
> > > >
> > >
> >
> >
> >
> >
>
>
Received on Thu Jul 29 1999 - 17:47:04 CEST