Introduction
Querying a database is just half of the battle. The other half is making the data that is pulled out of the table look pretty. Reports are used for those non-techie, higher ups that like it when things look pretty.
| Command | Description |
| START or @ | Executes a script file. |
| COLUMN | Defines the appearance of column headings and the format of the column data. |
| TTITLE | Adds a header to the top of each report page. |
| BTITLE | Adds a footer to the bottom of each report page. |
| BREAK | Suppresses duplicated data for the specific column(s) when presented in a sorted order. |
| COMPUTE | Performs calculations in a report based on the AVG, SUM, COUNT, MIN, or MAX statistical functions. |
| SPOOL | Redirects output to a text file. |
| COLUMN Options | Description |
| HEADING | Adds a column heading to a specified column. |
| FORMAT | Defines the width of columns and applies specific formats to columns containing numeric data. |
| NULL | Indicates text to be substituted for NULL values in a specific column. |
| Environment Variables | Description |
| UNDERLINE | Specifies the symbol to be used to separate a column heading from the contents of the column. |
| LINESIZE | Establishes the maximum number of characters that can appear on a single line of output. |
| PAGESIZE | Establishes the maximum number of lines that can appear on one page of output. |
| MARKUP HTML | Enables output from client to be saved as HTML. |
COLUMN Command
The COLUMN command can be used to format both a column heading and the data that is being analyzed.
COLUMN [ columnname | columnalias ] [ option ]
When using the COLUMN command remember that if a column alias is used, you must use it to identify the column that you are working with in the COLUMN command.
| Column Option | Description |
| FORMAT format_model | Applies a specific format to the column data. |
| HEADING column_heading | Indicates the heading to be used for a specified column. |
| NULL text_message | Identifies the text to be used in place of a NULL value. |
The FORMAT option allows you to format the data that will be displayed. Using this option allows you to show a set amount of digits in a value, dollar signs, decimal points, commas, and the width of the field.
| Format Code | Description | Example | Output |
| 9 | Indicates the position of numeric data, all leading zeros are suppressed. | 9999 | 64 |
| $ | Includes a dollar sign in output | $9999 | $64 |
| , | Indicates where to put the comma when needed. | 9,999 | 64 or 6,400 |
| . | Indicates where to put the decimal. | 9999.99 | 64.64 |
| An | Identifies the width of a column in a report | A16 | ( column will be 16 places wide ) |
The TO_CHAR function can be used to obtain the same results when displaying data. This does come down to a preference, but in the event that you'd like to reuse a query it's better to use the COLUMN command. However, if you want to format a date, the COLUMN command does not provide a model for formatting dates and therefore, you must use the TO_CHAR to accomplish this.
COLUMN order# FORMAT A15
COLUMN cost FORMAT $9,999.99
Heading Option of Column
This option of the COLUMN command is used to specify a column heading for a particular column. The column named assigned by the HEADING option of the COLUMN command cannot be referenced in a SELECT statement. The column name assigned by the HEADING option can contain line breaks. To create a line break in a heading, use the '|' character. The HEADING option is similar to creating an alias for a column in a SELECT statement.
COLUMN cost HEADING 'Cost'
COLUMN retail HEADING 'Retail|Price'
Headings are separated by a series of dashes in text mode. This can be turned off allowing you to specify the number of dashing you wish to have. Typically you would have as many dashes as number of characters in the heading. To turn off the underline, you must set an environment variable:
SET UNDERLINE off
Null Option of Column
Reports shouldn't have any NULL values or blank spots. To make the report more eye pleasing the NULL option of the COLUMN command.
COLUMN ship_date HEADING 'Ship Date' NULL 'Not Shipped'
Headers and Footers
Headers and footers create a more polished look for reports. A header holds the title of the report. The TTITLE indicates the text or variables to be displayed at the top of the report. The BTITLE is everything that is to be printed at the bottom of the report.
TTITLE | BTITLE [option [ text | variable ] ] [ ON | OFF ]
| Option | Description |
| CENTER | Centers the data that is to be displayed. |
| FORMAT | Applies a format model to the data to be displayed. |
| LEFT | Aligns data to be displayed to the left. |
| RIGHT | Aligns data to be displayed to the right. |
| SKIP n | Indicates the number of lines to skip before the display of data resumes. |
You can apply alignment and format options to either text entered as a literal string or to SQL*Plus variables.
| Variable | Description |
| SQL.LNO | Current line number. |
| SQL.PNO | Current page number. |
| SQL.RELEASE | Current Oracle release number. |
| SQL.USER | Current user name. |
LINESIZE & PAGESIZE
LINESIZE is used to set the width of the page while PAGESIZE is used to set the length of the page. To set the width of the report to 50 and the length of each page to 100 insert the SET command for LINESIZE and PAGESIZE you would use the following lines:
SET LINESIZE 50
SET PAGESIZE 100
Break Command
The break command is used to suppress duplicate data. This command would most likely be used if you were listing items and sorting them by category. Instead of having the category repeating over and over, it will only be listed once, followed by a blank space until a different category is listed.
BREAK ON columnname | columnalias [ ON ] [ SKIP n | page ]
Clear Command
The CLEAR command is used to clear the settings that are applied to the BREAK and COLUMN commands. After you issue these commands, they are used again and again until you explicitly tell Oracle to stop using them.
CLEAR COLUMN | BREAK
Compute Command
The COMPUTE command is used to show the average, sum, count, max, or min. You would use something like this when you wanted to know what a customer owes you.
COMPUTER statisticalfunction OF columnname | REPORT ON groupname
COMPUTER SUM OF total ON customer#
BREAK ON customer# SKIP 1
| CUSTOMER# | ORDER# | TOTAL |
| 1001 | 1003 | 106.85 |
| 1018 | 75.90 | |
| ************** | --------- | |
| sum | 182.75 | |
| 1003 | 1006 | 54.50 |
| 1016 | 89.95 | |
| ************** | --------- | |
| sum | 144.45 | |
| 1004 | 1008 | 39.90 |
| ************** | --------- | |
| sum | 39.90 | |
| 1005 | 1000 | 19.95 |
| 1009 | 41.95 | |
| ************** | --------- | |
| sum | 61.90 |
Saving and Running Scripts
SQL*Plus does offer a way of loading saved scripts which saves time in entering data. This can be achieved by one of two ways. The first way is by using a text editor such as notepad and saving the file as '.sql'. The other way is writing the script from the SQL command line. just type ed or edit and when you're are done with the script go up to File->Save As.
Once the script has been saved you can execute it one of two ways from the command line.
START path_to_file.sql
or
@path_to_file.sql
In the Internet interface of SQL*Plus, you can hack out a query and click on the Save Script button. To load a script simply click on the Load Script button.
SPOOL Command
After running a report, it'd be helpful if we could save it. Using the SPOOL command will allow us to do just that. The syntax is simple:
SPOOL path_to_file
To use the spool, you would do:
SPOOL C:\reportout.txt
SET LINESIZE 20
SET PAGESIZE 20
....
SPOOL OFF
CLEAR BREAK
CLEAR COLUMN
Instead of text you can also output HTML by setting another environment variable:
SET MARKUP HTML ON | OFF