Re: SQLLDR Invalid Number on Record 2: Windows to Unix

I did not post the data file due to privacy concerns, if necessary I will post a portion of the file with mock data.

The file is automatically translated from EBCDIC to ASCII when downloaded & can be read as clear text in Notepad or Multi-Edit. SQLLDR does process the first record in the file correctly. It just does not process any subsequent records.

I admit to being a novice with sqlldr, but I have NO PROBLEM processing the file as is with VB or Java.

fitzjarrell_at_cox.net wrote:
> Comments embedded.
> The ants are driving me crazy wrote:
> > I'm trying to load a text file from a Windows client onto a Unix-hosted
> > Oracle database. The first record loads fine, but all subsequent
> > records get errors such as "invalid number" or similar errors. Is this
> > a Windows to Unix file format issue?
>
> No, it's a problem with your data file.
>
> > If so, what is the solution?
> > Don't lines in Windows text files end with CRLF and Unix lines end with
> > LF? Is this the problem?
>
> No. Why would it be? SQL*Loader is reading the data from your Windows
> PC, not the UNIX box.
>
> > By the way, the file originated from an IBM
> > mainframe, but I believe that is irrelevant for this issue.
> >
>
> EBCDIC is not ASCII. How was the file transferred from the mainframe
> to the PC?
>
> > Listed below is a selection from my LOG file:
> >
> >
> >
> > Table PO_ACTION, loaded from every logical record.
> > Insert option in effect for this table: INSERT
> >
> > Column Name Position Len Term Encl Datatype
> > ------------------------------ ---------- ----- ---- ----
> > ---------------------
> > COMPANY 1:2 2 CHARACTER
> >
> > CONT_ID 5:14 10 CHARACTER
> >
> > TR_CODE 16:17 2 CHARACTER
> >
> > TR_DESC 19:29 11 CHARACTER
> >
> > SOURCE 32:32 1 CHARACTER
> >
> > TR_AMOUNT 34:45 4 FLOAT
> >
> > TR_DATE 48:55 8 DATE MMDDYYYY
> >
> > NULL if TRANS_DATE = 0X3030303030303030(character '00000000')
> > DD 57:60 4 CHARACTER
> >
> > PL4 62:65 4 CHARACTER
> >
> > PL6 62:67 6 CHARACTER
> >
> > POID 69:70 2 CHARACTER
> >
> > RES_AREA 71:72 2 CHARACTER
> >
> >
> > Record 2: Rejected - Error on table PO_ACTION, column COMPANY.
> > ORA-01722: invalid number
> >
> > Record 3: Rejected - Error on table PO_ACTION, column TR_DATE.
> > ORA-01858: a non-numeric character was found where a numeric was
> > expected
> >
> > Record 4: Rejected - Error on table PO_ACTION, column TR_DATE.
> > ORA-01843: not a valid month
>
> Clearly this data is not in a valid format for SQL*Loader to read it;
> of course I can't prove that since you chose to leave us in the dark
> with regard to your data file.. Again, I'll ask how you transferred
> this file from the IBM mainframe to the Windows PC; my guess (from the
> extremely limited information you've posted) is the file transfer
> wasn't performed properly. Transfer the file again, in ASCII mode (not
> binary, as I suspect is the case for the original transfer) and see if
> SQL*Loader can successfully operate upon it.
>
> I've loaded many files from a Windows PC into an Oracle database on a
> UNIX platform and have had no difficulty. The problem lies in the data
> itself.
>
>
> David Fitzjarrell
Received on Thu Oct 12 2006 - 10:46:52 CDT