Re: SQL Loader 524 Error: How to Prevent?

[Quoted] Yes I have tried appending CRLF, blank rows etc to the end of the file and I [Quoted] got the same thing. As for the commit point, that is done in the control file with ROW=<your value> My suspicion is that if you have 12 rows in the [Quoted] datafile and your set ROWS=10, you will end up with 10 rows in the DB. If you set rows to 1, then you should end up with 12. A better way is to find [Quoted] a way to remove the end stuff in the file. I found the character at the end [Quoted] using Notepad editor in NT. The last line had an unprintable character. Remove the character.....removes the problem......HAROLD

IIFThen wrote in message ...
>See Below responses:
>
>Harold Peske <hpeske_at_telusplanet.net> wrote in message
>news:TXTi3.976$k3.124623_at_news0.telusplanet.net...
>> 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.
>
>Don't really want any other program xfering data other than SQL*Loader.
>
>>
>> 2) set commit point = 1 (heaven forbid but should work)
>
>What is this? Does it go in my control file? If yes, my control file is
>below. Can you please tell me where? I'll give it a shot.
>
>P.S. Has anyone tried putting a single character at the end of their data
>file and have SQL*Loader load it into an Oracle table and a query verify
>that all data is there???? My select count(*) from table query returns a
>count of 0!:
>
>>
>> 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 Fri Jul 16 1999 - 05:33:38 CEST