Page 100
You can use these settings from the editor, or you can change them at the beginning of a sql command file. To determine the current setting and the old value if it has been changed, use the show command.
To direct the output of your sql to a file for review or printing, use the spool command. To begin spooling, enter the command along with the name of the file directly into the editor or as a command in the command file. Be certain to put the full directory location (c:\user\test.txt) down for the file name. It never fails that I have several students that spooled their first report to a file and can't find the file because SQL*PLUS placed it in Oracle's default directory.
The output continues to be directed to the file until the spool off command is issued. At this time, you can view the file by using Notepad or some other text-editing product. You must also use these products to print the report because SQL*PLUS does not have print features.
SQL*PLUS has one additional way to open a spool file: clicking the File/Spool/Spool File menu option. An open file dialog box appears. Select the directory you want the file to exist in, and then enter the file name. Press OK. The output is directed to this file. To shut off the spooling, click the File/Spool Off option on the menu.
SQL*PLUS has the functionality to create highly formatted reports. Titles and footers can be placed on the report by using the ttitle and btitle commands. A large variety of formats are available for the columns. Oracle7 also provides many different number formats that you can use in all the Oracle products. The break command page breaks the report at specified places on the report. The compute command calculates and returns subtotals at the page breaks. You can modify the SQL*PLUS environment by using the set command settings.
Up to this point, you have been selecting data from one table. The next chapter describes combining columns from more than one table into a new virtual record. You do this with a technique called a join. You can add records from two or more tables by using set operators called union, minus, and intersection. These techniques are used to relate the records from a normalized database. These very powerful tools create information for users. They can lead to some misleading results when used improperly.
Page 101
16-FEB-97 EMPLOYEE WAGES Page: 1 BY GENDER, WAGES LAST GENDER NAME WAGES M CARTER 13000
Page 102