Re: SQL Loader 524 Error: How to Prevent?

Part of the problem is that SQL*Loader has not reached a commit point before the error occurs. If you have a file with greater than 1000 rows and your commit point is 1000, then the number of records in the final table should be equal to the last commit point.

Possible workarounds are:

1. I found the ASCII character (26) in the file. I ran the data file through Perl to remove CHR(26) (of all things) This fixed the files for my situation. Any method of removing the last character should work. In my situation, the data file came as an export file in ASC Delimited format from Paradox.
2. set commit point = 1 (heaven forbid but should work)

Hope this helps.......HAROLD

IIFThen wrote in message <8yOi3.5583$Tl3.33091_at_news3.mia>...
>Thanks for the email. Let me tell you what I did in more detail:
>
>Case 1: Without end of file character
>
>(1) Ran SQL*Loader against datafile with no partial records.
>Result: Log file said records loaded successfully. Good!
>
>(2) Ran Oracle SQL Worksheet with same schema username/paasword_at_alias
>as SQL*Loader required. Ran the following query: select count(*) from
table.
>Resultset equaled the records SQL*Loader said it loaded.
>
>Now..
>
>Case 2: With end of file character
>
>(1) Ran SQL*Loader against datafile with a partial record. Inserted a
>character on the last line of data file.
>Result: Log file said records loaded successfully. Good! I thought....
>
>(2) Ran Oracle SQL Worksheet with same schema username/paasword_at_alias
>as SQL*Loader required. Ran the following query: select count(*) from
table.
>The result was 0!
>
>I was hoping someone out there in Oracle land could replicate/disproved
what
>I was experiencing. Still looking for a solution....
>
>IIFThen
>
>John Griffin <jgriffin_at_boyne.u-net.com> wrote in message
>news:378BA5FF.370762E1_at_boyne.u-net.com...
>> IF the SQL*load file says that it has written records to the table then I
>would
>> believe it - it sounds like you are doing something wrong.
>>
>> 1. Please verify that the table name in the control file is the same as
>the
>> table you are checking with the query
>> 2. Verify that you are using logging on to the same schema with
SQL*loader
>as
>> you are when running the query i.e. you are not running the query against
>> another copy of the table in another schema
>> 3. Verify that the log file you are looking at has the correct timestamp
>and it
>> is the correct log file
>> 4. If you check all of these and everything appears fine try using the
>parameter
>> COMMIT_DISCONTINUED=TRUE
>>
>>
>>
>> IIFThen wrote:
>>
>> > Well...SQL*Loader says all records loaded correctly but when I query
the
>> > table for these records it returns a count of 0. Try putting an eof
>> > character or any character at the end of your data file(i'm using an
>> > external one) and see if you can generate a nonzero count when you
query
>the
>> > Oracle table. I'd like to know if I'm doing something crazy. FYI:
>SQL*Loader
>> > seems to handle partial records anywhere but the last line of the file.
>> >
>> > BTW: The data file that I'm loading is being ftp'ed over to a server
and
>I'm
>> > automatically running the SQL*Loader program against it. Therefore, I
>need
>> > for SQL*Loader to reject all partial records especially on the last
line
>> > which I suspect is preventing my records from "actually" being loaded.
>> >
>> > Thanks!
>> >
>> > IIFThen
>> >
>> > Mark G <someone_at_hot> wrote in message
news:378af1e8.0_at_145.227.194.253...
>> > > I had this a few times.
>> > > The problem is with the end of file so all records before that should
>be
>> > > loaded ok (well, it loaded all for me).
>> > > I once was told that make sure a carriage return is at the end of
your
>> > file
>> > > so sql*loader knows what the last record is. Also a carriage return
>at
>> > the
>> > > end of your control file.
>> > >
>> > > Try it and see.
>> > >
>> > > M
>> > >
>> > > IIFThen wrote in message ...
>> > > >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
>> > > >
>> > > >
>> > >
>> > >
>>
>
>
Received on Tue Jul 13 1999 - 23:00:51 CDT