Home » RDBMS Server » Server Utilities » sql loader: no record inserted (8i, winXP)
sql loader: no record inserted [message #298476] Wed, 06 February 2008 03:15 Go to next message
rzkhan
Messages: 370
Registered: March 2005
Senior Member
 
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
  • Attachment: scan.txt
    (Size: 1.14KB, Downloaded 1293 times)

Report message to a moderator

Re: sql loader: no record inserted [message #298478 is a reply to message #298476] Wed, 06 February 2008 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
FIELDS TERMINATED BY ','

Fields are not terminated by ',' in your file but by tabulation (chr(9)).

Regards
Michel

Report message to a moderator

Re: sql loader: no record inserted [message #298479 is a reply to message #298478] Wed, 06 February 2008 03:24 Go to previous messageGo to next message
rzkhan
Messages: 370
Registered: March 2005
Senior Member
it also shows error

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:27:37 2008

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL*Loader-350: Syntax error at line 7.
Expecting quoted string, found "(".
FIELDS TERMINATED BY (chr(9))
                     ^

C:\>sqlldr et/et

control = d:\et\control.txt

SQL*Loader: Release 8.1.7.0.0 - Production on Wed Feb 6 14:28:09 2008

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL*Loader-350: Syntax error at line 7.
Expecting quoted string, found "chr".
FIELDS TERMINATED BY chr(9)
                     ^

C:\>


Report message to a moderator

Re: sql loader: no record inserted [message #298483 is a reply to message #298479] Wed, 06 February 2008 03:42 Go to previous messageGo to next message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does "FIELDS TERMINATED BY WHITESPACE" do any good?

Report message to a moderator

Re: sql loader: no record inserted [message #298488 is a reply to message #298479] Wed, 06 February 2008 04:01 Go to previous message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I gave the idea not the syntax.
I thought you were smart enough to have a look to documentation by yourself.

Regards
Michel

Report message to a moderator

Previous Topic: Importing data from oracle 9i to Oracle 10g
Next Topic: How to increase perfromance while loading data into IOT's?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jun 16 03:06:22 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.