Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




Using Oracle External Tables for Data transfer

Arup Nanda - OTN

Oracle9i Database introduced external tables, which allow a formatted plain text file to be visible to the database as a table that can be selected by regular SQL. Suppose you have to move the contents of the table named TRANS from the OLTP database to the data warehouse database using this external table approach. Here are the steps to accomplish that.
  1. From the OLTP database, create a plain text file with the contents of the table TRANS. The file can be called trans_flat.txt in the directory /home/oracle/dump_dir. Usually this file is created with this SQL:
    spool trans_flat.txt
    select <column_1> ||','|| <column_2> ||','|| ...
    from trans;
    spool off
  2. Copy the file over to the data warehouse server using ftp, rcp, or some other mechanism. The file exists in the directory /home/oracle/dump_dir.
  3. On the data warehouse database, create a directory object named dump_dir as:
    create directory dump_dir as '/home/oracle/dump_dir';
  4. Create an external table:
    create table trans_ext
       ... <columns of the table> ...
    organization external
       type oracle_loader
       default directory admin
       access parameters
          records delimited by newline
          badfile 'trans_ext.bad'
          discardfile 'trans_ext.dis'
          logfile 'trans_ext.log'
          fields terminated by ","  optionally enclosed by '"'
              ... <columns> ...
       location ('trans_flat.txt')
    reject limit unlimited;
  5. Now load the external table into the regular tables using any common method such as direct load insert and merge.
The most time-consuming step here is Step 1, in which the plain text file is created. You could create this file using plain SQL and spooling to a file—a simple yet lengthy process. You can make the process somewhat faster by using a Pro*C or OCI program instead of SQL*Plus to offload the records to a flat file, but it will still take a while. The other "speed bump" is the need to specify the columns manually—another time-consuming process.



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks