Oracle SQL*Plus: The Definitive Guide, 2nd Edition

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 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 first step in extracting data is to figure out what data you need to extract. You need to develop a SQL query that will return the data you need. To extract data for current employees, you could use a query such as in Example 9-1.

Example 9-1. A query to extract current employee data

SELECT employee_id, 
       employee_billing_rate
       employee_hire_date, 
       employee_name
FROM employee
WHERE employee_termination_date IS NULL;

You can write queries that are more complicated than shown here. If necessary, you can join several tables together, or you can UNION several SELECT statements together.

Format the Data

The next step, once you have your query worked out, is to format the data to be extracted. The best way I've found 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 expression. For example, if you want to produce a comma-delimited file, you will need to include ...