Home » RDBMS Server » Server Utilities » SQL*Loader (sqlldr) Timezone Import "UTC" (10.2.0.1.0)
icon9.gif  SQL*Loader (sqlldr) Timezone Import "UTC" [message #534860] Fri, 09 December 2011 14:41 Go to next message
mike926
Messages: 2
Registered: December 2011
Location: United States
Junior Member
Real Simple Question that I can't find anyplace.

Data I want to import:


"CHAR1","CHAR2","CHAR3","CHAR4","2011-12-07 18:20:12 UTC","CHAR5"

Using the sqlldr control files how to you import the date time with the "UTC" converting it to a non-timezone enabled table?

Tried:
datein timestamp "YYYY-MM-DD HH24:MI:SS TZR",

Failed: only works with TO_TIMESTAMP_TZ function

So I want to import and convert it to the current localtime in the sqlldr control file. Is that possible. Send option is to drop the UTC completely and even that I don't see as a ignore characters option.

Examples:
SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 UTC','YYYY-MM-DD HH:MI:SS TZR') FROM DUAL


Thanks

Mike
  • Attachment: links.txt
    (Size: 0.17KB, Downloaded 1425 times)

[Updated on: Fri, 09 December 2011 15:02]

Report message to a moderator

Re: SQL*Loader (sqlldr) Timezone Import "UTC" [message #534865 is a reply to message #534860] Fri, 09 December 2011 15:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
-- test.ctl:
load data
infile *
into table test_tab
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(col1, col2, col3, col4,
datein "to_timestamp_tz (:datein, 'yyyy-mm-dd hh24:mi:ss tzr')")
begindata:
"CHAR1","CHAR2","CHAR3","CHAR4","2011-12-07 18:20:12 UTC","CHAR5"


-- table, load, and results:
SCOTT@orcl_11gR2> create table test_tab
  2    (col1	varchar2(5),
  3  	col2	varchar2(5),
  4  	col3	varchar2(5),
  5  	col4	varchar2(5),
  6  	datein	timestamp)
  7  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> select * from test_tab
  2  /

COL1  COL2  COL3  COL4
----- ----- ----- -----
DATEIN
---------------------------------------------------------------------------
CHAR1 CHAR2 CHAR3 CHAR4
07-DEC-11 06.20.12.000000 PM


1 row selected.

Re: SQL*Loader (sqlldr) Timezone Import "UTC" [message #534866 is a reply to message #534865] Fri, 09 December 2011 15:25 Go to previous message
mike926
Messages: 2
Registered: December 2011
Location: United States
Junior Member
O gee I was so Close! Wow Thank you.

  10 Rows successfully loaded.


(have more than 10)

Thank you very much. Very useful info could not find it anyplace on how to do that. Did not realize you can do that with to_timestamp_tz Thanks!

Mike
Previous Topic: return a column value using Sqlloader after loading
Next Topic: Directories in data pump
Goto Forum:
  


Current Time: Thu Mar 28 15:08:58 CDT 2024