Date format inside csv has to be in MM/DD/YYYY format but External table time format DD-MON-YYYY (Doc ID 2880754.1)

Last updated on APRIL 17, 2023

Applies to:

Goal

Sometimes we face the issue while loading the CSV file data(comma separated) into the oracle database table using the EXTERNAL TABLE concept.
As per our requirement, the date format inside csv has to be in mm/dd/yyyy format only. Kindly help us achieve this.
Attaching the sample csv file and code which we are using.
External table time format -- DD-MON-YYYY

we are using CSV file and it is in following format:

$cat FetcRelCtl.csv
30-JUN-2022,102,DBNAME,SCHEMANAME,OBJECTTYPE,OBJECTCOUNT

$ cat FetchRelCtl.csv
06/30/2022,102,DBNAME,SCHEMANAME,TABLE,2526,32951,0

SQL> set lines 300 pages 300
SQL> select * from NLS_DATABASE_PARAMETERS order by parameter;

PARAMETER                        VALUE
------------------------------- ----------------------------------------------------------------
NLS_CALENDAR                    GREGORIAN
NLS_CHARACTERSET                WE8ISO8859P1
NLS_COMP                        BINARY
NLS_CURRENCY                    $
NLS_DATE_FORMAT                 DD-MON-RR
NLS_DATE_LANGUAGE               AMERICAN
NLS_DUAL_CURRENCY               $
NLS_ISO_CURRENCY                AMERICA
NLS_LANGUAGE                    AMERICAN
NLS_LENGTH_SEMANTICS            BYTE
NLS_NCHAR_CHARACTERSET          AL16UTF16
NLS_NCHAR_CONV_EXCP             FALSE
NLS_NUMERIC_CHARACTERS          .,
NLS_RDBMS_VERSION               19.0.0.0.0
NLS_SORT                        BINARY
NLS_TERRITORY                   AMERICA
NLS_TIMESTAMP_FORMAT            DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT         DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT                 HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT              HH.MI.SSXFF AM TZR

20 rows selected.

create or replace directory Insert_CSV as '/tmp/abc/csv';

create table TestControl
(ReleaseDate Date,ReleaseNumber CHAR(30),DBName CHAR(30),SchemaName CHAR(30),ObjectType CHAR(30),ObjectCount Numeric(5),ColumnCount Numeric(5),PIIColumnCount Numeric(5))
Organization external
(type oracle_loader
default directory Insert_CSV
access parameters (records delimited by newline
fields terminated by ',')
location ('${InputFile}'))
reject limit unlimited;

We observed when the csv file contains data in other formats like DD-MON-YYYY , external table script works fine. 
But the requirement is to create the external table, even if the csv file has the content in mm/dd/yyyy format.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.