Overview
This topic provides examples of how to load CSV files from external tables under various conditions. Some of the examples build on previous examples.
Technologies and Tools Used
The following technologies have been used to achieve this
- Oracle SQL, PL/SQL
Use Case
· The order of the columns in the table must match the order of fields in the data file.
· The records in the data file must be terminated by a newline.
· The field in the records in the data file must be separated by commas (if field values are enclosed in quotation marks, then the quotation marks are not removed from the field).
· There cannot be any newline characters in the middle of a field.
· There cannot be any newline characters in the middle of a field.
Steps with Screenshot
| The data for the external table is as follows: |
Copyevents_all.csv
Winter Games,10-JAN-2010,10,
Hockey Tournament,18-MAR-2009,3,
Baseball Expo,28-APR-2009,2,
International Football Meeting,2-MAY-2009,14,
Track and Field Finale,12-MAY-2010,3,
Mid-summer Swim Meet,5-JUL-2010,4,
Rugby Kickoff,28-SEP-2009,6,
The definition of the external table is as follows:
SQL> CREATE TABLE EVENTS_XT_1
(EVENT varchar2 (30),
START_DATE date,
LENGTH number)
ORGANIZATION EXTERNAL
(default directory def_dir1 location (‘events_all.csv’));
Table created.
The following shows a SELECT operation on the external table EVENTS_XT_1:
SQL> select START_DATE, EVENT, LENGTH
from EVENTS_XT_1
order by START_DATE;
START_DAT EVENT LENGTH
——— —————————— ———-
18-MAR-09 Hockey Tournament 3
28-APR-09 Baseball Expo 2
02-MAY-09 International Football Meeting 14
28-SEP-09 Rugby Kickoff 6
10-JAN-10 Winter Games 10
12-MAY-10 Track and Field Finale 3
05-JUL-10 Mid-summer Swim Meet 4
7 rows selected.
SQL>
