-
Hi all, I am using SQL Loader to transfer data from a text file (variable length, tab delimited) onto a table. I have a date field in the table and the corresponding value in the text file is like "2002-04-01 00:00:00".
This is the format in my control file :
(Resrv_date DATE "YYYY-MM-DD HH24:MM:SS")
But, I get the error :
Record1 - Rejected - Error on table xxx, column RESRV_DATE. Field in data file exceeds maximum length.
But when I exp/imp the data or use SQL's DTS, all works fine. Any help is greatly appreciated.
Thanks,
Shiva.
-
u have little error in date format:
Resrv_date DATE "YYYY-MM-DD HH24:MM:SS"
should be
Resrv_date DATE "YYYY-MM-DD HH24:MI:SS"
May be it help.
-
Thanks for the suggestion, but it still get the same error.
Shiva.
-
Try:
Resrv_date DATE to_date(:Resrv_date,"YYYY-MM-DD HH24:MM:SS")
-
Here is my control file :
LOAD DATA
INFILE '1.txt'
INSERT INTO TABLE test_bam_util
TRUNCATE
REENABLE DISABLED_CONSTRAINTS
FIELDS TERMINATED BY '\t'
TRAILING NULLCOLS
(resrv_date DATE "YYYY-MM-DD HH24:MM:SS",
router CHAR,
if_name CHAR,
dummy1 FILLER,
dummy2 FILLER,
inbitssec CHAR)
If I use FIELDS TERMINATED BY WHITESPACE instead of '\t', I am able to get the data into the table, but the date data "2002-04-01 00:00:00" gets spilt into 2 seperate fields - "2002-04-01" and "00:00:00" - because of the space and hence in my table, the values I have are like :
resrv_date = "2002-04-01 00:00:00"
router = "00:00:00"
When I used the TO_DATE() functionality here, as in
resrv_date DATE "TO_DATE(:resrv_date,'YYYY-MM-DD HH24:MI:SS')", this is the error I get :
ORA-01821: date format not recognized
Any ideas/suggestions ??
Thanks,
Shiva.
[Edited by Shiva on 05-08-2002 at 12:56 PM]
-
Some code for impuve ur script:
...
FIELDS TERMINATED BY '\t' OPTIONALY ENCLOSED '"'
TRAILING NULLCOLS
...
(
resrv_date DATE to_date(:resrv_date||' '||:router, 'YYYY-MM-DD H24:MM:SS'),
...
in to_date u should use ' (not")
-
Hi Shestakov, I used
FIELDS TERMINATED BY WHITESPACE
TRAILING NULLCOLS
(entry DATE "YYYY-MM-DD HH24:MI:SS",
router position(25:39) CHAR terminated by whitespace,
if_name CHAR,
dummy1 FILLER,
dummy2 FILLER,
inbitssec CHAR)
and it works great.
I could never get the to_date() function to work though, it would be nice to know why it did not, but as long as I get it to work, I am fine.
Thanks for your continued help.
Shiva.
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
|