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


 VALUES (101,169,TO_DATE('15-Nov-1961','DD-MON-YYYY'),'Marusia Churai');

INSERT INTO employee


 VALUES (105,121,TO_DATE('15-Jun-2004','DD-MON-YYYY'),'Mykola Leontovych');

INSERT INTO employee


 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.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net