Re: SQL Loader 524 Error: How to Prevent?

But.. another program will run a server and call sql*loader against my data file as a scheduled event and i won't know the number of records in this file. If the data file is corrupt and sql*loadercan't handle it, well no uploaded records. See my predicament?

PS. I've already tried your suggestions and you're right it works. But it takes human intervention which I don't won't.

Any ideas?

IIFThen

Harold Peske <hpeske_at_telusplanet.net> wrote in message news:mKxj3.2319$k3.224664_at_news1.telusplanet.net...
> Yes I have tried appending CRLF, blank rows etc to the end of the file and
 I
> 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
> 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
> a way to remove the end stuff in the file. I found the character at the
 end
> 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 - 00:00:00 CEST