How/Where do I use COMMIT in SQL*Loader

From: IIFThen <msessions_at_aldensys.com>
Date: 1999/07/28
Message-ID: <nnDn3.5559$i5.48073_at_news4.mia>#1/1


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 - 00:00:00 CEST

Original text of this message