I have a table (as below). I am in need to insert data from a fixed width text file using sqlldr.
----table script
create table t_intermediate
(
codeno number(3),
cardno number(5),
a1 varchar2(1),
a2 varchar2(1),
a3 varchar2(1),
a4 varchar2(1),
a5 varchar2(1),
a6 varchar2(1),
a7 varchar2(1),
a8 varchar2(1),
a9 varchar2(1),
a10 varchar2(1),
a11 varchar2(1),
a12 varchar2(1),
a13 varchar2(1),
a14 varchar2(1),
a15 varchar2(1),
a16 varchar2(1),
a17 varchar2(1),
a18 varchar2(1),
a19 varchar2(1),
a20 varchar2(1),
a21 varchar2(1),
a22 varchar2(1),
a23 varchar2(1),
a24 varchar2(1),
a25 varchar2(1),
a26 varchar2(1),
a27 varchar2(1),
a28 varchar2(1),
a29 varchar2(1),
a30 varchar2(1),
a31 varchar2(1),
a32 varchar2(1),
a33 varchar2(1),
a34 varchar2(1),
a35 varchar2(1),
a36 varchar2(1),
a37 varchar2(1),
a38 varchar2(1),
a39 varchar2(1),
a40 varchar2(1),
a41 varchar2(1),
a42 varchar2(1),
a43 varchar2(1),
a44 varchar2(1),
a45 varchar2(1),
a46 varchar2(1),
a47 varchar2(1),
a48 varchar2(1),
a49 varchar2(1),
a50 varchar2(1),
a51 varchar2(1),
a52 varchar2(1),
a53 varchar2(1),
a54 varchar2(1),
a55 varchar2(1),
a56 varchar2(1),
a57 varchar2(1),
a58 varchar2(1),
a59 varchar2(1),
a60 varchar2(1),
b1 varchar2(1),
b2 varchar2(1),
b3 varchar2(1),
b4 varchar2(1),
b5 varchar2(1),
b6 varchar2(1),
b7 varchar2(1),
b8 varchar2(1),
b9 varchar2(1),
b10 varchar2(1),
b11 varchar2(1),
b12 varchar2(1),
b13 varchar2(1),
b14 varchar2(1),
b15 varchar2(1),
b16 varchar2(1),
b17 varchar2(1),
b18 varchar2(1),
b19 varchar2(1),
b20 varchar2(1)
)
/
---- Data File Attached
please see attached file
----control file
LOAD DATA
INFILE 'd:\et\scan.txt'
badfile 'd:\et\bad.txt'
discardfile 'd:\et\disc.txt'
INTO TABLE t_intermediate
INSERT
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(cardno,codeno,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a2
6,a27,a28,a29,a30,a31,a32,a33,a34,a35,a36,a37,a38,a39,a40,a41,a42,a43,a44,a45,a46,a47,a48,a49,a50,
a51,a52,a53,a54,a55,a56,a57,a58,a59,a60,b1,b2,b3,b4,b5,b6,b7,b8,b9,b10,b11,b12,b13,b14,b15,b16,b17,b18,b19,b
20)
---I load as below
---at SQLLDR it shows
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>cd\
C:\>sqlldr et/et
control = d:\et\control.txt
SQL*Loader: Release 8.1.7.0.0 - Production on Wed Feb 6 14:08:16 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 3
Commit point reached - logical record count 6
Commit point reached - logical record count 7
C:\>sqlldr et/et
control = d:\et\control.txt
SQL*Loader: Release 8.1.7.0.0 - Production on Wed Feb 6 14:10:36 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 3
Commit point reached - logical record count 6
Commit point reached - logical record count 7
C:\>
---- log file
SQL*Loader: Release 8.1.7.0.0 - Production on Wed Feb 6 12:26:47 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Control File: d:\et\control.txt
Data File: d:\et\scan.txt
Bad File: c:\bad.txt
Discard File: c:\disc.txt
(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 T_INTERMEDIATE, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CARDNO FIRST * , O(") CHARACTER
CODENO NEXT * , O(") CHARACTER
A1 NEXT * , O(") CHARACTER
A2 NEXT * , O(") CHARACTER
A3 NEXT * , O(") CHARACTER
A4 NEXT * , O(") CHARACTER
A5 NEXT * , O(") CHARACTER
A6 NEXT * , O(") CHARACTER
A7 NEXT * , O(") CHARACTER
A8 NEXT * , O(") CHARACTER
A9 NEXT * , O(") CHARACTER
A10 NEXT * , O(") CHARACTER
A11 NEXT * , O(") CHARACTER
A12 NEXT * , O(") CHARACTER
A13 NEXT * , O(") CHARACTER
A14 NEXT * , O(") CHARACTER
A15 NEXT * , O(") CHARACTER
A16 NEXT * , O(") CHARACTER
A17 NEXT * , O(") CHARACTER
A18 NEXT * , O(") CHARACTER
A19 NEXT * , O(") CHARACTER
A20 NEXT * , O(") CHARACTER
A21 NEXT * , O(") CHARACTER
A22 NEXT * , O(") CHARACTER
A23 NEXT * , O(") CHARACTER
A24 NEXT * , O(") CHARACTER
A25 NEXT * , O(") CHARACTER
A26 NEXT * , O(") CHARACTER
A27 NEXT * , O(") CHARACTER
A28 NEXT * , O(") CHARACTER
A29 NEXT * , O(") CHARACTER
A30 NEXT * , O(") CHARACTER
A31 NEXT * , O(") CHARACTER
A32 NEXT * , O(") CHARACTER
A33 NEXT * , O(") CHARACTER
A34 NEXT * , O(") CHARACTER
A35 NEXT * , O(") CHARACTER
A36 NEXT * , O(") CHARACTER
A37 NEXT * , O(") CHARACTER
A38 NEXT * , O(") CHARACTER
A39 NEXT * , O(") CHARACTER
A40 NEXT * , O(") CHARACTER
A41 NEXT * , O(") CHARACTER
A42 NEXT * , O(") CHARACTER
A43 NEXT * , O(") CHARACTER
A44 NEXT * , O(") CHARACTER
A45 NEXT * , O(") CHARACTER
A46 NEXT * , O(") CHARACTER
A47 NEXT * , O(") CHARACTER
A48 NEXT * , O(") CHARACTER
A49 NEXT * , O(") CHARACTER
A50 NEXT * , O(") CHARACTER
A51 NEXT * , O(") CHARACTER
A52 NEXT * , O(") CHARACTER
A53 NEXT * , O(") CHARACTER
A54 NEXT * , O(") CHARACTER
A55 NEXT * , O(") CHARACTER
A56 NEXT * , O(") CHARACTER
A57 NEXT * , O(") CHARACTER
A58 NEXT * , O(") CHARACTER
A59 NEXT * , O(") CHARACTER
A60 NEXT * , O(") CHARACTER
B1 NEXT * , O(") CHARACTER
B2 NEXT * , O(") CHARACTER
B3 NEXT * , O(") CHARACTER
B4 NEXT * , O(") CHARACTER
B5 NEXT * , O(") CHARACTER
B6 NEXT * , O(") CHARACTER
B7 NEXT * , O(") CHARACTER
B8 NEXT * , O(") CHARACTER
B9 NEXT * , O(") CHARACTER
B10 NEXT * , O(") CHARACTER
B11 NEXT * , O(") CHARACTER
B12 NEXT * , O(") CHARACTER
B13 NEXT * , O(") CHARACTER
B14 NEXT * , O(") CHARACTER
B15 NEXT * , O(") CHARACTER
B16 NEXT * , O(") CHARACTER
B17 NEXT * , O(") CHARACTER
B18 NEXT * , O(") CHARACTER
B19 NEXT * , O(") CHARACTER
B20 NEXT * , O(") CHARACTER
Record 1: Rejected - Error on table T_INTERMEDIATE, column CARDNO.
ORA-01722: invalid number
Record 2: Rejected - Error on table T_INTERMEDIATE, column CARDNO.
ORA-01722: invalid number
Record 3: Rejected - Error on table T_INTERMEDIATE, column CARDNO.
ORA-01722: invalid number
Record 4: Rejected - Error on table T_INTERMEDIATE, column CARDNO.
ORA-01722: invalid number
Record 5: Rejected - Error on table T_INTERMEDIATE, column CARDNO.
ORA-01722: invalid number
Record 6: Rejected - Error on table T_INTERMEDIATE, column CARDNO.
ORA-01722: invalid number
Record 7: Rejected - Error on table T_INTERMEDIATE, column CARDNO.
ORA-01722: invalid number
Table T_INTERMEDIATE:
0 Rows successfully loaded.
7 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: 63468 bytes(3 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 7
Total logical records discarded: 0
Run began on Wed Feb 06 12:26:47 2008
Run ended on Wed Feb 06 12:26:54 2008
Elapsed time was: 00:00:06.87
CPU time was: 00:00:00.03
------
Please help me any guy to resolve the problem.
Thanks
rzkhan
] 
Blog
Search
Help
Members
Register
Login
Home
