On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

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

1 comment:

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