Re: SQL Loader 524 Error: How to Prevent?

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 Wed Jul 14 1999 - 19:35:39 CEST