This discussion is archived
3 Replies Latest reply: Oct 21, 2013 3:35 AM by DK2010 RSS

TO_TIMESTAMP_TZ function in SQLLDR

Boopathy Vasagam Newbie
Currently Being Moderated

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 
  • 1. Re: TO_TIMESTAMP_TZ function in SQLLDR
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    seem you missed the colon in control file  you can try

     

    1. LOAD DATA 
    2. INFILE 'ts_table.dat' 
    3. TRUNCATE INTO TABLE TS_TABLE 
    4. FIELDS terminated by " " optionally enclosed by '"' trailing nullcols 
    5. REQUEST_TIME "TO_TIMESTAMP_TZ(:REQUEST_TIME,'DD/MON/YYYY HH24:MI:SS TZH:TZM')" 

    HTH

  • 2. Re: TO_TIMESTAMP_TZ function in SQLLDR
    Boopathy Vasagam Newbie
    Currently Being Moderated

    Hi DK2010,

    Same error

  • 3. Re: TO_TIMESTAMP_TZ function in SQLLDR
    DK2010 Guru
    Currently Being Moderated

    Hi,

    Check this, its working for me

     

    SQL> desc TS_TABLE
     Name                                                                                                              Null?    Type
     ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
     REQUEST_TIME                                                                                                               TIMESTAMP(6) WITH LOCAL TIME ZONE
    SQL> !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
    SQL> !cat ts.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 TZH:TZM')"  
    )  
    SQL> host sqlldr me/me control=ts.ctl
    SQL*Loader: Release 11.2.0.2.0 - Production on Sat Sep 14 04:30:43 2013
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    Commit point reached - logical record count 3

     

     

    HTH

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points