create table with non default date format [message #301904] |
Fri, 22 February 2008 04:05  |
alina_
Messages: 14 Registered: February 2008
|
Junior Member |
|
|
Hi,
I need help with creating an external table which contains a column with date format 'YYYYMMDDHH24MISS'.
CREATE TABLE MYTABLE_EXT
(
EVENT_ID VARCHAR2(5 BYTE),
EVENT_DATE DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY MYDIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';'
MISSING FIELD VALUES ARE NULL
)
LOCATION (MYDIR:'mydata.dat')
)
REJECT LIMIT UNLIMITED
PARALLEL ( DEGREE 4 INSTANCES DEFAULT )
NOMONITORING;
}
As far as I know, by default the column event_date will have a format as defined by NLS_DATE_FORMAT.
I tried the following before creating the table :
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDHH24MISS';
But I still have the following error during data loading:
error processing column EVENT_DATE in row 11519 for datafile mydata.dat
ORA-01861: literal does not match format string
Data in input file:
78;20080218234941
80;20080218234941
The idea is to avoid date conversion due to a large number of rows loaded every time.
Thanks in advance ...
|
|
|
|
|
Re: create table with non default date format [message #301955 is a reply to message #301905] |
Fri, 22 February 2008 08:18   |
alina_
Messages: 14 Registered: February 2008
|
Junior Member |
|
|
Dear Michel,
I specified the mask according to your reference but it still fails with the following error:
CREATE TABLE MYTABLE_EXT
(
EVENT_ID VARCHAR2(5 BYTE),
EVENT_DATE DATE POSITION(1:14) "YYYYMMDDHH24MISS"
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY SAPELAB
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';'
MISSING FIELD VALUES ARE NULL
)
LOCATION (SAPELAB:'mydata.dat')
)
REJECT LIMIT UNLIMITED
PARALLEL ( DEGREE 4 INSTANCES DEFAULT )
NOMONITORING;
It highlights the parenthesis in red.
Dear dhananjay,,
Using TO_DATE conversion is what I would like to avoid, because as it says in ORACLE documentation the date conversion slows down the loading performance.
[Updated on: Fri, 22 February 2008 08:19] Report message to a moderator
|
|
|
|
|