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.

PC Gaming Headset Headphone Hook Holder Hanger Mount, Headphones Stand with Adjustable & Rotating Arm Clamp, Under Desk Design, Universal Fit, Built in Cable Clip Organizer EURPMASK

(27325)

$12.99 (as of 30/06/2024 15:53 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

🎧【Clamp-on Headphone Stand】The E-Hold Series headphone holder fits desks from 0.31 inch to 1.57 inch thick, making it a versatile choice for most setups. The spring clamp is a breeze to remove and reattach. No glue, no mess. It blends seamlessly into... read more

LLEVTIC Computer Speakers, Desktop Speakers with 6 Colorful RGB Lights, Volume Control PC Speakers, USB Powered Gaming Speakers with 3.5mm Aux Cable for PC Monitor Laptop Tablet Phone

(1250)

44% Off $26.88 $14.99 (as of 30/06/2024 15:56 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

【Full-Range Stereo Sound Quality】Equipped with advanced sound drivers and a full-range 2.0 channel reinforced stereo core, this computer speakers provides the perfect atmosphere for your entertainment activities such as watching movies, gaming, and l... read more

Laptop Cleaning Kit Keyboard Cleaner, Keyboard Cleaning Kit Electronics Cleaning Tool for MacBook iPad Phone iPhone Pro, Brush Tool for Tablet, Computer, PC, TV Camera Lens Computer Vacuum Keyboard

(2045)

$16.98 (as of 30/06/2024 15:53 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

DEEP CLEAN YOUR DEVICE The electronics cleaner kit can effectively deep clean all your electronic devices such as mobile phones, tablets, TVs, etc. It comes with retractable large keyboard brush, polishing cleaning cloth X 2, keycap puller, metal pen... read more

UtechSmart Venus Pro RGB Wireless MMO Gaming Mouse, 16,000 DPI Optical Sensor, 2.4 GHz Transmission Technology, Ergonomic Design, 16M Chroma RGB Lighting, 16 programmable Buttons, Up to 70 Hours

20% Off $59.99 $47.99 (as of 30/06/2024 15:53 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

【Extensive Fully Programmable Buttons】16 customizable buttons can improve your gaming skills and performance by complex custom button macro coding of reconfiguration and assignment. The button custom function combines multiple keys into one button th... read more