-
Hi folks,
I have a very simple test scnario of SQL Loader. My control file looks like this :
LOAD DATA
INTO TABLE temp1
FIELDS TERMINATED BY '~'
(
col1 Integer,
col2 Char,
col3 Integer,
col4 date 'mm-dd-yyyy'
)
And my test data file is as follows.
123456~Mycompany~9568787~12-17-2001
3123456~Mycompany one~895687878~11-18-2001
It is failing with the following error:
SQL*Loader: Release 9.0.1.1.1 - Production on Mon Feb 4 09:10:20 2002
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Control File: temp1.ctl
Data File: temp1.txt
Bad File: temp1.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 256000 bytes
Continuation: none specified
Path used: Conventional
Table TEMP1, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1 FIRST 4 INTEGER
COL2 NEXT * ~ CHARACTER
COL3 NEXT 4 INTEGER
COL4 NEXT * ~ DATE mm-dd-yyyy
Record 1: Rejected - Error on table TEMP1, column COL4.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 2: Rejected - Error on table TEMP1, column COL4.
ORA-01858: a non-numeric character was found where a numeric was expected
Table TEMP1:
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: 33536 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 2
Total logical records discarded: 0
Run began on Mon Feb 04 09:10:20 2002
Run ended on Mon Feb 04 09:10:21 2002
Elapsed time was: 00:00:00.27
CPU time was: 00:00:00.16
I also tried with only 3 cols. (excluding the date col. ) It worked fine. It worked fine with fixed lendth too. Failing only in delimited case. Why so?
Also tried with the control file like this.
..
..
col1 Integer,
col2 Char,
col3 Integer,
col4 "to_date(substr(:col4,1,5)||'-'||substr(:col4,7,4), 'mm-dd-yyyy')"
No luck. Can any one help me?
Thanks in advance
M S Reddy
-
I got it by adding Integer External.
LOAD DATA
INTO TABLE temp1
FIELDS TERMINATED BY '~'
(
col1 Integer external,
col2 Char,
col3 Integer external,
col4 date 'mm-dd-yyyy'
)
Thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|