Designing a Simple Report

The first part of this chapter will lead you through the five steps involved in generating a simple, columnar report. This report will be complete with page headings, page footings, and column headings. In addition, you will learn about several settings, controlled by various SET commands, that are useful when printing and formatting reports .

5.3.1 Step 1: Formulate the Query

The first step to designing a report is to formulate the underlying SQL query. There is little point in doing anything else until you have done this. The remaining steps all involve formatting and presentation. If you haven't defined your data, there is no reason to worry about formatting.

For this chapter, let's look at developing a report that answers the following questions:

  • To what projects is each employee assigned?
  • How many hours have been charged to each project?
  • What is the cost of those hours?

One way to satisfy these requirements would be to develop a report based on the query in Example 5-1, which summarizes the hours and dollars charged by employees to each of their projects.

Example 5-1. Summarizing hours and dollars by employee and project

SELECT e.employee_name,

 p.project_name,

 SUM(ph.hours_logged) ,

 SUM(ph.dollars_charged)

FROM employee e INNER JOIN project_hours ph

 ON e.employee_id = ph.employee_id

 INNER JOIN project p 

 ON p.project_id = ph.project_id

GROUP BY e.employee_id, e.employee_name,

 p.project_id, p.project_name;

If you execute this query using SQL*Plus, here's what the output will look like:

EMPLOYEE_NAME

----------------------------------------

PROJECT_NAME SUM(PH.HOURS_LOGGED)

---------------------------------------- --------------------

SUM(PH.DOLLARS_CHARGED)

-----------------------

Marusia Churai

Corporate Web Site 20

 3380



Marusia Churai

Enterprise Resource Planning System 24

 4056



EMPLOYEE_NAME

----------------------------------------

PROJECT_NAME SUM(PH.HOURS_LOGGED)

---------------------------------------- --------------------

SUM(PH.DOLLARS_CHARGED)

-----------------------



Marusia Churai

Accounting System Implementation 24

 4056



Marusia Churai

Data Warehouse Maintenance 20

...

Looks ugly, doesn't it? I wouldn't want to hand that to a client or my boss. It's a start though. At least now you can see what the data looks like, and you know what you have to work with.

Executing the Example Reports

You should execute the example reports in this chapter from your operating system command prompt by issuing a SQL*Plus command such as the following:

sqlplus


username


/


password


@


scriptfile


 

For example, to execute Example 5-1, I personally use this command:

sqlplus gennick/secret @ex5-1.sql
 

This approach ensures that each script begins from a known state, the state in which SQL*Plus starts. Beginning with such a clean slate is important because the formatting commands you'll learn about in this chapter are cumulative and their settings stick around for the duration of your SQL*Plus session. If you run the reports in this chapter from the SQL*Plus prompt, and especially if you run them out of order, your results won't match what you see in these pages.

 

5.3.2 Step 2: Format the Columns

Now that you have the data, you can begin to work through the formatting process. Look again at the listing produced in step 1. At least three things can be done to improve the presentation of the data:

  • Get each record to fit on just one line.
  • Use better column headings.
  • Format the numbers .

The first thing that probably leaps out at you is the need to avoid having report lines so long that they wrap around onto a second line and become difficult to read. This is often a result of SQL*Plus allowing for the maximum width in each column. Another cause is that for calculated columns, the entire calculation is used for the column heading. That can result in some long headings.

5.3.2.1 Column headings

Use the COLUMN command to format the data returned from a SELECT statement. It allows you to specify heading, width, and display formats. The following commands use COLUMN's HEADING clause to specify more readable column headings for our report:

COLUMN employee_name HEADING "Employee Name"

COLUMN project_name HEADING "Project Name"

COLUMN SUM(PH.HOURS_LOGGED) HEADING "Hours"

COLUMN SUM(PH.DOLLARS_CHARGED) HEADING "DollarsCharged"

 

You can refer to the calculated columns in the query by using their calculations as their names . However, doing so is cumbersome, to say the least, and requires you to keep the two copies of the calculation in sync. There is a better way. You can give each calculated column an alias and use that alias in the COLUMN commands. To give each column an alias, the changes to the query look like the following:

SUM(PH.HOURS_LOGGED) hours_logged ,

SUM(PH.DOLLARS_CHARGED) dollars_charged

 

The commands to format these two columns then become:

COLUMN hours_logged HEADING "Hours"

COLUMN dollars_charged HEADING "DollarsCharged"

 

The heading for the dollars_charged column has a vertical bar separating the two words. This vertical bar tells SQL*Plus to place the heading on two lines and allows you to use two rather long words without the need for an excessive column width.

The vertical bar is the default heading separator character and may be changed with the SET HEADSEP command. See Appendix A for details.

 

5.3.2.2 Numeric display formats

Next , you can specify more readable display formats for the numeric columns via the FORMAT clause. COLUMN commands are cumulative, so one approach is to execute the COLUMN commands to set the headers followed by some more COLUMN commands to set the display formats:

COLUMN employee_name HEADING "Employee Name"

COLUMN project_name HEADING "Project Name"

COLUMN hours_logged HEADING "Hours"

COLUMN dollars_charged HEADING "DollarsCharged"



COLUMN hours_logged FORMAT 9,999

COLUMN dollars_charged FORMAT 9,999.99

 

This cumulative approach is handy in an interactive session because you can continually refine your column formatting without having to respecify formatting that you're already happy with. In a script file, though, you're better off issuing just one COLUMN command per column, followed by all formatting options that you wish to specify for that column. This way, all formatting for a given column is in one place:

COLUMN employee_name HEADING "Employee Name"

COLUMN project_name HEADING "Project Name"

COLUMN hours_logged HEADING "Hours" FORMAT 9,999

COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99

 

5.3.2.3 Text display formats

Finally, you can use FORMAT to specify a shorter length for the employee_name and project_name columns. The database allows each of those columns to contain up to 40 characters, but a visual inspection of the output shows that the names are typically much shorter than that. The format clauses shown next make these columns each 20 characters wide:

COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED

COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED

 

Normally, SQL*Plus will wrap longer values onto a second line. The WORD_WRAPPED keyword keeps SQL*Plus from breaking a line in the middle of a word.

WRAPPED and TRUNCATE are alternatives to WORD_WRAPPED. WRAPPED allows a longer value to break in the middle of a word and wrap to the next line. TRUNCATE does what it says; it throws away characters longer than the format specification allows.

 

5.3.2.4 Report output after formatting the columns

Example 5-2 pulls together all the formatting and SQL changes discussed so far.

Example 5-2. Hours and dollars report with the columns nicely formatted

--Format the columns

COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED

COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED

COLUMN hours_logged HEADING "Hours" FORMAT 9,999

COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99

 

--Execute the query to generate the report.

SELECT e.employee_name,

 p.project_name,

 SUM(ph.hours_logged) hours_logged,

 SUM(ph.dollars_charged) dollars_charged

FROM employee e INNER JOIN project_hours ph

 ON e.employee_id = ph.employee_id

 INNER JOIN project p 

 ON p.project_id = ph.project_id

GROUP BY e.employee_id, e.employee_name,

 p.project_id, p.project_name;



EXIT

 

Here is what the output will look like:

Employee Name Project Name Hours Charged

-------------------- -------------------- ------ ------------

Marusia Churai Corporate Web Site 20 ,380.00

Marusia Churai Enterprise Resource 24 ,056.00

 Planning System



Marusia Churai Accounting System 24 ,056.00

 Implementation



Marusia Churai Data Warehouse 20 ,380.00

 Maintenance

 

This is a great improvement over step 1. The headings are more readable. The numbers, particularly the dollar amounts, are formatted better. Most records fit on one line, and when two lines are needed, the data are wrapped in a much more readable format.

A blank line has been inserted after every record with a project name that wraps to a second line. That blank line is a record separator , and it's added by SQL*Plus every time a wrapped column is output as part of a report. I suppose it is added to prevent confusion because, in some circumstances, you might think that the line containing the wrapped column data represented another record in the report. I usually turn it off; this is the command:

SET RECSEP OFF

 

The next step is to add page headers and footers to the report.

5.3.3 Step 3: Add Page Headers and Footers

Page headers and footers may be added to your report through the use of the TTITLE and BTITLE commands. TTITLE and BTITLE stand for "top title" and "bottom title," respectively.

5.3.3.1 The top title

TTITLE commands typically end up being a long string of directives interspersed with text, and they often span multiple lines. Let's say you want a page header that looked like this:

The Fictional Company

 

 

I.S. Department Project Hours and Dollars Report

=============================================================

 

This heading is composed of the company name centered on the first line, two blank lines, a fourth line containing the department name and the report title, followed by a ruling line made up of equal sign characters. You can begin to generate this heading with the following TTITLE command:

TTITLE CENTER "The Fictional Company"

 

The keyword CENTER is a directive telling SQL*Plus to center the text that follows . In their documentation, Oracle sometimes uses the term printspec to refer to such directives.

Always begin a TTITLE command with a directive such as LEFT, CENTER, or RIGHT. Failure to do this causes SQL*Plus to interpret the command as an old, now obsolete, and much less functional form of the command.

 

To get the two blank lines into the title, add a SKIP printspec as follows:

TTITLE CENTER "The Fictional Company" SKIP 3

 

SKIP 3 tells SQL*Plus to skip forward three lines. This results in two blank lines and causes the next report line to print as the third line. To generate the fourth line of the title, containing the department name and the report name, you again add on to the TTITLE command:

TTITLE CENTER "The Fictional Company" SKIP 3 -

 LEFT "I.S. Department" -

 RIGHT "Project Hours and Dollars Report"

 

The text "I.S. Department" will print flush left because it follows the LEFT printspec, and the report title will print flush right because it follows the RIGHT printspec. Both strings will print on the same line because there is no intervening SKIP printspec. The last thing to do is to add the final ruling line composed of equal sign characters, giving you this final version of the TTITLE command:

TTITLE CENTER "The Fictional Company" SKIP 3 -

 LEFT "I.S. Department" -

 RIGHT "Project Hours and Dollars Report" SKIP 1 -

 LEFT "============================================================="

 

This is actually one long command. The hyphens at the end of the first three lines are SQL*Plus command continuation characters. There are 61 equal sign characters in the last line of the title.

You must use the SKIP directive to advance to a new line. If you want to advance just one line, use SKIP 1. SQL*Plus will not automatically advance for you. If you remove the two SKIP directives from the above TTITLE command, you will end up with a one-line title consisting entirely of equal signs.

 

5.3.3.2 The bottom title

The BTITLE command works the same way as TTITLE, except that it defines a footer to appear at the bottom of each page of the report. As with TTITLE, you should always begin a BTITLE command with a printspec such as LEFT or RIGHT, as opposed to text or a variable name. If you want a footer composed of a ruling line and a page number, you can use the following BTITLE command:

BTITLE LEFT "=============================================================" -

 SKIP 1 -

 RIGHT " Page " FORMAT 999 SQL.PNO

 

This BTITLE command introduces two features that haven't been shown in previous examples. The first is the FORMAT parameter, which in this case specifies a numeric display format to use for all subsequent numeric values. The second is the system variable SQL.PNO, which supplies the current page number. Table 5-1 lists several values, maintained automatically by SQL*Plus, that you can use in report headers and footers.

Table 5-1. SQL*Plus system variables

System variable

Value

SQL.PNO

Current page number

SQL.LNO

Current line number

SQL.RELEASE

Current Oracle release

SQL.SQLCODE

Error code returned by the most recent SQL query

SQL. USER

Oracle username of the user running the report

 

The values in Table 5-1 have meaning only to SQL*Plus and can be used only when defining headers and footers. They cannot be used in SQL statements such as INSERT or SELECT.

5.3.3.3 Setting the line width

One final point to bring up regarding page titles is that the directives RIGHT and CENTER operate with respect to the current line width. The default line width, or linesize as it is called in SQL*Plus, is 80 characters. So by default, a centered heading will be centered over 80 characters. A flush right heading will have its last character printed in the 80th position. This presents a slight problem because our report, using the column specifications given in step 2, is only 61 characters wide. The result will be a heading that overhangs the right edge of the report by 19 characters, and that won't appear centered over the data. You could choose to live with that, or you could add this command to the script:

SET LINESIZE 61

 

Setting the linesize tells SQL*Plus to format the headings within a 61-character line. It also tells SQL*Plus to wrap or truncate any lines longer than 61 characters, but the column specifications in this report prevent anything like that from occurring.

The number of equal sign characters in the ruling line must match the linesize. Otherwise , the ruling line will be too short or too long. Either way, it will look tacky.

 

5.3.3.4 Report output with page titles

Example 5-3 shows our report generation script with the addition of the TTITLE, BTITLE, and SET LINESIZE commands.

Example 5-3. Hours and dollars report with page titles added

--Set the linesize, which must match the number of equal signs used

--for the ruling lines in the headers and footers.

SET LINESIZE 61

 

--Setup page headings and footings

TTITLE CENTER "The Fictional Company" SKIP 3 -

 LEFT "I.S. Department" -

 RIGHT "Project Hours and Dollars Report" SKIP 1 -

 LEFT "============================================================="

 

BTITLE LEFT "=============================================================" -

 SKIP 1 -

 RIGHT "Page " FORMAT 999 SQL.PNO

 

--Format the columns

COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED

COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED

COLUMN hours_logged HEADING "Hours" FORMAT 9,999

COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99

 

--Execute the query to generate the report.

SELECT e.employee_name,

 p.project_name,

 SUM(ph.hours_logged) hours_logged,

 SUM(ph.dollars_charged) dollars_charged

FROM employee e INNER JOIN project_hours ph

 ON e.employee_id = ph.employee_id

 INNER JOIN project p 

 ON p.project_id = ph.project_id

GROUP BY e.employee_id, e.employee_name,

 p.project_id, p.project_name;



EXIT

 

Executing this script will produce the following output:

The Fictional Company





I.S. Department Project Hours and Dollars Report

=============================================================

 Dollars

Employee Name Project Name Hours Charged

-------------------- -------------------- ------ ------------

Marusia Churai Corporate Web Site 20 ,380.00

Marusia Churai Enterprise Resource 24 ,056.00

 Planning System

=============================================================

 Page 1



 The Fictional Company





I.S. Department Project Hours and Dollars Report

=============================================================

 Dollars

Employee Name Project Name Hours Charged

-------------------- -------------------- ------ ------------



Marusia Churai Accounting System 24 ,056.00

 Implementation

=============================================================

 Page 2

...

 

Only a few things are left to clean up before you can print this report; one obvious improvement is to fix the pagination in order to get more than 14 lines per page.

Ruling Lines in Page Titles

Example 5-3 shows the use of a ruling line in a page title. That particular line is composed of 61 equal-sign characters that have been painstakingly typed into the TTITLE command. Want to avoid having to count those out? Use the following incantation instead:

COLUMN ruling_line NEW_VALUE ruling_line

SELECT RPAD('=',61,'=') ruling_line

FROM dual;
 

Then, replace that long string of equal sign characters in TTITLE with &ruling_line :

TTITLE CENTER "The Fictional Company" SKIP 3 -

 LEFT "I.S. Department" -

 RIGHT "Project Hours and Dollars Report" SKIP 1 -

 LEFT &ruling_line
 

Do the same thing for BTITLE:

BTITLE LEFT &ruling_line -

 SKIP 1 -

 RIGHT "Page " FORMAT 999 SQL.PNO
 

The script in ex5-3b.sql uses this technique, which comes courtesy of Tom Kyte. If you're not familiar with substitution variables and the use of COLUMN...NEW_VALUE, you'll find a more detailed explanation of this technique in "Master/Detail Reports" later in this chapter.

 

5.3.4 Step 4: Format the Page

Most of the work to produce this report is behind you. Step 4 involves adjusting two SQL*Plus settings that control page size and pagination. These two settings are:

pagesize

Controls the number of lines per page. SQL*Plus prints headings and advances to a new page every pagesize lines.

newpage

Controls the size of the top margin, or tells SQL*Plus to use a formfeed character to advance to a new page.

The SET command is used to define values for each of these settings. The values to use depend primarily on your output device, the paper size being used, and the font size being used. Because SQL*Plus is entirely character-oriented, these settings are defined in terms of lines. The first question to ask, then, is how many lines will your printer print on one page of paper.

5.3.4.1 How many lines on a page?

Years ago, before the advent of laser printers with their multiplicity of typefaces , typestyles, and typesizes (i.e., fonts), this was an easy question to answer. The standard vertical spacing for printing was six lines per inch, with eight lines per inch occasionally being used. Thus, an 11-inch-high page would normally contain 66 lines. Most printers were pinfeed printers taking fanfold paper, and would permit you to print right up to the perforation, allowing you to use all 66 lines if you were determined to do so.

Today's printers are much more complicated, yet most will still print six lines per inch if you send them plain ASCII text. However (and this is important), many printers today will not allow you to print right up to the top and bottom edges of the paper. This is especially true of laser printers, which almost always leave a top and bottom margin. You may have to experiment a bit with your printer to find out exactly how many lines you can print on one page.

I usually duck this issue entirely by setting PAGESIZE to a safely low setting, usually below 60 lines, and setting NEWPAGE to zero, which causes SQL*Plus to use a formfeed character to advance to a new page. My examples in this chapter use this approach.


The other issue to consider is the font size you will be using to print the report. I typically just send reports to a printer as plain ASCII text, and that usually results in the use of a 12-point, monospaced font, which prints at six lines per inch. Sometimes, however, I'll load the file containing a report into an editor, change the font size to something larger or smaller, and then print the report. If you do that, you'll need to experiment a bit to find out how many lines will fit on a page using the new font size.

5.3.4.2 Setting the pagesize

You set the pagesize with the SQL*Plus command SET PAGESIZE as follows:

SET PAGESIZE 55

 

This tells SQL*Plus to print 55 lines per page. Those 55 lines include the header and footer lines as well as the data. As it prints your report, SQL*Plus keeps track of how many lines have been printed on the current page. SQL*Plus knows how many lines make up the page footer. When the number of remaining lines equals the number of lines in your footer, SQL*Plus prints the footer and advances to the next page. How SQL*Plus advances the page depends on the NEWPAGE setting.

5.3.4.3 Setting the page advance

There are two methods SQL*Plus can use to advance the printer to a new page. The first method, and the one used by default, is to print exactly the right number of lines needed to fill one page. Having done that, the next line printed will start on a new page. Using this method depends on knowing how many lines you can fit on one page, and switching printers can sometimes cause your report to break. One laser printer, for example, may have a slightly larger top margin than another.

A more reliable method is to have SQL*Plus advance the page using the formfeed character. The command to do this is:

SET NEWPAGE 0

 

The NEWPAGE setting tells SQL*Plus how many lines to print to advance to a new page. The default value is 1. Setting NEWPAGE to 0 causes SQL*Plus to output a formfeed character when it's time to advance the page.

If you set NEWPAGE to 0, do not set PAGESIZE to exactly match the number of lines you can physically print on your printer. Doing that may cause your output to consist of alternating detail pages and blank pages. That's because filling the physical page will itself advance your printer to a new page. The subsequent formfeed advances the page again, resulting in a skipped page. Instead, set PAGESIZE to at least one line less than what will fit on a physical page.

 

The examples in this chapter use a NEWPAGE setting of 0 and a PAGESIZE of 55 lines, so you can add the following three lines to the script file:

--Set up pagesize parameters

SET NEWPAGE 0

SET PAGESIZE 55

 

You are free to put these lines anywhere you like so long as they precede the SELECT statement that generates the report. Example 5-4 shows them added to the beginning of the script file.

Example 5-4. Hours and dollars report with pagination

--Setup pagesize parameters

SET NEWPAGE 0

SET PAGESIZE 55



--Set the linesize, which must match the number of equal signs used

--for the ruling lines in the headers and footers.

SET LINESIZE 61

 

--Set up page headings and footings

TTITLE CENTER "The Fictional Company" SKIP 3 -

 LEFT "I.S. Department" -

 RIGHT "Project Hours and Dollars Report" SKIP 1 -

 LEFT "============================================================="

 

BTITLE LEFT "=============================================================" -

 SKIP 1 -

 RIGHT "Page " FORMAT 999 SQL.PNO

 

--Format the columns

COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED

COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED

COLUMN hours_logged HEADING "Hours" FORMAT 9,999

COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99

 

--Execute the query to generate the report.

SELECT e.employee_name,

 p.project_name,

 SUM(ph.hours_logged) hours_logged,

 SUM(ph.dollars_charged) dollars_charged

FROM employee e INNER JOIN project_hours ph

 ON e.employee_id = ph.employee_id

 INNER JOIN project p 

 ON p.project_id = ph.project_id

GROUP BY e.employee_id, e.employee_name,

 p.project_id, p.project_name;



EXIT

 

5.3.5 Step 5: Print It

Run the script file one more time and look at the output on the screen. If everything looks good, you are ready to print. To print a report, you need to have SQL*Plus write the report to a file and then print that file. When people speak of writing SQL*Plus output to a file, the term spool is often used as a verb. You are said to be spooling your output to a file. The SPOOL command is used for this purpose, and you will need to use it twice, once to turn spooling on and again to turn it off.

5.3.5.1 Spooling to a file

To send report output to a file, put SPOOL commands immediately before and after the SQL query as shown here:

SPOOL proj_hours_dollars.lis

SELECT E.EMPLOYEE_NAME,

 . . . 

SPOOL OFF

 

The first SPOOL command tells SQL*Plus to begin echoing all output to the specified file. After this command executes, everything you see on the screen is echoed to this file. The second SPOOL command turns spooling off and closes the file.

You may wish to add two other commands to the script file before generating the report. The first is:

SET FEEDBACK OFF

 

Turning feedback off gets rid of the "50 rows selected" message, which you may have noticed at the end of the report when you ran earlier versions of the script. The second command you may want to add is:

SET TERMOUT OFF

 

This command does what it says. It turns off output to the display (terminal output) but allows the output to be written to a spool file. Your report will run several orders of magnitude faster if SQL*Plus doesn't have to deal with updating and scrolling the display, especially true if you are running the Windows GUI version of SQL*Plus. You should definitely use SET TERMOUT OFF when spooling any large report. I usually put the above two settings immediately prior to the SPOOL command. For example:

SET FEEDBACK OFF

SET TERMOUT OFF

SPOOL $HOME/sqlplus/reports/proj_hours_dollars.lst

SELECT E.EMPLOYEE_NAME,

 . . . 

SPOOL OFF

 

Do make sure that you set TERMOUT off prior to executing the SPOOL command to spool the output; otherwise, the SET TERMOUT OFF command will appear in your spool file.

5.3.5.2 The final script

Example 5-5 shows the script for our report after adding the SPOOL commands and the commands to turn feedback and terminal output off. Example 5-5 begins with the SET ECHO OFF command , ensuring that you aren't bothered by having to watch all the remaining commands in the script scroll by on your terminal window.

Example 5-5. Hours and dollars report to be spooled to a file for printing

SET ECHO OFF



--Set up pagesize parameters

SET NEWPAGE 0

SET PAGESIZE 55



--Set the linesize, which must match the number of equal signs used

--for the ruling lines in the headers and footers.

SET LINESIZE 61

 

--Set up page headings and footings

TTITLE CENTER "The Fictional Company" SKIP 3 -

 LEFT "I.S. Department" -

 RIGHT "Project Hours and Dollars Report" SKIP 1 -

 LEFT "============================================================="

 

BTITLE LEFT "=============================================================" -

 SKIP 1 -

 RIGHT "Page " FORMAT 999 SQL.PNO

 

--Format the columns

COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED

COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED

COLUMN hours_logged HEADING "Hours" FORMAT 9,999

COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99



--Turn off feedback and set TERMOUT off to prevent the

--report being scrolled to the screen.

SET FEEDBACK OFF

SET TERMOUT OFF

 

--Execute the query to generate the report.

SPOOL proj_hours_dollars.lst

SELECT e.employee_name,

 p.project_name,

 SUM(ph.hours_logged) hours_logged,

 SUM(ph.dollars_charged) dollars_charged

FROM employee e INNER JOIN project_hours ph

 ON e.employee_id = ph.employee_id

 INNER JOIN project p 

 ON p.project_id = ph.project_id

GROUP BY e.employee_id, e.employee_name,

 p.project_id, p.project_name;

SPOOL OFF

EXIT

 

5.3.5.3 Executing the report

If you've stored the script for the report in a text file, you can execute that file from the SQL*Plus prompt like this:

SQL>

@ex5-5

 

The @ character in front of a filename tells SQL*Plus to execute the commands contained in that file.

5.3.5.4 Printing the file

After you run the script, the complete report will be in the proj_hours_dollars.lst file. To print that file, you must use whatever print command is appropriate for your operating system. On a Windows machine, assuming that LPT1 is mapped to a printer, you can use the following DOS command:

COPY c:aproj_hours_dollars.lis LPT1:

 

A typical Unix print command would be:

lp proj_hours_dollars.lis

 

An alternative is to load the file into a word processor such as Microsoft Word or Lotus Word Pro. These programs will interpret formfeeds as page breaks when importing a text file, so your intended pagination will be preserved. After you've imported the file, select all the text and mark it as Courier New 12 point. Then set the top and left margins to their minimum values; for laser printers, half-inch margins usually work well. Next, set the right and bottom margins to zero. Finally, print the report.

One final option, useful with Unix and Linux but not available in any Windows version of SQL*Plus, is to use the SPOOL OUT command instead of SPOOL OFF. SPOOL OUT closes the spool file and then prints that file out to the default printer, saving you the extra step of manually printing it. For whatever reason, Oracle has chosen not to implement SPOOL OUT under Windows. It is, however, available under Unix and Linux.

Running SQL*Plus Reports from Shell Scripts

In production settings, you want to generate SQL*Plus reports from shell scripts that you schedule via cron or some other scheduling system. It's not a safe practice to embed passwords in shell scripts, so how then do you safely invoke SQL*Plus from them? One approach is to use operating system authenticated users, which authenticate to Oracle by virtue of being logged into the operating system.

For example, you can create an Oracle user such as ops$reports to correspond to the Unix user named reports . Any shell script that you schedule to run as the Unix reports user may then invoke SQL*Plus without having to specify a username and password, and the database will implicitly log the SQL*Plus session in as the ops$reports user. You request such an implicit login by using the forward slash (/) where you would otherwise specify a username and password:

sqlplus / @ex5-1
 

The advantage of this technique is that it enables you to invoke SQL*Plus to run scripts without having to compromise security by embedding login information in script files where others may see it. Do be careful, though, to control access to the associated Unix login. In this case, you would need to protect the password for the Unix reports user, as anyone logging on as reports would have implicit access to the database via ops$reports .


     



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net