Step 1) As a
SYS user create a directory EXT and grant read, write permission to user
Ashwani.
SQL> conn / as sysdba
SQL> create directory ext as 'G:\pump';
SQL> grant read,write on directory
ext to ashwani;
Step 2) Connect
to user Ashwani and create table EMP
SQL> conn ashwani/ashwani
SQL>
CREATE TABLE EMP(
emp_id INT,
emp_name VARCHAR2(30)
)
ORGANIZATION EXTERNAL(
TYPE oracle_loader
DEFAULT DIRECTORY ext
ACCESS PARAMETERS
(FIELDS TERMINATED BY ',')
LOCATION ('mytable.csv')
);
Step 3)
Create CSV file 'mytable.csv'
at location 'G:\pump' and insert few records.
G:\pump>dir mytable.csv
Volume in drive G is New Volume
Volume Serial Number is 867E-CA03
Directory of G:\pump
27-Nov-19 03:05 PM 12 mytable.csv
1 File(s) 12 bytes
0 Dir(s) 26,743,087,104 bytes free
SQL> select * from ashwani.emp;
NOTE:
You cannot apply the INSERT,DELETE
and UPDATE to external table.
