sqlldr issue!!!

[ehspacl]

Has anyone got a user guide for sqlldr, having problems!!!
Or
Anyone help with this install, I have a copy of the ctl file,the csv used and the loadlog results. Can't seem to get the right datatype. The table's 3 columns are all datatype number(38).

ctl file
LOAD DATA
INFILE gaamaa_test.csv
APPEND INTO TABLE mfi_audit_test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(BATCH_NUMBER POSITION(1:8) DECIMAL EXTERNAL,
TRANSACTION_NUMBER POSITION(12:15) DECIMAL EXTERNAL,
LINE_NUMBER POSITION(19:22) DECIMAL EXTERNAL
)

csv file
"00359429","0001","0001"
"00359429","0002","0001"

Loadlog
SQL*Loader: Release 8.1.7.0.0 - Production on Thu May 20 11:01:17 2004

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

Control File: /trans/scp/scripts/gaamaa_test.ctl
Data File: /trans/scp/gaamaa_test.csv
Bad File: /trans/scp/gaamaa_test.bad.csv
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 9999
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK
Table MFI_AUDIT_TEST, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
BATCH_NUMBER 1:8 8 , O(") CHARACTER
TRANSACTION_NUMBER 12:15 4 , O(") CHARACTER
LINE_NUMBER 19:22 4 , O(") CHARACTER

Record 1: Rejected - Error on table MFI_AUDIT_TEST, column BATCH_NUMBER.
ORA-01722: invalid number

Record 2: Rejected - Error on table MFI_AUDIT_TEST, column BATCH_NUMBER.
ORA-01722: invalid number


Table MFI_AUDIT_TEST:
0 Rows successfully loaded.
2 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: 1408 bytes(64 rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 2
Total logical records discarded: 0

Run began on Thu May 20 11:01:17 2004
Run ended on Thu May 20 11:01:18 2004

Elapsed time was: 00:00:00.21
CPU time was: 00:00:00.07

 

[aryeh1010]

I've gotten this error message when sql*loader encountered nulls in the data.

Try using nullif in the control file

<column_name> nullif <column_name>=blanks


Aryeh Keefe

 

[ehspacl]

Thanks for your reply but the csv file is included in my first post and it doesn't have any nulls:-

csv file
"00359429","0001","0001"
"00359429","0002","0001"

 

[Turkbear]

Hi,
Remove the POSITION specifications from your CTL file; they are not used when reading a file that is delimited.

 

[ddrillich]

As Turkbear said, please remove the POSITION specifications from the control file.

The following control file is working for me:
LOAD DATA
APPEND INTO TABLE tst
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
BATCH_NUMBER DECIMAL EXTERNAL,
TRANSACTION_NUMBER DECIMAL EXTERNAL,
LINE_NUMBER DECIMAL EXTERNAL
)

with the following table:

create table tst
(
BATCH_NUMBER NUMBER,
TRANSACTION_NUMBER NUMBER,
LINE_NUMBER NUMBER
)

Regards,
Dan