Importing and Exporting Data with Oracle Database XE
(Page 1 of 3 )
You can't get the most out of a database until you can import data into it and export data out of it. This article will show you several ways to perform these tasks on your Oracle XE database. It is excerpted from chapter 39 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Rarely is your database self-contained. You may have to create a spreadsheet for the accounting department so it can merge data in the database with its existing spreadsheets, or you may need to import a text file generated from a reporting or tracking tool into one of your database tables. Therefore, you need to be fluent in the use of Oracle Database XE’s import and export capabilities.
In this chapter, we show you a couple of ways to export data from your database tables to an external destination using the SQL*PlusSPOOLcommand, and of course the similar options available in the GUI. On the flip side, we show you how to use the Oracle Database XE GUI tools to import data from a text file or a spreadsheet.
Exporting Data
Most likely, the departments at your company use a variety of tools to manage their data, such as Excel for spreadsheets or a custom Java application that uses text files for input or output. Invariably, they need data from your database. You have a number of tools available to satisfy these requests, ranging from the very basic SPOOL command in SQL*Plus to the convenience of the export options available in the Oracle Database XE GUI.
Using the SPOOL Command
If you have ever used the command-line SQL*Plus utility, you may have wondered how to capture the output from the SQL commands you type, short of using a GUI-based cut-and-paste utility. The SPOOLcommand simplifies this process.
In our example, the IT department employees are overworked, so the employee relations department is giving each IT department employee free movie tickets. Therefore, you must capture employee information for employees in theIT_PROGdepartment and send it to the employee relations department in a format suitable for import into Microsoft Excel so the employee relations department can track the movie ticket expenses. First, connect to Oracle Database XE as theHRuser as follows:
sqlplus hr/hr
-------------------------------------------- SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 18 20:59:09 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> --------------------------------------------
Typically when you use SQL*Plus for ad hoc queries, you want to see column headers. In this case, you do not need column headers or a row count summary to import into Excel, so you use theSETcommand to turn these off:
set heading off set feedback off
To see allSEToptions within SQL*Plus, just type HELP SET. Finally, you want to capture the output to a file, so you use theSPOOLcommand to specify the destination location for the output file:
spool /tmp/it_empl.csv
Next, you run the query as follows, inserting commas between fields to make the file suitable for importing into Excel as a CSV formatted file:
select employee_id || ',' || last_name || ',' || first_name || ',' || email from employees where job_id like 'IT_%';
Finally, turn off theSPOOLcommand as follows:
SPOOL OFF
Note the||operator in theSELECTstatement; it is the concatenation operator in an Oracle expression. The||operator combines the variables on each side of the operator into a single string value. If the variables on either side of the operator are not aVARCHAR2or aCHARvariable (such asNUMBERorDATE), the variables are converted to aVARCHAR2 value before concatenating them.
SQL> spool off --------------------------------------------
The only other required step before you send the file to the employee relations department is to trim out the blank lines and the line that has theSPOOL OFFcommand.
