Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

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

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

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata