Hi,
I am having problem in using TO_TIMESTAMP_TZ function in SQLLDR.
Table definition:
desc ts_table
Name Null Type
------------ ---- ---------------------------------
REQUEST_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE
Source data:
[oracle@mywligoa01 onewb-loganalyzer]$ cat ts_table.dat
07/Sep/2013:13:04:44 +0700
07/Sep/2013:13:04:44 +0800
07/Sep/2013:13:04:44 +0900
Control File:
[oracle@mywligoa01 onewb-loganalyzer]$ cat ts_table.ctl
LOAD DATA
INFILE 'ts_table.dat'
TRUNCATE INTO TABLE TS_TABLE
FIELDS terminated by " " optionally enclosed by '"' trailing nullcols
(
REQUEST_TIME "TO_TIMESTAMP_TZ(:REQUEST_TIME,'DD/MON/YYYY HH24:MI:SS TZHTZM')"
)
Response:
[oracle@mywligoa01 onewb-loganalyzer]$ sh exec_sqlldr_file.sh log ts_table.ctl
Start Time: Mon Oct 21 11:32:40 SGT 2013
SQLLOG_FILE is: ts_table.ctl_113240211013.log
Connecting to occss_entmt_hk_dev/OCCSS_ENTMT_HK_DEV_123@hklpdudasb-scan.hk.standardchartered.com:1622/OCCSS_DEV_01.hk.standardchartered.com
ts_table.ctl
SQL*Loader-951: Error calling once/load initialization
ORA-26052: Unsupported type 231 for SQL expression on column REQUEST_TIME.
SQL*Loader: Release 11.2.0.1.0 - Production on Mon Oct 21 11:32:40 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
End Time: Mon Oct 21 11:32:44 SGT 2013
Am I missing something? And the other inference is:
> drop table ts_table
table TS_TABLE dropped.
> create table ts_table(request_time timestamp)
table TS_TABLE created.
> desc ts_table
desc ts_table
Name Null Type
------------ ---- ------------
REQUEST_TIME TIMESTAMP(6)
With same data file and control file calling the sqlldr again.
[oracle@mywligoa01 onewb-loganalyzer]$ sh exec_sqlldr_file.sh log ts_table.ctl
Start Time: Mon Oct 21 11:44:34 SGT 2013
SQLLOG_FILE is: ts_table.ctl_114434211013.log
Connecting to occss_entmt_hk_dev/OCCSS_ENTMT_HK_DEV_123@hklpdudasb-scan.hk.standardchartered.com:1622/OCCSS_DEV_01.hk.standardchartered.com
ts_table.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on Mon Oct 21 11:44:34 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Load completed - logical record count 3.
End Time: Mon Oct 21 11:44:37 SGT 2013
[oracle@mywligoa01 onewb-loganalyzer]$
Table data: But there is not effect of time zone.
> select * from ts_table
REQUEST_TIME
-------------------------------
07-SEP-13 01.04.44.000000000 PM
07-SEP-13 01.04.44.000000000 PM
07-SEP-13 01.04.44.000000000 PM