Oracle External Table Import time Date type data

1. Create an external Table directory

Create directory Extdir as ' E:appextdir ';

2. Define External Tables

CREATE TABLE Ext_test

(

RT date,

Rtype char (1)

)

Organization External (

Type Oracle_loader

Default directory Extdir

Access Parameters (

Fields terminated by ', '---field spacer is comma

Missing field values are NULL---outer file line terminator null

(

RT Date "Yyyy-mm-dd HH24:MI:SS",---Defining the time field format type

Rtype char (1)---Define other fields (comma-delimited between multiple fields), with the length defined with the external table

))

Location (' test_ext.txt '));

3. Execute Query statement

Sql> select * from Ext_test;

RT R

-------------- -

January-September -12 D

February-October -12 D

1 September-October -12 D

2 March-November -12 D

20月-November -12 D

November-January -12 D

December-June -12 D

2 May-August -12 D

July-May -12 D

August-April -12 D

10 rows have been selected.

Sql>

Note:

External file Example:

Test_ext.txt File Contents:

2012-09-01,d

2012-10-02,d

2012-10-19,d

2012-11-23,d

2012-11-20,d

2012-01-11,d

2012-06-12,d

2012-08-25,d

2012-05-07,d

2012-04-08,d