Re: SQL Loader 524 Error: How to Prevent?

[Quoted] Part of the problem is that SQL*Loader has not reached a commit point before [Quoted] the error occurs. If you have a file with greater than 1000 rows and your [Quoted] commit point is 1000, then the number of records in the final table should [Quoted] 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 [Quoted] 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. [Quoted]
2. set commit point = 1 (heaven forbid but should work)

[Quoted] 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 Wed Jul 14 1999 - 06:00:51 CEST