Home » SQL & PL/SQL » SQL & PL/SQL » create table with non default date format
create table with non default date format [message #301904] Fri, 22 February 2008 04:05 Go to next message
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 #301905 is a reply to message #301904] Fri, 22 February 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The idea is to avoid date conversion due to a large number of rows loaded every time

As input data are strings and column type is date there is always a conversion.

Specify your date field format/mask in your table definition as in Database Utilities, Part II SQL*Loader, Chapter 9 Field List Reference, Section SQL*Loader Datatypes, paragrah DATE.

Regards
Michel
Re: create table with non default date format [message #301907 is a reply to message #301904] Fri, 22 February 2008 04:16 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
may be one way would be to declare the EVENT_DATE as varchar2 and then use TO_DATE for further processing.just a thought,there might be some workaround for your problem


regards,
Re: create table with non default date format [message #301955 is a reply to message #301905] Fri, 22 February 2008 08:18 Go to previous messageGo to next message
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

Re: create table with non default date format [message #301957 is a reply to message #301955] Fri, 22 February 2008 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You did it in the wrong place.
It should be done in the column list of ACCESS PARAMETERS clause (list you currently have not).

It EXACTLY the same thing doing TO_DATE in SQL or using date conversion from SQL*Loader or external table. Oracle calls the same function.
As I said, as input is string and column field is date conversion is mandatory.

Regards
Michel

Re: create table with non default date format [message #301960 is a reply to message #301955] Fri, 22 February 2008 08:41 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
But there is a slight advantage of using to_Date conversion while loading the data because you (in the sense oracle) can verify the integrity of the data while loading it rather than getting your query to fail while doing any select. So I would like to store the date column as date datatype and varchar2 column as varchar2 ideally.

Now coming to the point date format in external tables it is slightly different compared to sql*loader as @michael already pointed out it should come up in the access parameters and the usage is slightly different.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm#sthref1647

Regards

Raj
Previous Topic: Re-write SQL in Single Query? (merged)
Next Topic: Eliminate Duplicate subqueries in MERGE INTO
Goto Forum:
  


Current Time: Wed Dec 07 12:50:58 CST 2016

Total time taken to generate the page: 0.11860 seconds