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

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 Wed Jul 28 1999 - 12:46:22 CDT