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.
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 ...