| SQL * LDR DATE FORMATS [message #141011] |
Thu, 06 October 2005 10:44  |
Leonie
Messages: 9
Registered: November 2004
|
Junior Member |
|
|
Hi,
I'm using SQLLDR to load data from a csv into a table.
I have been given some date data that is in a funny format (3rd and 4th bits of data in example below).
210~41016310~2005-09-13 18:02:02.247000000~2005-09-13 17:56:51.687000000
211~41013845~2005-09-14 12:00:50.793000000~2005-09-13 14:00:37.233000000
212~41017065~2005-09-13 11:14:09.043000000~2005-08-23 15:27:02.047000000
I'm told the full stop is a decimal point and extra nine numbers
are fractions of seconds. This data came from a Linux platform.
Is there a date format I can use to get this data to load as a date rather than a varchar? I tried the CTL file below but it fails with ORA-01858: a non-numeric character was found where a numeric was expected
LOAD DATA
INFILE '$XXWHB_TOP/out/wbx_ilrn_loc_sync.csv'
BADFILE 'wbx_ilrn_loc_sync.bad'
DISCARDFILE 'wbx_ilrn_loc_sync.dis'
INTO TABLE OWBRUN.WBX_ILRN_LOCATION_SYNCH_DATE
REPLACE
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
"CLM_LOCATION_ID" ,
"ORACLE_LOCATION_ID" ,
"LAST_SYNC_DATE" DATE "YYYY-MM-DD HH24:MI:SS",
"PREV_SYNC_DATE" DATE "YYYY-MM-DD HH24:MI:SS")
BEGINDATA
Any ideas please?
|
|
|
|
| Re: SQL * LDR DATE FORMATS [message #141024 is a reply to message #141011] |
Thu, 06 October 2005 12:40  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
One option is to overrule the file-wide delimiter with one that is specific to a particular field, and ignore the extra field(s) that it generates:
(in sqlplus)
MYDBA@ORCL > create table test(a number, d date, b number);
Table created.
(in os shell)
e:\scot\sqlpath>sqlldr userid=mydba/orcl control=special_date
SQL*Loader: Release 10.1.0.4.0 - Production on Thu Oct 6 13:37:30 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached - logical record count 2
(back in sqlplus)
MYDBA@ORCL > select * from test;
A D B
---------- -------------------- ----------
1 01-JAN-2005 20:05:10 10
2 05-FEB-2005 10:14:23 20
2 rows selected.
(contents of special_date.ctl)
load data
infile *
truncate into table test
fields terminated by ','
optionally enclosed by '"'
(
a integer external,
d date "DD-MON-YYYY HH24:MI:SS" terminated by '.',
filler1 filler,
b integer external
)
begindata
1,01-JAN-2005 20:05:10.456,10
2,05-FEB-2005 10:14:23.789,20
|
|
|
|
| Re: SQL * LDR DATE FORMATS [message #141155 is a reply to message #141024] |
Fri, 07 October 2005 04:21 |
Leonie
Messages: 9
Registered: November 2004
|
Junior Member |
|
|
Hi,
Thanks v much for the reply, sounds exactly what I need.
But..... I tried what you suggested, copied exact code you suggested above and I get an error;
SQL*Loader: Release 8.0.6.3.0 - Production on Fri Oct 7 10:10:12 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SQL*Loader-350: Syntax error at line 9.
Expecting "," or ")", found "filler".
filler1 filler,
^
/home/oradev15/tmp>
How come it works for you and not me?
|
|
|
|
| Re: SQL * LDR DATE FORMATS [message #141200 is a reply to message #141011] |
Fri, 07 October 2005 08:27 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
Because you are using an oracle version that is like 10 years old, and that is way past a supported state. And I'm using one that is only 1 year old. Oracle has been working over the past 10 years to improve their product, but if you don't upgrade then you can't take advantage of their efforts. If you have the maintenance agreement, upgrading is free.
|
|
|
|
| Re: SQL * LDR DATE FORMATS [message #141206 is a reply to message #141200] |
Fri, 07 October 2005 08:40 |
Leonie
Messages: 9
Registered: November 2004
|
Junior Member |
|
|
Hi,
Agree our test system is old but live is a bit better 9.2.0.4. Still I bet there are others out there not up to date as well.
Anyway I think I have a way round it based on your suggestion.
Thanks
|
|
|
|
| Re: SQL * LDR DATE FORMATS [message #141213 is a reply to message #141011] |
Fri, 07 October 2005 09:03 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
But remember, there is out of date, and then there is OUT OF DATE. 9.2.0.4 is arguably out of date. 8.0 is OUT OF DATE.
10gR2 (here is current)
10gR1
9iR2 (already two releases behind)
9iR1
8iR3
8iR2
8iR1
8 (you are here, seven releases behind)
You are right though, many people still on 8iR3.
Glad it helped though, have a good one.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
