Oracle External Table

External tables are created using the sql create table.organization table statement.

 To load the bulk data using sql loader

Step 1:To create a file in os level

oracle@db ~]$ mkdir d1
oracle@db ~]$ cd d1
oracle@db d1]$ vi f1
oracle@db ~]$ export ORACLE_SID=<databasename>
oracle@db ~]$ sqlplus / as sysdba

First we connect the ‘SYS’ user then we can create a new directory

Syntax:
 Sql> conn sqlplus / as sysdba
 You must have any directory system privilege to create directories.

Syntax:
 Sql> grant create any directory to username;
Example:
 Sql> grant create any directory to sai;

Syntax:
 Sql> create directory <directory_name> as  <path_name>;
Example:
 Sql> create directory d1 as  ‘/home/oracle/db/’;
Syntax:
 Sql> grant read,write on directory directoryname to username;
Example:
 Sql> grant read,write on directory d1 to nijam;

Create table:

 Organization external table:using type oracle loader

 Default directory:

 Access parameters( :

 Records delimited by newline

 Fields terminated by

 Location :specifies the location of the external date,the location is specified as a list of directory objects and filenames.if the directory objects is not specified,then the default directory objects is used as the file location.

Example program:

 Create table filename(study) (id number(5),name varchar2(15))
 Organization external (type oracle_loader
 Default directory directoryname
 Access parameters(
 Records delimited by newline
 Fields terminated by ‘ , ‘)
 Location (‘filename’));

SQL > select * from tablename;