Wednesday, July 8, 2009

Timestamp in an External Table

Since this wasn't particularly easy to find, and it took several tries, I decided to post it here. As most things, once you know how to write it, it's not particularly tricky.

Input Data from External Table:
1,Jan 8 1998 12:43:27:012AM
2,Feb 19 1998 2:43:27:111PM
3,Jan 8 2008 3:49:27:693PM


Create Table Ext_Dt
( Item Number
, Needed_By Timestamp
)
Organization External
(
Type Oracle_Loader
Default Directory Ext
Access Parameters
(
Records Delimited By Newline
Badfile 'Date.Bad'
Logfile 'Date.Log'
Fields Terminated By ','
Missing Field Values Are Null
(
Item,
Needed_By Char Date_Format Timestamp Mask 'Mon Dd Yyyy Hh:Mi:Ss:Ff3am'
)
)
Location (Ext:'Dt.Txt')
)
Reject Limit Unlimited;

Select Item
, Needed_By
, To_Char(Needed_By,'Mon' ) Mth
, To_Char(Needed_By,'Hh24') Hr
From Ext_Dt;

2 comments:

  1. Thank you for putting an effort to published this article. You've done a great job! Good bless!

    Caren
    www.gofastek.com

    ReplyDelete
  2. Great contribution it help me a lot.

    ReplyDelete