External Tables

[Dosth]

Hi There!
I 'm just trying to use external tables in oracle 9i rel 2.0. I'm not sure how i should specify the date format. i.e I have a flatfile which has

'ABC','19/06/1973' how do I specify the format of date.

Any help

Thanks

Dosth

[Sameer]

Hi,

Doesn't matter even if you don't specidy the date format while creating ETL...

Create table external_emp (
ID VARCHAR2(3),
HIREDATE DATE)
Organization external
(type oracle_loader
default directory BLAH
access parameters (records delimited by newline
fields terminated by ','
optionally enclosed by ''' )
location ('extemp.txt'))
reject limit 1000;

HTH

Sameer

[Edited by Sameer on 07-25-2002 at 11:48 AM]

[Dosth]

Thanks Sameer,
I got the solution .

I was trying to give the date format ... Date format can be given like this

(
SITE CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
ENTRY_DATE CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
DATE_FORMAT DATE MASK "DD/MM/YYYY HH24:MI:SS"
)

I'm not sure if the data would be read if date format is not mentioned correctly. Have'nt tried loading it. But logically
oracle won't be able to differentiate between dd/mm and mm/dd but for the values. Hence I presume mask is necessary

Cheers
dosth

[Edited by Dosth on 07-25-2002 at 12:05 PM]