Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Reloading the Data

Now that you know how to extract data from Oracle, you need to know how to load it back in again. This is easy if you have generated a file of SQL statements; you execute that file from within SQL*Plus. Loading data is a little tougher, however, if you have a file of comma-delimited or fixed-width data.

One way to load data into Oracle from a flat file is to use a tool called SQL*Loader, which is a generic utility provided by Oracle for the express purpose of loading data into the database from a file. Another approach is to use the new, external table feature introduced in Oracle9i Database. SQL*Loader is convenient for loading from a relatively small file residing on your client PC, and it's a utility you can use without DBA support, which is a consideration if you don't happen to be the DBA.

An entire book could be written about SQL*Loader and external tables, so it's not possible to cover those topics exhaustively in the remainder of this chapter. What I can do is show you how to use the two methods to reload the employee table from a comma-delimited or fixed-width text file, the same files you learned how to create in this chapter. That should be enough to get you started.

Executing DDL and DML

If you extract data by using SQL*Plus to create a file of INSERT statements, loading the data somewhere else is as simple as creating the necessary table and executing the file. If you created a file of DDL statements, such as the CREATE PUBLIC SYNONYM commands shown ...