OraFAQ Forum: Server Utilities » SQL*Loader (sqlldr) Timezone Import "UTC"

Home » RDBMS Server » Server Utilities » SQL*Loader (sqlldr) Timezone Import "UTC" (10.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Loader (sqlldr) Timezone Import "UTC" [message #534860]

Fri, 09 December 2011 14:41

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 1845 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

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

-- test.ctl:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: SQL*Loader (sqlldr) Timezone Import "UTC" [message #534866 is a reply to message #534865]

Fri, 09 December 2011 15:25

mike926
Messages: 2
Registered: December 2011
Location: United States

Junior Member

O gee I was so Close! Wow Thank you.

Code: [Select all] [Show/ hide]

  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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 12:39:01 CDT 2025