| Nested functions in SQL Loader Control File. [message #115998] |
Fri, 15 April 2005 17:40  |
xenotech
Messages: 2
Registered: April 2005
|
Junior Member |
|
|
I have a control file that tries to take a date string, strip 2 characters (T and Z) from it and then convert it to a date. I get the error "Date format is not recognized". If I use the same function calls within a simple insert statement, it works fine...so the error message seems to be a bit of a redherring.
The incoming data file format for dates is "YYYYMMDDTHHMISSZ"
Any advice or help would be appreciated. Here is the line from the control file.
EVENT_START_DTM DATE "TO_DATE(REPLACE(REPLACE('20050415T063030Z', 'T'), 'Z'), 'YYYYMMDDHHMISS')" TERMINATED BY ',',
Thanks in advance.
Bill
|
|
|
|
|
|
|
|
| Re: Nested functions in SQL Loader Control File. [message #116375 is a reply to message #116227] |
Tue, 19 April 2005 13:56  |
 |
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please see the demonstration below that executes without problems. You will need to post what is different, such as some sample data, your control file, your table structure, how you are trying to load it, and the resulting log file. You need to post actual copy and paste, to avoid errors in retyping.
-- contents of test.dat:
20050415T063030Z,
20050419T105042Z,
-- contents of test.ctl:
load data
infile 'test.dat'
into table test_tab
fields terminated by ','
(
event_start_dtm "TO_DATE (REPLACE (REPLACE (:event_start_dtm, 'T'), 'Z'), 'YYYYMMDDHH24MISS')"
)
-- table:
scott@ORA92> create table test_tab
2 (event_start_dtm date)
3 /
Table created.
-- load data:
scott@ORA92> host sqlldr scott/tiger control=test.ctl log=test.log
-- results:
scott@ORA92> select * from test_tab
2 /
EVENT_START_DTM
--------------------
15-APR-2005 06:30:30
19-APR-2005 10:50:42
scott@ORA92>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
