Oracle SQL*Plus: The Definitive Guide

Extracting the Data

To write a script to extract data from Oracle and place it in a flat file, follow these steps:

1. Formulate the query.

2. Format the data.

3. Spool the extract to a file.

4. Make the script user-friendly.

The last step, making the script user-friendly, isn’t really necessary for a one-off effort. However, if it’s an extraction you are going to perform often, it’s worth taking a bit of time to make it easy and convenient to use.

Formulate the Query

The very first step in extracting data is to figure out just what data you need to extract. You need to develop a SQL query that will return the data you need. For the example in this chapter, we will just extract the employee data, so the query will look like this:

SELECT employee_id, 
       employee_name, 
       employee_hire_date, 
       employee_termination_date, 
       employee_billing_rate
  FROM employee;

Keep in mind that you can write queries that are much more complicated than those shown here. If necessary, you can join several tables together, or you can UNION several queries together.

Format the Data

The next step, once you have your query worked out, is to format the data to be extracted. The way to do this is to modify your query so it returns a single, long expression that combines the columns together in the format that you want in your output file. It’s often necessary to include text literals in the SELECT statement as part of this calculation. For example, if you want to produce a comma-delimited file, you will need to include those commas ...