Re: SQL Loader problem

From: japlha <japlha_at_sympatico.ca>
Date: Tue, 25 Dec 2001 17:54:19 -0500
Message-ID: <3C29039B.392574DC_at_sympatico.ca>

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 Tue Dec 25 2001 - 23:54:19 CET