modify oracle externat table date column format

Question

I have an external table linked to csv file like this:

create table XT_FIN  
(
  ba_date                   DATE
)  
organization external
(  
  type ORACLE_LOADER  
  default directory XT_C  
  access parameters   
  (  
    records delimited by newline  
           badfile     XT_LOGS:'f.bad'  
           logfile     XT_LOGS:'f.log'  
           discardfile XT_LOGS:'f.dsc'  
           skip 1  
           fields  terminated by ',' optionally enclosed by '"'  
           lrtrim  
           missing field values are null  
                  (  

    BA_DATE         DATE 'MM/DD/YYYY HH:MI:SS am'  
                  )
  ) 
  location (XT_C:'detail.csv')
)  
reject limit UNLIMITED;

CSV file datetime format changed to 24 hours, so I wanted to chnage it in this table using this statement:

ALTER TABLE 
XT_FIN 
MODIFY       (   BA_DATE        DATE 'MM/DD/YYYY HH24:MI:SS'  );

but it doesn't like having format string after DATE, it wants ) instead. How do I change only format of this Date column? Thanks

Answer 1

You are trying to change the column definition in the table itself - and a date column doesn't have a format. You are altering an external table and you need to change the access parameters instead:

alter table xt_fin 
access parameters (fields (ba_date date 'MM/DD/YYYY HH24:MI:SS'));

This replaces the whoel access parameters section though, so you really need to re-specify all the other settings too:

alter table xt_fin 
access parameters
(
  records delimited by newline
  badfile     D42:'f.bad'  
  logfile     D42:'f.log'  
  discardfile D42:'f.dsc'  
  skip 1
  fields terminated by ',' optionally enclosed by '"'
  lrtrim  
  missing field values are null
  (
    ba_date date 'MM/DD/YYYY HH24:MI:SS'
  )
);

... including any other columns you omitted from your post.