External tables with Datapump

External tables are largely used as a convenient way of moving data into and out of the database.They let you query data in a flat file as though the file were an Oracle table.

Oracle uses SQL*Loader functionality, through the ORACLE_LOADER access driver to move data from the flat file into the database; it uses a Data Pump access driver to move data out of the db into a file in an Oracle-proprietary format, and back into the database from files of that format

Real-Life use of External Tables:

Suppose that you receive a daily .csv file from a reporting department. Instead of writing a SQL*Loader script to import each day's .csv file into your database, you can simply create an external table and write an "insert ... select" SQL query to insert the data directly into your tables. Place the day's CSV file in the location specified in the external table definition, run the query, and you're done.

Lets see how its done:

Choose or create a OS directory where you will put your .CSV(..flat file).

In my case i have taken : 'D:\et\'

My CSV file will look like this having name "emp.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

The next step is to create this directories in Oracle, and grant read/write access on it to the Oracle user who will be creating the external table

SQL> create directory opump as 'D:\et\';

Directory created.

SQL> grant read,write on directory opump to gg;

Grant succeeded.

Now,create the external table correspond to .CSV file.

SQL> conn gg/gg

Connected.

SQL>  create table xtern_empl_rpt

  2          ( empl_id varchar2(3),

  3            last_name varchar2(50),

  4            first_name varchar2(50),

  5            ssn varchar2(9),

  6           email_addr varchar2(100),

  7            years_of_service number(2,0)

  8          )

  9          organization external

 10         ( default directory opump

 11           access parameters

 12           ( records delimited by newline

 13             fields terminated by ','

 14           )

 15           location ('emp.csv')

 16       );

Table created.

External table is successfully created now.Lets check the data in oracle!

SQL> sho user;

USER is "GG"

SQL> select * from xtern_empl_rpt ;

Note: If u add more records in the csv file i.e. emp.csv then oracle table "xtern_empl_rpt" will automatically updated.

For example i will add a new record in my csv file:

7,Vishu,DBA,123,itsmevishu82@gmail.com,25

Save the file..

Lets see the record if updated in oracle table.

Unloading data into an external file using DATAPUMP.

Oracle 10g lets you create a new external table from data in your database, which goes into a flat file pushed from the database using the ORACLE_DATAPUMP access driver. This flat file is in an Oracle-proprietary format that can be read by DataPump. The syntax is similar to the CREATE TABLE... ORGANIZATION EXTERNAL above, but simpler -- since you can't specify the data format, you can specify very few access_parameters. The key difference is that you must specify the access driver, ORACLE_DATAPUMP, since the access driver defaults to ORACLE_LOADER.

Lets see how its done..

First create a user called "mm" and provide the privileges as below.

SQL> create user mm identified by mm;

User created.

SQL> grant read,write on directory opump to mm;

Grant succeeded.

Now,connect to mm and create the dumpfile using external table.

SQL> conn mm/mm

Connected.

SQL> create table import_empl_info

  2      ( empl_id varchar2(3),

  3        last_name varchar2(50),

  4        first_name varchar2(50),

  5        ssn varchar2(9),

  6        birth_dt date

  7      )

  8      organization external

  9      ( type oracle_datapump

 10       default directory opump

 11       location ('empl_info_rpt.dmp')

 12     ) ;

Table created.

It will create the dumpfile called "empl_info_rpt.dmp" which can be moved and used in any other database or same.

IMPORT THE TABLE BACK IN AGAIN..

We will now again load the data into oracle from the dump being created through external table.

First drop the table "import_empl_info".

SQL> drop table import_empl_info;

Table dropped.

Now load the data again ..

SQL> create table import_empl_info

  2      ( empl_id varchar2(3),

  3        last_name varchar2(50),

  4        first_name varchar2(50),

  5        ssn varchar2(9)

  6        )

  7      organization external

  8      ( type oracle_datapump

  9       default directory opump

 10       location ('empl_info_rpt.dmp')

 11     ) ;

We have seen now how External tables in 9i and 10g provide a convenient, seamless way to move data in and out of the database

Restrictions imposed on External tables:

1.)External are not usable in many ways regular Oracle tables.

2.)You cannot perform any DML operations on external tables other than table creation.

3.)You can't create an index on an external table. 

Enjoy:-)