Types of Output Files

Generally speaking, you can produce four types of output files when extracting data with SQL*Plus:

• Delimited columns
• Fixed-width columns
• Data manipulation language (DML)
• Data definition language (DDL)

There may be variations on these types ”delimited files, for example, can be tab-delimited or comma-delimited, and you may be able to dream up some novel format ”but, generally speaking, these are the most useful.

9.1.1 Delimited Files

Delimited files use a special text character to separate each data value in a record. Typically, the delimiter is a tab or a comma, but any character may be used. Here's an example of a comma-delimited file containing employee information (ID, rate, hire date, and name ):

101,169,"15-NOV-1961","Marusia Churai" 105,121,"15-JUN-2004","Mykola Leontovych" 107,45,"02-JAN-2004","Lesia Ukrainka"

This example illustrates a commonly used format called the Comma Separated Values (CSV) format. CSV-formatted files use commas to delimit the values, and they enclose text fields within quotes. The CSV format is recognized by most spreadsheets and desktop databases.

9.1.2 Fixed-Width Files

A fixed-width file contains data in columns, where each column is a certain width, and all values in that column are the same width. Here's an example of the same employee data shown earlier, but formatted into fixed-width columns:

10116915-NOV-1961Marusia Churai 10512115-JUN-2004Mykola Leontovych 10704502-JAN-2004Lesia Ukrainka

In this example, the columns abut each other with no space in between. If you don't want to match an existing file layout, you may prefer to allow at least one space between columns to aid readability.

9.1.3 DML Files

A DML file contains DML statements, such as INSERT, DELETE, UPDATE, and SELECT. This type of file can be used as a quick and dirty way of extracting data from one database for insertion into another. If you want to transfer data for the three employees from the preceding examples, your DML file would contain the following INSERTs :

INSERT INTO employee (employee_ID,employee_billing_rate,employee_hire_date,employee_name) VALUES (101,169,TO_DATE('15-Nov-1961','DD-MON-YYYY'),'Marusia Churai'); INSERT INTO employee (employee_ID,employee_billing_rate,employee_hire_date,employee_name) VALUES (105,121,TO_DATE('15-Jun-2004','DD-MON-YYYY'),'Mykola Leontovych'); INSERT INTO employee (employee_ID,employee_billing_rate,employee_hire_date,employee_name) VALUES (107,45,TO_DATE('02-Jan-2004','DD-MON-YYYY'),'Lesia Ukrainka');

You can generate these INSERT statements, based on existing data, using SQL*Plus and SQL. Then you can apply those inserts to another database. This may not seem to be the most efficient way of moving data around, but if you have low data volume, such as a few dozen records that you want to send off to a client, it works well.

9.1.4 DDL Files

A DDL file contains DDL statements. It's not much different from a DML file, except that the goal is to modify your database rather than to extract data for another application. Suppose, for example, that you need to create public synonyms for all your tables. You can use an SQL query to generate the needed CREATE PUBLIC SYNONYM statements, spool those to a file, and then execute that file. You will find a brief example showing how to do this later in this chapter. Chapter 10 explores this subject in greater depth.