Using Oracle External Tables for Data transfer

Phone
800-766-1884

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.

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
```
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.
On the data warehouse database, create a directory object named dump_dir as:
```
create directory dump_dir as '/home/oracle/dump_dir';
```

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;

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.