External tables

External table is an oracle database feature that enables us to select and load CSV files into database through SQL. Besides, it enables us to create platform-independent dump files.

Below are the steps for accessing CSV files using an external table:

1. Create a database directory object that points to the location of the CSV file.

   1 2 3 4 5 6 7 8

   D:\>cd app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin  D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog  SQL*Plus: Release 12.2.0.1.0 Production on Sat Mar 12 09:59:37 2022  Copyright (c) 1982, 2016, Oracle.  All rights reserved.  SQL> conn sys/orcl@orcl as sysdba Connected. SQL> create directory csv_dir as 'C:\External_file';  Directory created.

2. Grant read and write privileges on the directory object to the user creating the external table.

   1 2 3

   SQL> grant read,write on directory csv_dir to mereba;  Grant succeeded.  SQL>

3. Run the CREATE TABLE…ORGANIZATION EXTERNAL statement.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

   QL> create table exadata_et(   2  exa_id NUMBER   3  ,machine_count NUMBER   4  ,hide_flag NUMBER   5  ,oracle NUMBER   6  ,ship_date DATE   7  ,rack_type VARCHAR2(32)   8  )   9  organization external (  10  type oracle_loader  11  default directory csv_dir  12  access parameters  13  (  14  records delimited by newline  15  fields terminated by '|'  16  missing field values are null  17  (exa_id  18  ,machine_count  19  ,hide_flag  20  ,oracle  21  ,ship_date char date_format date mask "mm/dd/yyyy"  22  ,rack_type)  23  )  24  location ('ex.csv')  25  )  26  reject limit unlimited;  Table created.  SQL>  SQL> select * from exadata_et;      EXA_ID MACHINE_COUNT  HIDE_FLAG     ORACLE SHIP_DATE ---------- ------------- ---------- ---------- --------- RACK_TYPE --------------------------------          5             2          0          0 04-DEC-11 Half           6             1          0          1 06-SEP-12 Quarter           7             4          0          1 10-AUG-12 Full       EXA_ID MACHINE_COUNT  HIDE_FLAG     ORACLE SHIP_DATE ---------- ------------- ---------- ---------- --------- RACK_TYPE --------------------------------          8             1          1          0 15-JUN-12 Quarter   SQL>

4. Use SQL*Plus to access the contents of the CSV file.