Re: SQL Loader problem

Joe,

Did you transfer the files in binary mode? If so you will have Carriage Return/ Line feed at the end of each line, instead of just the Line Feed that unix is expecting.
If you look at the file in vi it will appear to have a ^M after each line. You could ftp the files again in ascii mode or get rid of the extraneous carriage returns by hand
or with a script.

Will Carey

japlha wrote:

> Greg Kainz wrote:
>
> > "Joe Moore" <japlha_at_sympatico.ca> wrote in message
> > news:miMV7.5443$sg.980203_at_news20.bellglobal.com...
> > > I have been trying to load data from text files into my oracle database.
> > > I created the data files on Windows 2000 and transferred them to my Linux
> > > box. The data file are in text format using comma's for the column
> > > delimeters.
> > > When I run the sqlldr command I get this error message for each row:
> > > Record 1: Rejected - Error on table EMP, column DEPTNO,
> > > ORA-01722: invalid number
> > > Record 2: Rejected - Error on table EMP, column DEPTNO,
> > > ORA-01722: invalid number
> > > Record 3: Rejected - Error on table EMP, column DEPTNO,
> > > ORA-01722: invalid number
> > > ...
> > > Any ideas why this is happening?
> > >
> > > Thanks
> > >
> > Lots of ideas; none very helpful unless maybe you post your control file, a
> > snippet of data and maybe the table descriptions... then maybe we can
> > provide a little more focused help...sorry if this sounds snippy - not my
> > intention.
> > Merry Christmas
> > Greg Kainz
>
> I have included my control file, table description, input file and log file.
>
> This is new stuff for me. I'm trying to recreate the data I used in my Oracle
> Course.
> I know I could use insert statements but I will need to know how to use the
> SQL Loader.
> My recent course didn't cover imports and exports all that much.
> I appreciate any help
>
> Thanks again
> Joe
>
> ------------------------------------------------------------------------
> LOAD DATA
> INFILE '/exports/EMP.TXT'
> TRUNCATE
> INTO TABLE EMP
> FIELDS TERMINATED BY ","
> (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
>
> ------------------------------------------------------------------------
> create table emp(
> EMPNO number(4) not null,
> ename varchar2(10),
> job varchar2(9),
> mgr number(4),
> hiredate date,
> sal number(7,2),
> comm number(7,2),
> deptno number(2) not null)
> /
>
> ------------------------------------------------------------------------
> 7839,KING,PRESIDENT,,17-NOV-1981,5000,,10
> 7698,BLACKE,MANAGER,7839,01-MAY-1981,2850,,30
> 7782,CLARK,MANAGER,7839,09-JUN-1981,2450,,10
> 7566,JONES,MANAGER,7839,02-APR-1981,2975,,20
> 7654,MARTIN,SALESMAN,7698,28-SEP-1981,1250,1400,30
> 7499,ALLEN,SALESMAN,7698,20-FEB-1981,1600,300,30
> 7844,TURNER,SALESMAN,7698,08-SEP-1981,1500,0,30
> 7900,JAMES,CLERK,7698,03-DEC-1981,950,,30
> 7521,WARD,SALESMAN,7698,22-FEB-1981,1250,500,30
> 7902,FORD,ANALYST,7566,03-DEC-1981,3000,,20
> 7369,SMITH,CLERK,7902,17-DEC-1980,800,,20
> 7788,SCOTT,ANALYST,7566,09-DEC-1982,3000,,20
> 7876,ADAMS,CLERK,7788,12-JAN-1983,1100,,20
> 7934,MILLER,CLERK,7782,23-JAN-1982,1300,,10
>
> ------------------------------------------------------------------------
>
> SQL*Loader: Release 8.1.7.0.1 - Production on Mon Dec 24 15:25:43 2001
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> Control File: /u01/app/oracle/product/8.1.7/sqlplus/admin/emp.dat
> Data File: /exports/EMP.TXT
> Bad File: /u01/app/oracle/product/8.1.7/sqlplus/admin/EMP.bad
> Discard File: none specified
>
> (Allow all discards)
>
> Number to load: ALL
> Number to skip: 0
> Errors allowed: 50
> Bind array: 64 rows, maximum of 65536 bytes
> Continuation: none specified
> Path used: Conventional
>
> Table EMP, loaded from every logical record.
> Insert option in effect for this table: TRUNCATE
>
> Column Name Position Len Term Encl Datatype
> ------------------------------ ---------- ----- ---- ---- ---------------------
> EMPNO FIRST * , CHARACTER
> ENAME NEXT * , CHARACTER
> JOB NEXT * , CHARACTER
> MGR NEXT * , CHARACTER
> HIREDATE NEXT * , CHARACTER
> SAL NEXT * , CHARACTER
> COMM NEXT * , CHARACTER
> DEPTNO NEXT * , CHARACTER
>
> Record 1: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 2: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 3: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 4: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 5: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 6: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 7: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 8: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 9: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 10: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 11: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 12: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 13: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Record 14: Rejected - Error on table EMP, column DEPTNO.
> ORA-01722: invalid number
>
> Table EMP:
> 0 Rows successfully loaded.
> 14 Rows not loaded due to data errors.
> 0 Rows not loaded because all WHEN clauses were failed.
> 0 Rows not loaded because all fields were null.
>
> Space allocated for bind array: 63984 bytes(31 rows)
> Space allocated for memory besides bind array: 0 bytes
>
> Total logical records skipped: 0
> Total logical records read: 14
> Total logical records rejected: 14
> Total logical records discarded: 0
>
> Run began on Mon Dec 24 15:25:43 2001
> Run ended on Mon Dec 24 15:25:43 2001
>
> Elapsed time was: 00:00:00.31
> CPU time was: 00:00:00.08
Received on Wed Dec 26 2001 - 07:05:36 CET