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

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:20:11 CDT