Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

9.1 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 typesdelimited files, for
example, can be tab-delimited or comma-delimited, and you may be able
to dream up some novel formatbut, 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.