Thursday, September 29, 2011

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:-)

8 comments:

Anonymous said...

Hi Vishu,

Thanks for this fantastic post. It's very helpful. However, just a doubt:
Under the heading "Unloading data into an external file using DATAPUMP", which database table is being used to fill data into 'empl_into_rpt.dmp'file?
I'm under impression that the unload of data occurs when the external table is created using the "AS" clause. Like;
AS SELECT * FROM emp;

Please help me here.

Thanks,
Alok

Anonymous said...

What's up, yeah this piece of writing is truly nice and I have learned lot
of things from it concerning blogging. thanks.



Review my page - Pest Inspection Cost Guelph ON

Anonymous said...

I am curious to find out what blog platform you're working
with? I'm experiencing some minor security issues
with my latest blog and I'd like to find something more secure.
Do you have any suggestions?

Review my weblog; ways to make money online from home Phoenix

dalia alaa said...

Lucky me I came across your website by chance
http://www.kuwait.prokr.net/

raybanoutlet001 said...

michael kors bags
oakley sunglasses
ugg boots
cheap jordan shoes
ray ban sunglasses outlet
nike tn
ugg boots
nike blazer
ray ban sunglasses
rolex watches

jeje said...

kyrie irving shoes
jordan 13
adidas outlet
air jordan 11
adidas nmd
jordan retro
longchamp handbags
lacoste online shop
birkin bag
adidas eqt support adv

5689 said...

zzzzz2018.8.31
off-white clothing
nike air max 95 ultra
reebok
ugg boots
giuseppe zanotti
nike huarache
football pas cher
basket nike
fitflops sale clearance
coach outlet store online

Unknown said...

birkin bag
kd 11
yeezy boost
pure boost
fila
michael kors handbags
reebok shoes
off white hoodie
fitflops sale clearance
yeezy boost 350 v2