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 ',' --- the field delimiter is comma

Missing field values are null --- the end of the external file line is null

(

Rt date "YYYY-MM-DD HH24: MI: SS", --- define the time field format type

Rtype char (1) --- define other fields (multiple fields are separated by commas). The length is the same as that defined in the External table.

))

Location('test_ext.txt '));

3. Execute the query statement

SQL> select * from ext_test;

RT R

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

Month 1-9-12 D

02-10-12 D

September 10-12 D

23-11-12 D

20-11 months-12 D

11-1-12 D

12-6 months-12 D

25-8-12 D

7-5-12 D

08-4-12 D

10 rows have been selected.

SQL>

Note:

External file example:

Content of the test_ext.txt file:

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