To write a script to extract data from Oracle and place it in a flat file, follow these steps:
Formulate the query.
Format the data.
Spool the extract to a file.
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.
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.
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.
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 ...