Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 39

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. There- fore, 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*Plus SPOOL command, 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 SPOOL command simplifies this process.
In our example, the IT department employees are overworked, so the employee relations depart-
ment is giving each IT department employee free movie tickets. Therefore, you must capture employee information for employees in the IT_PROG department and send it to the employee relations depart- ment 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 the HR user 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>

675

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 the SET command to turn these off:
set heading off set feedback off

To see all SET options within SQL*Plus, just type HELP SET. Finally, you want to capture the output to a file, so you use the SPOOL command 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 the SPOOL command as follows:

SPOOL OFF

Note the || operator in the SELECT statement; it is the concatenation operator in an Oracle expres- sion. 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 a VARCHAR2 or a CHAR variable (such as NUMBER or DATE), the variables are converted to a VARCHAR2 value before concatenating them.
The output file from the query looks like this:

103,Hunold,Alexander,AHUNOLD
104,Ernst,Bruce,BERNST
105,Austin,David,DAUSTIN
106,Pataballa,Valli,VPATABAL
107,Lorentz,Diana,DLORENTZ

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 the SPOOL OFF command.

Exporting Using GUI Utilities
As you might expect, the Oracle Database XE GUI interface can produce the same results as the SPOOL command. From the Oracle Database XE home page, navigate to Utilities ➤ Data Load/Unload ➤ Unload ➤ Unload to Text. You will see the page shown in Figure 39-1.

■Note For external applications or systems that support it, Oracle Database XE can export your tables to XML
format in addition to a flat file text format.

Figure 39-1. The Oracle Database XE administration home page

Select the schema you want to export from. Since you are exporting an HR table and you are logged in as the HR user, the default is appropriate. Click the Next button and select the EMPLOYEES table, as shown in Figure 39-2. Click the Next button.

Figure 39-2. Selecting the table to export to text format (CSV or TXT)

In the dialog shown in Figure 39-3, click each column to export—in this case, EMPLOYEE_ID,
LAST_NAME, FIRST_NAME, and EMAIL.

After you click the Next button, specify other options for the exported file, such as the character that separates each column, whether to enclose each column with another character such as double quotes, and the output file format (DOS or Unix). Be sure to specify the file format corresponding to your browser’s platform. If Oracle Database XE is running on Linux, but your browser is running on Windows, specify DOS as the platform. By checking the Include Column Names box, the first line of the exported file contains the column names corresponding to the exported columns. In the example shown in Figure 39-4, you specify a comma as the separator and the file format as DOS.

Figure 39-4. Specifying the format of the text output

When you click the Unload Data button shown in Figure 39-4 on a Windows platform, Windows prompts you to either open the file with the default application (in this case, Notepad) or to save the file. In Figure 39-5, the Firefox Web browser asks you what to do with the file. Accept the default, open the file directly with Notepad, and click OK.

Figure 39-5. Specifying the format of the text output

Figure 39-6 shows the exported EMPLOYEES data in a Notepad window.

Figure 39-6. Text output of the EMPLOYEES table using the Oracle Database XE GUI

If you are using a browser on Linux, the export procedure is nearly identical until you click the Unload Data button. For the Firefox browser, you see the same prompt as you do on Windows except for the viewer application. As you can see in Figure 39-7, Linux does not have Windows Notepad.

Figure 39-7. Saving the exported text output using Firefox on Linux

The default text editor on this Linux workstation is gedit, and as you can see in Figure 39-8, the exported text file looks identical to the text file you exported from a Windows-based browser.

Figure 39-8. Viewing the exported EMPLOYEES table on Linux

Whether you use the SQL*Plus SPOOL command or the GUI depends on a couple of factors. The SPOOL command is a bit more work because you have to write a query, but you can filter your query as you please. The Oracle Database XE GUI export does not have a filtering option but gives you a few more options such as headers and target platform format (DOS or Unix/Linux).

Importing Data

Your life would be a lot easier if everyone used an Oracle database. Exchanging data would be consider- ably easier using Oracle Database XE’s native export and import commands (using these commands is beyond the scope of this book). The reality is that you will need to import data into your database from a variety of sources, such as text files, spreadsheets, and other database and application formats, such as XML.
In the following example, a legacy application collects anonymous comments about other
employees on a Web page and saves them in a spreadsheet. The spreadsheet contains only the employee number, the date of the comment, and the comment itself. To help management more accurately interpret the comments, you must import this spreadsheet data into the database and

join the new table to the existing EMPLOYEES table to pull the employee name and e-mail address. The spreadsheet that we will import is EmployeeComments.csv and you can see it in Figure 39-9.

Figure 39-9. Employee comments spreadsheet

From the Oracle Database XE home page, navigate to Utilities ➤ Data Load/Unload ➤ Load and you will see the options, shown in Figure 39-10: Load Text Data, Load Spreadsheet Data, and Load XML Data.

Figure 39-10. Data Load import type options

Click Load Spreadsheet Data and you see the options in Figure 39-11. You will load this spread- sheet to a new table and the spreadsheet will be loaded from an external file instead of using the operating system’s cut-and-paste function.

After you click the Next button, shown in Figure 39-11, you specify the location of your spread- sheet as shown in Figure 39-12 as well as the field delimiter and whether the first row of the spreadsheet contains the column names. Reviewing the contents of the employee comments spreadsheet, shown in Figure 39-9, you surmise that the spreadsheet’s first row contains well-constructed column names, so you leave the First Row Contains Column Names checkbox checked.

Figure 39-12. Data Load source format options

After you click the Next button, shown in Figure 39-12, you finalize the data import by specifying the new table name as well as the datatypes for each column to import. Oracle Database XE makes a first guess as to the columns’ datatypes. In the dialog shown in Figure 39-13, you specify EMPLOYEE_COMMENTS as the table name and adjust the datatypes to match the data in the spreadsheet. Oracle Database XE provides a few sample rows of the spreadsheet to help you determine the datatype and whether to import the column at all.

Figure 39-13. Data Load table name and column name format options

When you click the Next button shown in Figure 39-13, you see the last screen before the data import occurs and you will specify the primary key of your new table. You do not want to use EMPLOYEE_NUMBER as the primary key because the imported table may have more than one comment per employee. Therefore, you direct Oracle Database XE to create a new column, EMPLOYEE_COMMENTS_NUMBER, for the primary key, as shown in Figure 39-14. Oracle Database XE uses a new sequence, EMPLOYEE_COMMENTS_SEQ, to populate the primary key column. See Chapter 30 for more information on how to create and use sequences.

Figure 39-14. Data Load table name and column name format options

The moment you have been waiting for has finally arrived. When you click the Load Data button, Oracle Database XE creates the table and loads the spreadsheet data into the table. Figure 39-15 shows the status of the table including how many rows imported successfully and unsuccessfully.

Figure 39-15. Data Load results and status

Browsing to Home ➤ Object Browser, you see the table EMPLOYEE_COMMENTS among the other tables owned by HR in Figure 39-16.

Figure 39-16. Browsing the contents of the EMPLOYEE_COMMENTS table

You can now use the table EMPLOYEE_COMMENTS as you would any other table in your schema. For example, to show the name and e-mail address of the employees referenced in the EMPLOYEE_COMMENTS table, you can join the EMPLOYEES table to the EMPLOYEE_COMMENTS table using this query:

select employee_number, last_name, first_name, email, comment_date, comment_text
from employees e join employee_comments ec
on e.employee_id = ec.employee_number order by employee_number, comment_date
;

You can see the results of the query in Figure 39-17.

Figure 39-17. Query results from joining EMPLOYEES to EMPLOYEE_COMMENTS

Summary

This chapter gave you a whirlwind tour of some of the basic ways you can get data into your database from other data sources as well as export data to text files, spreadsheets, or other databases. Although you should be able to use the Oracle Database XE GUI utilities on a regular basis to import and export text files and spreadsheets, we also showed you how to use the SPOOL command with the SELECT statement when you can’t get to a Web browser and you want to perform your export operation using a batch job.
In the next and final chapter we make sure you can easily back up your data as well as recover your data in the event of a disaster of some type—when, not if, some kind of failure occurs in your database.