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.

   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.

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

3. Run the CREATE TABLE…ORGANIZATION EXTERNAL statement.

   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.