Oracle Development Unleashed (3rd Edition)

Page 298

• BTITLE print_options and/or text or variable options

  BTITLE places text at the bottom of each page. You can use various print options to position text at various locations. BTITLE simply centers the text if no print options are specified. print options include BOLD, CENTER, COL, FORMAT, LEFT, RIGHT, SKIP, and TAB. BTITLE spelled out by itself displays the current text setting. Other options you can specify are ON and OFF. BTITLE is ON by default.

• CLEAR and options

  CLEAR resets any of the SQL*Plus formatting commands. You can also use it to clear the screen. The options include BREAKS, BUFFER, COLUMNS , COMPUTES, SCREEN, SQL, and TIMING.

• COLUMN column_name and options

  COLUMN alters the default display attributes for a given column (column_name) of a SQL query. You can use a variety of options, but the more common ones are FORMAT, HEADING, JUSTIFY, NEWLINE, NEW_VALUE, and NOPRINT.

  FORMAT is useful for applying editing to numeric fields, date masks to date fields, and specific lengths to variable-length character fields.

  HEADING overrides the SQL*Plus default heading for the particular column.

  JUSTIFY overrides the SQL*Plus column alignment to the heading default.

  NEWLINE prints the column on the beginning of the next line.

  NEW_VALUE assigns the contents of the column to a SQL*Plus variable (see DEFINE, later in this section). You then can use this value with TTITLE or to store intermediate results for master/detail-type reports , and it is useful to store and pass information between two or more separate SQL statements.

  To format the output of a currency value, you could use the following code:

  COLUMN sal FORMAT ,999.00 HEADING Salary In the next example, the COLUMN command is used to assign a value to home_dir. The first SQL query references the home_dir; all other SQL queries then reference the home_path for the information returned by the first SQL query:

  COLUMN home_dir NEW_VALUE home_path NOPRINT

• COMPUTE function OF options ON break options

  COMPUTE calculates and prints totals for groupings of rows defined by the BREAK command. You can use a variety of standard functions. The most common option is the name of the column in the query on which the total is to be calculated. The break option determines where the totals are to be printed and reset, as defined by the BREAK command.

  The following list, for example, produces a report with totals of monthly_sales and commissions when the sales_rep column value changes:

  BREAK ON sales_rep SKIP 2 BREAK ON REPORT

Page 299

COMPUTE SUM OF monthly_sales ON sales_rep COMPUTE SUM OF commissions ON sales_rep COMPUTE SUM OF monthly_sales ON REPORT COMPUTE SUM OF commissions ON REPORT

It then skips two lines and produces monthly_sales and commissions totals at the end of the report.

• TTITLE print_options and/or text or variable options

  TTITLE places text at the top of each page. You can use various print options that position text at various locations. TTITLE centers the text and adds date and page numbers if no print options are specified. print options include BOLD, CENTER, COL, FORMAT, LEFT, RIGHT, SKIP, and TAB. TTITLE with no options at all displays the current text setting. Other options you can specify are ON and OFF. TTITLE is ON by default.

Miscellaneous Commands

This section presents a variety of commands that enable you to interact with the user , comment on the code, and enhance coding options. These miscellaneous commands are as follows :

• ACCEPT variable number or char PROMPT text

  ACCEPT receives input from the terminal and places the contents in variable. This variable can already have been defined with the DEFINE command. If the PROMPT option is specified, the text is displayed after skipping a line. You can specify the variable attributes of number or char at this time. The variable is a char if not otherwise defined.

• DEFINE variable

  DEFINE creates a user-defined variable and assigns it to be of char (character) format. You can assign this variable to be a default value at this time.

Page 300

• DESC or DESCRIBE database object

  DESCRIBE displays the columns associated with a table, view, or synonym.

• PAUSE text

  PAUSE prints the contents of text after skipping a line and then waits for you to press the Return (or Enter) key.

• PROMPT text

  PROMPT simply skips a line and prints the contents of text.

• REM or REMARK

  SQL*Plus ignores the contents of this line when it is used in SQL*Plus command files. REMARK enables documentation or other comments to be contained in these SQL*Plus command files.

• SET SQL*Plus System Variable

  The SET command controls the default settings for the SQL*Plus environment. You can automatically alter these settings for each SQL*Plus session by including them in the LOGIN.SQL file, discussed earlier in this chapter in the "SQL*Plus Commands" section. See Chapter 6 of Oracle's SQL*Plus User's Guide and Reference for a complete listing of the SET options.

  The following are some common SET options used for reporting: SET LINESIZE 80: Controls the width of the output report line.

  SET PAGESIZE 55: Controls the number of lines per page. The following are some common SET options that suppress various SQL*Plus output: SET FEEDBACK OFF: Suppresses the number of query rows returned.

  SET VERIFY OFF: Suppresses the substitution text when using &variables, including command-line variables .

  SET TERMOUT OFF: Suppresses all terminal output; this is particularly useful with the SPOOL command.

  SET ECHO OFF: Suppresses the display of SQL*Plus commands.

• SPOOL filename or options

  SPOOL opens, closes , or prints an operating-system_dependent file. Specifying SPOOL filename creates an operating-system-dependent file; filename can contain the full pathname of the file and extension. If no file extension is given, the file suffix, LST, is appended (filename.LST). Options include OFF and OUT. If OFF is specified, the operating-system_dependent file simply is closed. If OUT is specified, the operating-system_dependent file is closed and sent to the operating-system_dependent printer assigned as the default printer to the user's operating-system environment.