Previous | Table of Contents | Next

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.

Summary

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.

From Here…

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.

Review Questions

  1. Create a report listing of the employees by birth date. The report should have a title and footer. The title should have the current date and some text explaining the purpose of the report. The footer should contain the page number.

Page 101

  1. Re-create a report listing the employee's wages by gender, wages descending. Follow this format:
                       16-FEB-97               EMPLOYEE WAGES                  Page:   1
                                                      BY
                                               GENDER, WAGES
                                    LAST
                       GENDER      NAME            WAGES
                          M           CARTER          13000
    
  2. Modify the report in #2 so that it displays each employee's department as well as ordering the report by employee. Modify the headings appropriately. Be certain that department is only printed when the department number changes, and two spaces precede the printing of this value.
  3. Modify the report in #3 so that each department's employees are listed on a new page. Do not print the department number on each row. It should be included in the title.
  4. Modify the report in #4 so that the each department's average and total wages are computed for each department.
  5. Spool the output of #5 to a file. Change the default settings so that the output does not display on-screen. Print the report. Be certain to change the settings back to the default.

Page 102

Previous | Table of Contents | Next