SQL*Plus can be used to extract data from Oracle for use in a spreadsheet or some other application. The need to do this is so common that it’s a wonder Oracle doesn’t supply an application specifically for that purpose. Unfortunately, they don’t. Oracle does provide SQL*Loader, a utility that can load data into Oracle from almost any form of flat file, but there is no corresponding SQL*Unloader.
Oracle does, however, provide SQL*Plus. Even though it’s not a generic data extraction utility, through the creative use of SQL and SQL*Plus’s formatting options, you can extract numeric, date, and text data to a flat file. Depending on your needs, you can format the file as a comma-delimited file, a tab-delimited file, or you can format the data in fixed-width columns. Comma-delimited files are most useful if you are transferring data to a spreadsheet such as Lotus 1-2-3, or a desktop database such Microsoft Access. Fixed-width, columnar data files are often used to transfer data to legacy applications.
In addition to simply extracting data, you can get more creative and use SQL*Plus to generate a script file containing SQL statements. This is referred to as “using SQL to write SQL.” You can do something as simple as generating a flat file of INSERT statements to be used in recreating the data at another site, or you can generate a file of Data Definition Language (DDL) statements to modify your own database. I’ve even seen people use SQL*Plus to generate operating-system shell scripts to use in modifying and maintaining their database.
In this chapter, I will walk you through the process of writing a script to extract data from the sample database into a flat file. You will see how SQL can be written to produce a comma-delimited text file, a fixed-width text file, or a file of INSERT statements. Once this is done, you will see how that same data can be loaded back into Oracle.
Generally speaking, there are four types of output files you can produce when extracting data with SQL*Plus:
Delimited columns
Fixed-width columns
DML (Data Manipulation Language)
DDL (Data Definition Language)
There may be variations on these types — delimited files, for example, could be either tab-delimited or comma-delimited, and you may be able to dream up some novel format, but, generally speaking, these are the most useful.
Delimited files use a special text character to separate each data value in a record. Typically the delimiter is either 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):
1,135,"15-Nov-1961","Jonathan Gennick" 2,95,"10-Jan-1991","Bohdan Khmelnytsky" 3,200,"17-Jul-1984","Ivan Mazepa"
This example illustrates a very commonly used format called the CSV (Comma Separated Values) 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, and is the format for the examples in this chapter.
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:
00113515-Nov-1961Jonathan Gennick 00209510-Jan-1991Bohdan Khmelnytsky 00320017-Jul-1984Ivan Mazepa
In the above example, the columns abut each other with no space in between. If you aren’t trying to match an existing file layout, you may prefer to allow at least one space between columns to aid readability.
A DML file contains Data Manipulation Language 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. Here, for example, is a file of INSERT statements that recreate some employee data:
INSERT INTO employee
(employee_ID,employee_billing_rate,employee_hire_date,employee_name)
VALUES (1,135,TO_DATE('15-Nov-1961','DD-MON-YYYY'),'Jonathan Gennick'),
INSERT INTO employee
(employee_ID,employee_billing_rate,employee_hire_date,employee_name)
VALUES (2,95,TO_DATE('10-Jan-1991','DD-MON-YYYY'),'Bohdan Khmelnytsky'),
INSERT INTO employee
(employee_ID,employee_billing_rate,employee_hire_date,employee_name)
VALUES (3,200,TO_DATE('17-Jul-1984','DD-MON-YYYY'),'Ivan Mazepa'),You can generate these INSERT statements, based on existing data, using SQL*Plus and SQL. Then you can easily apply those inserts to another database. This may not seem to be the most “efficient” way of moving data around, but if you have a low volume of data, such as a few dozen records that you want to send off to a client, it works very well.
A DDL file contains Data Definition Language 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. Say, for example, that you need to create public synonyms for all your tables. You could use a 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 7, explores this subject in greater depth.