External tables in oracle

---Create directory to store data file.

SQL> connect sys as sysdba;
Enter password:
Connected.
SQL> create or replace directory xtern_data_dir as 'C:\Users\anandy\Scripts\Data
';

Directory created.

SQL> grant read,write on directory xtern_data_dir to scott;

Grant succeeded.

SQL> connect scott;
Enter password:
Connected.


--data file-employee_report.csv

001,Hutt,Jabba,896743856,jabba@thecompany.com,18
002,Simpson,Homer,382947382,homer@thecompany.com,20
003,Kent,Clark,082736194,superman@thecompany.com,5
004,Kid,Billy,928743627,billythkid@thecompany.com,9
005,Stranger,Perfect,389209831,nobody@thecompany.com,23,
006,Zoidberg,Dr,094510283,crustacean@thecompany.com,1

---Create external table for data load.

drop table xtern_empl_rpt;

create table xtern_empl_rpt
( empl_id varchar2(3),
last_name varchar2(50),
first_name varchar2(50),
ssn varchar2(9),
email_addr varchar2(100),
years_of_service number(2))
organization external
(TYPE oracle_loader
default directory xtern_data_dir
access parameters
( records delimited by newline
LOAD WHEN ((1:1) != "#")
fields terminated by ','
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS) location ('employee_report.csv'))
REJECT LIMIT UNLIMITED;

---Load data or create another table with data

create table empl_rpt as
select * from xtern_empl_rpt;


---Create external table for data export.

create table empl_rpt as select * from xtern_empl_rpt;

create table export_empl_info
organization external
( type oracle_datapump
default directory xtern_data_dir
location ('empl_info_rpt.dmp'))
as select * from empl_rpt;