Home » RDBMS Server » Server Utilities » Nested functions in SQL Loader Control File.
Nested functions in SQL Loader Control File. [message #115998] Fri, 15 April 2005 17:40 Go to next message
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 #116006 is a reply to message #115998] Fri, 15 April 2005 20:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
LOAD DATA
INFILE 'your_ascii_text_data_file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
(
-- other columns,
event_start_dtm "TO_DATE (REPLACE (REPLACE (:event_start_dtm, 'T'), 'Z'), 'YYYYMMDDHH24MISS')"
-- , other columns
)
Re: Nested functions in SQL Loader Control File. [message #116227 is a reply to message #116006] Mon, 18 April 2005 12:12 Go to previous messageGo to next message
xenotech
Messages: 2
Registered: April 2005
Junior Member
Yes you are correct.
Infact my control file line is as you have indicated, and the error still persists.

TO_DATE (REPLACE (REPLACE (:event_start_dtm, 'T'), 'Z'), 'YYYYMMDDHH24MISS') still causes ORA-01812 - "Date format is not recognized"
Re: Nested functions in SQL Loader Control File. [message #116375 is a reply to message #116227] Tue, 19 April 2005 13:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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> 


Re: Nested functions in SQL Loader Control File. [message #332781 is a reply to message #116375] Wed, 09 July 2008 12:27 Go to previous message
rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England
Junior Member
Barbara,
thanks for the example you have saved my migration.


THANK YOU
Previous Topic: sequences & IMP-00015: following statement failed because the object already exists:
Next Topic: Explain Plan
Goto Forum:
  


Current Time: Sat Dec 10 08:54:48 CST 2016

Total time taken to generate the page: 0.07635 seconds