SQL*Plus can be used to generate reports. Through the use of SELECT statements you can perform queries into the ORACLE database and then alter the display to create polished reports. Some elements of a report over which you have control are: titles, column headings, subtotals and grand totals, reformatting of numbers and text and column computations. Getting SQL*Plus to produce reports according to your needs requires only a few simple commands.

The script is made up of both SQL and SQL*Plus commands. How can you tell the difference? Beginning with the SELECT statement and ending with the semi-colon is SQL which talks to the ORACLE database. The remaining commands are SQL*Plus which are used to format the results of the query into a report.

NOTE: If you are using telnet through pigseye to access ORACLE, you may have difficulty viewing on your screen the reports you produce. Table(s) mentioned in the SELECT statement are printed following the generation of the report. This may prevent your seeing the report because there is no scroll bar to scroll back up to see the report. When you begin your SQL*Plus session, issue the following command:

Setting PAUSE ON will cause SQL*Plus to display one full screen of information and then it waits for you to press enter before it displays the next screen. More… will appear in the lower left corner until you press enter.

Suppose we want an annual salary report. We want all employees from the EMPLOYEE listed on the report, their job title, department number and annual salary. We want employees grouped by department. We want subtotals of annual salary for each department, then a total annual salary at the end of the report. Sound a bit intimidating? Don't worry, we'll build this report in small steps and explain each line as we go. The best place to start is with your SELECT statement. Once you are sure it returns the information you need, then you can begin adding SQL*Plus commands to format the report.

How should we write the SELECT statement that will return the data needed, grouped as we want it?

SQL>SELECT DEPTNO, NAME, JOB, SALARY*12

FROM EMPLOYEE

ORDER BY DEPTNO;

Now we begin building the report

With the SQL*Plus command SPOOL, we can store query results in a file to be edited or printed later. We will call this file repfile. Since only SQL commands are usually stored in the buffer, we need to begin by setting up a buffer that will store our SQL*Plus commands along with the SQL commands. The command to set up this buffer looks like this:  

I will use 'a' for my buffer name.

Then issue the INPUT command so that we may begin putting our file into the buffer.

Hit enter twice to return to the SQL> prompt.

Save the query as empreport. Don't confuse this file with repfile. Empreport will contain the SQL SELECT statement and SQL*Plus commands that produce the report. Repfile will contain the report produced by empreport.

Now run empreport. Since this file begins with a SQL*Plus command, we must use the start or @ command.

The results table looks the same on the screen as previously, but the file repfile now contains the results table. SPOOL <file_name> tells SQL*Plus to store the query results, formatted as you specify, in the file <file_name>. If you do not follow the file name with a period and an extension, the default file extension LST will be added. This file is stored in your directory on csis. You may use WS-FTP to copy it to your computer later. SPOOL OFF turns spooling off. You may receive an error message telling you there is no default destination and to identify your printer. Ignore this message. Your result file is being saved. SPOOL can also be used to send results to a printer.

Let's add a title at the top of the report.

We need to add a line after line 1. LIST line 1, then use the INPUT command to enter the title.

Hit enter twice to get back to the SQL> prompt.

LIST the file.

LIST line 6. Use the INPUT command to add the following line.

Hit enter twice.

Save your query file. Remember to end the command with replace.

Run the file.

Thu Nov 06 page 1

Employee Annual Salaries

As of Nov-6-1997

Notice the current date and page number are placed on the report. The TTITLE command instructs SQL*Plus to place your title at the top of the page. Your title is what you typed within single quotes in the TTITLE command. The vertical line is the heading separator. It tells SQL*Plus that there should be a break wherever the | is placed. The vertical bar in our TTITLE command instructs SQL*Plus to place the text before the bar on one line and the text after the bar on the next line. Notice we included an instruction at the end of the file to turn TTITLE off.

Your report may contain breaks. Let's set the page size, linesize and newpage settings to ensure that our report fits on a page.

LIST line 2, then use the INPUT command to add a new line.

Hit enter twice to return to the SQL> prompt.

Save the file and then run it.

You report should contain the same results, but the format may change so that there are no breaks in the information. To set linesize use the command SET LINESIZE number_of_characters. To set the number of lines on a page, use SET PAGESIZE number_of_lines. To set the number of lines between the beginning of each page and the top title, use SET NEWPAGE number_of_lines. If NEWPAGE is set to zero, SQL*Plus skips zero lines and displays and prints a formfeed character to begin a new page.

Now let's change the column headings on our report.

LIST line 2. We want to add lines following it, so use the INPUT(i) command.

After typing line 6, hit enter twice. Then LIST the file. LIST line 12. Use INPUT to add a new line.

Hit enter twice. Then save the new file. Remember you are replacing the old copy, so you need to type replace after the file name.

Run the file.

Thu Nov 06 page 1

Employee Annual Salaries

As of Nov-6-1997

We used the HEADING clause of the COLUMN command to define more useful column headings for our report. Notice that there are single quotation marks around the new headings that are two words. To change a column heading to two or more words, enclose the new heading in single quotation marks in the COLUMN command. To display a column heading on more than one line, use the vertical bar (|) where you want to begin a new line. Notice we included a command at the end to clear columns.

We have a problem with some of our new headings - they won't fit in the SQL*Plus default display width. We need to reformat the columns so that longer column titles will appear. We will use the line editing capabilities of SQL*Plus. Return to Error Correction in SQL*Plus if you need to review line editing.

SQL> get empreport

LIST line 4 so that we may change it.

Remember that c is the abbreviation for the CHANGE command. We put the old text after the first slash and the new text after the second slash.

LIST line 5. Use the CHANGE command to make the following change.

Save the new file. Then run it.

Thu Nov 06 page 1

Employee Annual Salaries

As of Nov-6-1997

The format of the column title may be changed using the FORMAT clause of the COLUMN command. Since both the NAME and JOB columns in the original table are defined as CHAR data types, we use the letter A followed by  a number representing the width of the column in characters. The EMPLOYEE NAME column will be 15 characters wide and the POSITION column will be 12 characters wide.

Now let's change the format of the salary column so that a dollar sign is placed in front of the salary, a comma is placed after the thousands, and two digits appear after the decimal.

LIST line 6. Use the CHANGE command to edit the line.

Save the new file.

Run the file

Thu Nov 06 page 1

Employee Annual Salaries

As of Nov-6-1997

The format of the salary column was specified as 8 digits with a decimal, a comma after the thousands and a leading $.

Next, let's insert a break after each department.

LIST line 6. Use the INPUT command to add a line.

Hit enter twice to return to the SQL> prompt. Save the file and run it.

BREAK ON is used to create meaningful subset of records in a report. The column specified in the BREAK ON command should be the same column included in your ORDER BY clause. SKIP 2 tells SQL*Plus to insert two blank lines at each break point. Let's compute a subtotal for each department.

6 column salary*12 format $999,990.99 heading 'Annual Salary'

LIST line 7. We want to add a line, so use the INPUT command.

Hit enter twice here.

LIST the file.

SQL>l

6 column salary*12 format $999,990.99 heading 'Annual Salary'

List line 15. Use the INPUT command to add lines.

Hit enter twice. Save the file.

Run the file.

SQL> @empreport

Thu Nov 06 page 1

Employee Annual Salaries

As of Nov-6-1997

The BREAK ON and COMPUTE commands work in conjunction with one another. The totals computed by the COMPUTE SUM command will be for the sections specified by the BREAK ON command. NOTE: Every COMPUTE SUM command requires the use of a BREAK ON command to guide it. The BREAK ON command can be used without a COMPUTE SUM command to provide groupings where no totals are necessary.

We also included commands to clear breaks and computes. Once COMPUTE, BREAK ON, or COLUMN commands are issued they remain active until you clear them or leave SQL*Plus. Therefore it is wise to clear these settings before issuing new commands or the commands from previous reports will act on the current report producing unwanted results.

All we need to do to complete our report is to include a command that will calculate and print the summary total of annual salaries.

SQL>get empreport

6 column salary*12 format $999,990.99 heading 'Annual Salary'

LIST line 7. Use the CHANGE command to modify the line.

LIST line 8. Use INSERT to add a line after line 8.

Hit enter twice to return to the SQL> prompt. Save the file and then run it.

Now we have accomplished our goal of building a complex report step-by-step and saving the report to a file. You may now ftp the file from csis to the computer at which you are working. Use WS-FTP to connect to csis.kennesaw.edu. Enter your username and password. Find the file in your directory. Once a copy of the file is on your computer, you may edit it if needed and print it using Word or another editing program.

Last revised 11/18/1997 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems