fullpacodd

System Variable	Description
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time.
Controls when Oracle Database commits pending changes to the database.
Sets the automatic printing of bind variables.
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
Sets the non-alphanumeric character used to end PL/SQL blocks to c.
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
In iSQL*Plus, SET COLSEP determines the column separator character to be printed between column output that is rendered inside <PRE> tags. Sets the text to be printed between selected columns.
Sets the character you can use to terminate a substitution variable reference if you wish to immediately follow the variable with a character that SQL*Plus would otherwise interpret as a part of the substitution variable name.
Controls the number of batches after which the COPY command commits changes to the database.
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
Sets the character used to prefix variables to c.
Sets the depth of the level to which you can recursively describe an object.
Controls whether the START command lists each command in a script as the command is executed.
*SET EDITF[ILE] file_name[.ext]	Sets the default filename for the EDIT command.
Controls where on a page each report begins.
Defines the character you enter as the escape character.
Displays the number of records returned by a query when a query selects at least n records.
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
*SET FLU[SH] {ON OFF}	Controls when output is sent to the user's display device.
Controls printing of column headings in reports.
Defines the character you enter as the heading separator character.
Changes the default instance for your session to the specified instance path.
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.
Specifies the location from which archive logs are retrieved during recovery.
Sets maximum width (in bytes) for displaying LONG, CLOB, NCLOB and XMLType values; and for copying LONG values.
Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, CLOB, NCLOB or XMLType value.
Outputs HTML marked up text, which is the output used by iSQL*Plus.
Sets the number of blank lines to be printed from the top of each page to the top title.
Sets the text that represents a null value in the result of a SQL SELECT command.
Sets the default format for displaying numbers.
Sets the default width for displaying numbers.
Sets the number of lines in each page.
Enables you to control scrolling of your terminal when running reports.
RECSEP tells SQL*Plus where to make the record separation.
Display or print record separators.
Controls whether to display the output (that is, DBMS_OUTPUT PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
*SET SHIFT[INOUT] {VIS[IBLE] INV[ISIBLE]}	Enables correct alignment for terminals that display shift characters.
*SET SHOW[MODE] {ON OFF}	Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET.
*SET SQLBL[ANKLINES] {ON OFF}	Controls whether SQL*Plus puts blank lines within a SQL command or script.
Converts the case of SQL commands and PL/SQL blocks just prior to execution.
*SET SQLCO[NTINUE] {>text}	Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).
*SET SQLN[UMBER] {ON OFF}	Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block.
Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z].
*SET SQLPRE[FIX] {#c}	Sets the SQL*Plus prefix character.
*SET SQLP[ROMPT] {SQL>text}	Sets the SQL*Plus command prompt.
Sets the character used to end and execute SQL commands to c.
*SET SUF[FIX] {SQLtext}	Sets the default file that SQL*Plus uses in commands that refer to scripts.
*SET TAB {ON OFF}	Determines how SQL*Plus formats white space in terminal output.
*SET TERM[OUT] {ON OFF}	Controls the display of output generated by commands executed from a script.
*SET TI[ME] {ON OFF}	Controls the display of the current time.
Controls the display of timing statistics.
*SET TRIM[OUT] {ON OFF}	Determines whether SQL*Plus puts trailing blanks at the end of each displayed line.
*SET TRIMS[POOL] {ON OFF}	Determines whether SQL*Plus puts trailing blanks at the end of each spooled line.
Sets the character used to underline column headings in SQL*Plus reports to c.
Controls whether SQL*Plus lists the text of a SQL statement or PL/SQL command before and after SQL*Plus replaces substitution variables with values.
Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width.
Defines the base URI to use. This is useful to change the prefix of the file to access when writing generic XQuery expressions.
Controls the ordering of results from an XQuery.
Sets the preservation mode for notes created or returned.
Specifies an XQuery context item which can be either a node or a value.

1. Sql Set Column Width

I'm using SQL.Plus to spool a table to a csv file, but the default setting is to extract using fixed width. Each column fills the length with extra spaces which is not contained in DB tables. As you can see from above sample that last two column names are not displayed but column values are displayed. This happens for all output. This issue is not coming when we run the sql scripts manually in toad window but when executed through shell script we face this issue. I have set the property as SET LINESIZE 30000.

To change APPINFO back to its default setting, enter

SET ARRAY[SIZE] {15n}

Sets the number of rows that SQL*Plus will fetch from the database at one time.

Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.

SET AUTO[COMMIT]{ON OFF IMM[EDIATE] n}

Controls when Oracle Database commits pending changes to the database after SQL or PL/SQL commands.

ON commits pending changes to the database after Oracle Database executes each successful INSERT, UPDATE, or DELETE, or PL/SQL block. OFF suppresses automatic committing so that you must commit changes manually (for example, with the SQL command COMMIT). IMMEDIATE functions in the same manner as ON. n commits pending changes to the database after Oracle Database executes n successful SQL INSERT, UPDATE, or DELETE commands, or PL/SQL blocks. n cannot be less than zero or greater than 2,000,000,000. The statement counter is reset to zero after successful completion of n INSERT, UPDATE or DELETE commands or PL/SQL blocks, a commit, a rollback, or a SET AUTOCOMMIT command.

SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.

Note:

For this feature, a PL/SQL block is considered one transaction, regardless of the actual number of SQL commands contained within it.

SET AUTOP[RINT] {ON OFF}

Sets the automatic printing of bind variables.

ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command).

See PRINT for more information about displaying bind variables.

SET AUTORECOVERY [ON OFF]

ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.

No interaction is needed, provided the necessary files are in the expected locations with the expected names. The filenames used are derived from the values of the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT.

OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given. See RECOVER for more information about database recovery.

You must set AUTORECOVERY to ON to use the RECOVER command in iSQL*Plus.

Example

To set the recovery mode to AUTOMATIC, enter

SET AUTOT[RACE] {ON OFF TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).

The report can include execution statistics and the query execution path.

OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics. Information about EXPLAIN PLAN is documented in the Oracle Database SQL Reference.

Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.

The TRACEONLY option may be useful to suppress the query data of large queries. If STATISTICS is specified, SQL*Plus still fetches the query data from the server, however, the data is not displayed.

The AUTOTRACE report is printed after the statement has successfully completed.

Information about Execution Plans and the statistics is documented in the Oracle Database Performance Tuning Guide.

When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.

The formatting of your AUTOTRACE report may vary depending on the version of the server to which you are connected and the configuration of the server. The additional information and tabular output of AUTOTRACE PLAN is supported when connecting to Oracle Database 10g (Release 10.1) or later. When you connect to an earlier database, the older form or AUTOTRACE reporting is used.

AUTOTRACE is not available when FIPS flagging is enabled.

AUTOTRACE PLAN does not use glogin.sql column definitions to display output.

See Tracing Statements for more information on AUTOTRACE.

SET BLO[CKTERMINATOR] {. cON OFF}

Sets the character used to end PL/SQL blocks to c.

It cannot be an alphanumeric character or a whitespace. To execute the block, you must issue a RUN or / (slash) command.

OFF means that SQL*Plus recognizes no PL/SQL block terminator. ON changes the value of c back to the default period (.), not the most recently used character.

SET CMDS[EP] {; c ON OFF}

Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.

ON or OFF controls whether you can enter multiple commands on a line. ON automatically sets the command separator character to a semicolon (;).

Example

To specify a title with TTITLE and format a column with COLUMN, both on the same line, enter

SET ECHO {ON OFF}

Controls whether or not to echo commands in a script that is executed with @, @@ or START. ON displays the commands on screen. OFF suppresses the display. ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.

SET EDITF[ILE] file_name[.ext]

SET EDITFILE is not supported in iSQL*Plus

Sets the default filename for the EDIT command. See EDIT for more information about the EDIT command. The default filename for the EDIT command is afiedt.buf which is the SQL buffer. The buffer has no command history list and does not record SQL*Plus commands.

You can include a path and/or file extension. See SET SUF[FIX] {SQLtext} for information on changing the default extension. The default filename and maximum filename length are operating system specific.

SET EMB[EDDED] {ON OFF}

Controls where on a page each report begins.

OFF forces each report to start at the top of a new page. ON enables a report to begin anywhere on a page. Set EMBEDDED to ON when you want a report to begin printing immediately following the end of the previously run report.

SET ESC[APE] {c ON OFF}

Defines the character used as the escape character.

OFF undefines the escape character. ON enables the escape character. ON changes the value of c back to the default '.

You can use the escape character before the substitution character (set through SET DEFINE) to indicate that SQL*Plus should treat the substitution character as an ordinary character rather than as a request for variable substitution.

Example

If you define the escape character as an exclamation point (!), then

displays this prompt:

To set the output to WORD_WRAPPED, enter

SET SHIFT[INOUT] {VIS[IBLE] INV[ISIBLE]}

SET SHIFTINOUT is not supported in iSQL*Plus

Enables correct alignment for terminals that display shift characters. The SET SHIFTINOUT command is useful for terminals which display shift characters together with data (for example, IBM 3270 terminals). You can only use this command with shift sensitive character sets (for example, JA16DBCS).

Use VISIBLE for terminals that display shift characters as a visible character (for example, a space or a colon). INVISIBLE is the opposite and does not display any shift characters.

Example

To enable the display of shift characters on a terminal that supports them, enter

SET SQLPRE[FIX] {#c}

SET SQLPREFIX is not supported in iSQL*Plus

Sets the SQL*Plus prefix character. While you are entering a SQL command or PL/SQL block, you can enter a SQL*Plus command on a separate line, prefixed by the SQL*Plus prefix character. SQL*Plus will execute the command immediately without affecting the SQL command or PL/SQL block that you are entering. The prefix character must be a non-alphanumeric character.

SET SQLP[ROMPT] {SQL>text}

SET SQLPROMPT is not supported in iSQL*Plus

Sets the SQL*Plus command prompt. SET SQLPROMPT substitute variables dynamically. This enables the inclusion of runtime variables such as the current connection identifier. Substitution variables used in SQLPROMPT do not have to be prefixed with '&', and they can be used and accessed like any other substitution variable. Variable substitution is not attempted for the default prompt, 'SQL> '.

Variable substitution occurs each time SQLPROMPT is SET. If SQLPROMPT is included in glogin.sql, then substitution variables in SQLPROMPT are refreshed with each login or connect.

Example

To change your SQL*Plus prompt to display your connection identifier, enter:

To set the SQL*Plus command prompt to show the current user, enter

To change your SQL*Plus prompt to display your the current date, the current user and the users privilege level, enter:

To change your SQL*Plus prompt to display a variable you have defined, enter:

SQL*Plus User's Guide and Reference

Library

Product

Contents

Index

This chapter explains how to format your query results to produce a finished report. This chapter covers the following topics:

• changing column headings

• formatting NUMBER, CHAR, VARCHAR2 (VARCHAR), LONG, DATE, and Trusted Oracle columns

• copying, listing, and resetting column display attributes

• suppressing duplicate values and inserting space for clarity

• calculating and printing summary lines (totals, averages, minimums, maximums, and more)

• listing and removing spacing and summary line definitions

• setting page dimensions

• placing titles at the top and bottom of each page

• displaying column values and the current date or page number in your titles

• listing and suppressing page title definitions

• placing headers and footers at the beginning and end of reports

• sending query results to a file or printer

Read this chapter while sitting at your computer and try out the examples shown. Before beginning, make sure you have access to the sample tables described.

Formatting Columns

Through the SQL*Plus COLUMN command, you can change the column headings and reformat the column data in your query results.

Changing Column Headings

When displaying column headings, you can either use the default heading or you can change it using the COLUMN command. The sections below describe how the default headings are derived and how you can alter them with the COLUMN command.

Default Headings

SQL*Plus uses column or expression names as default column headings when displaying query results. Column names are often short and cryptic, however, and expressions can be hard to understand.

Changing Default Headings

You can define a more useful column heading with the HEADING clause of the COLUMN command, in the format shown below:

See the COLUMN command for more details.

Example 4-1 Changing a Column Heading

To produce a report from EMP with new headings specified for DEPTNO, ENAME, and SAL, enter the following commands:

SQL*Plus displays the following output:

Note: The new headings will remain in effect until you enter different headings, reset each column's format, or exit from SQL*Plus.

To change a column heading to two or more words, enclose the new heading in single or double quotation marks when you enter the COLUMN command. To display a column heading on more than one line, use a vertical bar ( ) where you want to begin a new line. (You can use a character other than a vertical bar by changing the setting of the HEADSEP variable of the SET command. See SET for more information.)

Example 4-2 Splitting a Column Heading

To give the column ENAME the heading EMPLOYEE NAME and to split the new heading onto two lines, enter

Now rerun the query with the slash (/) command:

SQL*Plus displays the following output:

To change the character used to underline each column heading, set the UNDERLINE variable of the SET command to the desired character.

Example 4-3 Setting the Underline Character

To change the character used to underline headings to an equal sign and rerun the query, enter the following commands:

SQL*Plus displays the following results:

Now change the underline character back to a dash:

Note: You must enclose the dash in quotation marks; otherwise, SQL*Plus interprets the dash as a hyphen indicating you wish to continue the command on another line.

Formatting NUMBER Columns

When displaying NUMBER columns, you can either accept the SQL*Plus default display width or you can change it using the COLUMN command. The sections below describe the default display and how you can alter the default with the COLUMN command.

Default Display

A NUMBER column's width equals the width of the heading or the width of the FORMAT plus one space for the sign, whichever is greater. If you do not explicitly use FORMAT, then the column's width will always be at least the value of SET NUMWIDTH.

SQL*Plus normally displays numbers with as many digits as are required for accuracy, up to a standard display width determined by the value of the NUMWIDTH variable of the SET command (normally 10). If a number is larger than the value of SET NUMWIDTH, SQL*Plus rounds the number up or down to the maximum number of characters allowed.

You can choose a different format for any NUMBER column by using a format model in a COLUMN command. A format model is a representation of the way you want the numbers in the column to appear, using 9's to represent digits.

Changing the Default Display

The COLUMN command identifies the column you want to format and the model you want to use, as shown below:

Use format models to add commas, dollar signs, angle brackets (around negative values), and/or leading zeros to numbers in a given column. You can also round the values to a given number of decimal places, display minus signs to the right of negative values (instead of to the left), and display values in exponential notation.

To use more than one format model for a single column, combine the desired models in one COLUMN command (see Example 4-4). For a complete list of format models and further details, see the COLUMN command.

Example 4-4 Formatting a NUMBER Column

To display SAL with a dollar sign, a comma, and the numeral zero instead of a blank for any zero values, enter the following command:

Now rerun the current query:

SQL*Plus displays the following output:

Use a zero in your format model, as shown above, when you use other formats such as a dollar sign and wish to display a zero in place of a blank for zero values.

Note: The format model will stay in effect until you enter a new one, reset the column's format, or exit from SQL*Plus.

Formatting CHAR, VARCHAR2 (VARCHAR), LONG, DATE, and Trusted Oracle Columns

When displaying CHAR, VARCHAR2 (VARCHAR), LONG, DATE, and Trusted Oracle columns, you can either accept the SQL*Plus default display width or you can change it using the COLUMN command. The sections below describe the defaults and how you can alter the defaults with the COLUMN command.

Default Display

The default width of CHAR and VARCHAR2 (VARCHAR) columns is the width of the column in the database. (VARCHAR2 requires Oracle7.)

The display width of LONG columns defaults to the value of the LONGCHUNKSIZE variable of the SET command.

For Oracle7, the default width and format of unformatted DATE columns in SQL*Plus is derived from the NLS parameters in effect. Otherwise, the default format width is A9. With Oracle Version 6, the default width for DATE columns is nine characters. For more information on formatting DATE columns, see the FORMAT clause of the COLUMN command.

The default display width for the Trusted Oracle datatypes MLSLABEL and RAW MLSLABEL is the width defined for the column in the database or the width of the column heading, whichever is longer. (Note that the default display width for a Trusted Oracle column named ROWLABEL is 15.)

Note: The default justification for CHAR, VARCHAR2 (VARCHAR), LONG, DATE, and Trusted Oracle columns is left justification.

Changing the Default Display

You can change the displayed width of a CHAR, VARCHAR2 (VARCHAR), LONG, DATE, or Trusted Oracle column by using the COLUMN command with a format model consisting of the letter A (for alphanumeric) followed by a number representing the width of the column in characters.

Within the COLUMN command, identify the column you want to format and the model you want to use:

If you specify a width shorter than the column heading, SQL*Plus truncates the heading. If you specify a width for a LONG column, SQL*Plus uses the LONGCHUNKSIZE or the specified width, whichever is smaller, as the column width. See the COLUMN command for more details.

Example 4-5 Formatting a Character Column

To set the width of the column ENAME to four characters and rerun the current query, enter

SQL*Plus displays the results:

Note: The format model will stay in effect until you enter a new one, reset the column's format, or exit from SQL*Plus. ENAME could be a CHAR or VARCHAR2 (VARCHAR) column.

If the WRAP variable of the SET command is set to ON (its default value), the employee names wrap to the next line after the fourth character, as shown in Example 4-5. If WRAP is set to OFF, the names are truncated (cut off) after the fourth character.

The system variable WRAP controls all columns; you can override the setting of WRAP for a given column through the WRAPPED, WORD_WRAPPED, and TRUNCATED clauses of the COLUMN command. See COLUMN for more information on these clauses. You will use the WORD_WRAPPED clause of COLUMN later in this chapter.

Note: The column heading is truncated regardless of the setting of WRAP or any COLUMN command clauses.

Now return the column to its previous format:

Copying Column Display Attributes

When you want to give more than one column the same display attributes, you can reduce the length of the commands you must enter by using the LIKE clause of the COLUMN command. The LIKE clause tells SQL*Plus to copy the display attributes of a previously defined column to the new column, except for changes made by other clauses in the same command.

Example 4-6 Copying a Column's Display Attributes

To give the column COMM the same display attributes you gave to SAL, but to specify a different heading, enter the following command:

Rerun the query:

SQL*Plus displays the following output:

Listing and Resetting Column Display Attributes

To list the current display attributes for a given column, use the COLUMN command followed by the column name only, as shown below:

To list the current display attributes for all columns, enter the COLUMN command with no column names or clauses after it:

To reset the display attributes for a column to their default values, use the CLEAR clause of the COLUMN command as shown below:

To reset the attributes for all columns, use the COLUMNS clause of the CLEAR command.

Example 4-7 Resetting Column Display Attributes to their Defaults

To reset all columns' display attributes to their default values, enter the following command:

You may wish to place the command CLEAR COLUMNS at the beginning of every command file to ensure that previously entered COLUMN commands will not affect queries you run in a given file.

Suppressing and Restoring Column Display Attributes

You can suppress and restore the display attributes you have given a specific column. To suppress a column's display attributes, enter a COLUMN command in the following form:

The OFF clause tells SQL*Plus to use the default display attributes for the column, but does not remove the attributes you have defined through the COLUMN command. To restore the attributes you defined through COLUMN, use the ON clause:

Printing a Line of Characters after Wrapped Column Values

As you have seen, by default SQL*Plus wraps column values to additional lines when the value does not fit within the column width. If you want to insert a record separator (a line of characters or a blank line) after each wrapped line of output (or after every row), use the RECSEP and RECSEPCHAR variables of the SET command.

RECSEP determines when the line of characters is printed: you set RECSEP to EACH to print after every line, to WRAPPED to print after wrapped lines, and to OFF to suppress printing. The default setting of RECSEP is WRAPPED.

RECSEPCHAR sets the character printed in each line. You can set RECSEPCHAR to any character.

You may wish to wrap whole words to additional lines when a column value wraps to additional lines. To do so, use the WORD_WRAPPED clause of the COLUMN command as shown below:

Example 4-8 Printing a Line of Characters after Wrapped Column Values

To print a line of dashes after each wrapped column value, enter the following commands:

Now restrict the width of the column LOC and tell SQL*Plus to wrap whole words to additional lines when necessary:

Finally, enter and run the following query:

SQL*Plus displays the results:

If you set RECSEP to EACH, SQL*Plus prints a line of characters after every row (after every department, for the above example).

Before continuing, set RECSEP to OFF to suppress the printing of record separators:

Clarifying Your Report with Spacing and Summary Lines

When you use an ORDER BY clause in your SQLSELECT command, rows with the same value in the ordered column (or expression) are displayed together in your output. You can make this output more useful to the user by using the SQL*Plus BREAK and COMPUTE commands to create subsets of records and add space and/or summary lines after each subset.

The column you specify in a BREAK command is called a break column. By including the break column in your ORDER BY clause, you create meaningful subsets of records in your output. You can then add formatting to the subsets within the same BREAK command, and add a summary line (containing totals, averages, and so on) by specifying the break column in a COMPUTE command.

For example, the following query, without BREAK or COMPUTE commands,

produces the following unformatted results:

To make this report more useful, you would use BREAK to establish DEPTNO as the break column. Through BREAK you could suppress duplicate values in DEPTNO and place blank lines or begin a new page between departments. You could use BREAK in conjunction with COMPUTE to calculate and print summary lines containing the total (and/or average, maximum, minimum, standard deviation, variance, or count of rows of) salary for each department and for all departments.

Suppressing Duplicate Values in Break Columns

The BREAK command suppresses duplicate values by default in the column or expression you name. Thus, to suppress the duplicate values in a column specified in an ORDER BY clause, use the BREAK command in its simplest form:

Note: Whenever you specify a column or expression in a BREAK command, use an ORDER BY clause specifying the same column or expression. If you do not do this, the breaks may appear to occur randomly.

Example 4-9 Suppressing Duplicate Values in a Break Column

To suppress the display of duplicate department numbers in the query results shown above, enter the following commands:

SQL*Pus displays the following output:

Inserting Space when a Break Column's Value Changes

You can insert blank lines or begin a new page each time the value changes in the break column. To insert n blank lines, use the BREAK command in the following form:

To skip a page, use the command in this form:

Example 4-10 Inserting Space when a Break Column's Value Changes

To place one blank line between departments, enter the following command:

Now rerun the query:

SQL*Plus displays the results:

Inserting Space after Every Row

You may wish to insert blank lines or a blank page after every row. To skip n lines after every row, use BREAK in the following form:

To skip a page after every row, use

Note: SKIP PAGE does not cause a physical page break unless you have also specified NEWPAGE 0.

Using Multiple Spacing Techniques

Suppose you have more than one column in your ORDER BY clause and wish to insert space when each column's value changes. Each BREAK command you enter replaces the previous one. Thus, if you want to use different spacing techniques in one report or insert space after the value changes in more than one ordered column, you must specify multiple columns and actions in a single BREAK command.

Example 4-11 Combining Spacing Techniques

First, add another column to the current query:

Now, to skip a page when the value of DEPTNO changes and one line when the value of JOB changes, enter the following command:

To show that SKIP PAGE has taken effect, create a TTITLE with a page number, enter

Run the new query to see the results:

Listing and Removing Break Definitions

You can list your current break definition by entering the BREAK command with no clauses:

You can remove the current break definition by entering the CLEAR command with the BREAKS clause:

Sql Set Column Width

You may wish to place the command CLEAR BREAKS at the beginning of every command file to ensure that previously entered BREAK commands will not affect queries you run in a given file.

Computing Summary Lines when a Break Column's Value Changes

If you organize the rows of a report into subsets with the BREAK command, you can perform various computations on the rows in each subset. You do this with the functions of the SQL*Plus COMPUTE command. Use the BREAK and COMPUTE commands together in the following forms:

You can include multiple break columns and actions, such as skipping lines in the BREAK command, as long as the column you name after ON in the COMPUTE command also appears after ON in the BREAK command. To include multiple break columns and actions in BREAK when using it in conjunction with COMPUTE, use these commands in the following forms:

The COMPUTE command has no effect without a corresponding BREAK command.

You can COMPUTE on NUMBER columns and, in certain cases, on all types of columns. See COMPUTE for details.

The following table lists compute functions and their effects:

Function

Effect

SUM

MINIMUM

MAXIMUM

AVG

STD

VARIANCE

COUNT

NUMBER

Table 4 - 1. Compute Functions

The function you specify in the COMPUTE command applies to all columns you enter after OFF and before ON. The computed values print on a separate line when the value of the ordered column changes.

Labels for ON REPORT and ON ROW computations appear in the first column; otherwise, they appear in the column specified in the ON clause.

You can change the compute label by using COMPUTE LABEL. If you do not define a label for the computed value, SQL*Plus prints the unabbreviated function keyword.

The compute label can be suppressed by using the NOPRINT option of the COLUMN command on the break column. See the COMPUTE command for more details.

Example 4-12 Computing and Printing Subtotals

To compute the total of SAL by department, first list the current BREAK definition:

Now enter the following COMPUTE command and run the current query:

SQL*Plus displays the following output:

To compute the sum of salaries for departments 10 and 20 without printing the compute label:

SQL*Plus displays the following output:

To compute the salaries at the end of the report:

SQL*Plus displays the following output:

Note: The format of the column SAL controls the appearance of the sum of SAL, as well as the individual values of SAL. When you establish the format of a NUMBER column, you must allow for the size of sums you will include in your report.

Computing Summary Lines at the End of the Report

You can calculate and print summary lines based on all values in a column by using BREAK and COMPUTE in the following forms:

Example 4-13 Computing and Printing a Grand Total

To calculate and print the grand total of salaries for all salesmen and change the compute label, first enter the following BREAK and COMPUTE commands:

Next, enter and run a new query:

SQL*Plus displays the results:

To print a grand total (or grand average, grand maximum, and so on) in addition to subtotals (or sub-averages, and so on), include a break column and an ON REPORT clause in your BREAK command. Then, enter one COMPUTE command for the break column and another to compute ON REPORT:

Computing Multiple Summary Values and Lines

You can compute and print the same type of summary value on different columns. To do so, enter a separate COMPUTE command for each column.

Example 4-14 Computing the Same Type of Summary Value on Different Columns

To print the total of salaries and commissions for all salesmen, first enter the following COMPUTE command:

You do not have to enter a BREAK command; the BREAK you entered in Example 4-13 is still in effect. Now, add COMM to the current query:

Finally, run the revised query to see the results:

You can also print multiple summary lines on the same break column. To do so, include the function for each summary line in the COMPUTE command as follows:

If you include multiple columns after OFF and before ON, COMPUTE calculates and prints values for each column you specify.

Example 4-15 Computing Multiple Summary Lines on the Same Break Column

To compute the average and sum of salaries for the sales department, first enter the following BREAK and COMPUTE commands:

Now, enter and run the following query:

SQL*Plus displays the results:

Listing and Removing COMPUTE Definitions

You can list your current COMPUTE definitions by entering the COMPUTE command with no clauses:

You can remove all the COMPUTE definitions by entering the CLEAR command with the COMPUTES clause.

Example 4-16 Removing COMPUTE Definitions

To remove all COMPUTE definitions and the accompanying BREAK definition, enter the following commands:

You may wish to place the commands CLEAR BREAKS and CLEAR COMPUTES at the beginning of every command file to ensure that previously entered BREAK and COMPUTE commands will not affect queries you run in a given file.

Defining Page and Report Titles and Dimensions

The word page refers to a screenful of information on your display or a page of a spooled (printed) report. You can place top and bottom titles on each page, set the number of lines per page, and determine the width of each line.

The word report refers to the complete results of a query. You can also place headers and footers on each report and format them in the same way as top and bottom titles on pages.

Setting the Top and Bottom Titles and Headers and Footers

As you have already seen, you can set a title to display at the top of each page of a report. You can also set a title to display at the bottom of each page. The TTITLE command defines the top title; the BTITLE command defines the bottom title.

You can also set a header and footer for each report. The REPHEADER command defines the report header; the REPFOOTER command defines the report footer.

A TTITLE, BTITLE, REPHEADER or REPFOOTER command consists of the command name followed by one or more clauses specifying a position or format and a CHAR value you wish to place in that position or give that format. You can include multiple sets of clauses and CHAR values:

The most often used clauses of TTITLE, BTITLE, REPHEADER and REPFOOTER are summarized in the following table. For descriptions of all TTITLE, BTITLE, REPHEADER and REPFOOTER clauses, see the discussions of TTITLE and REPHEADER.

Clause

Example	Description
COLn	COL 72	Makes the next CHAR value appear in the specified column of the line.
SKIP n	SKIP 2	Skips to a new line n times. If n is greater than 1, n-1 blank lines appear before the next CHAR value.
LEFT	LEFT	Left-aligns the following CHAR value.
CENTER	CENTER	Centers the following CHAR value.
RIGHT	RIGHT	Right-aligns the following CHAR value.
Table 4 - 2. Often-Used Clauses of TTITLE, BTITLE, REPHEADER and REPFOOTER

Example 4-17 Placing a Top and Bottom Title on a Page

To put titles at the top and bottom of each page of a report, enter

Now run the current query:

SQL*Plus displays the following output:

Example 4-18 Placing a Header on a Report

To put a report header on a separate page, and to center it, enter

Now run the current query:

SQL*Plus displays the following output on page one

and the following output on page two

To suppress the report header without changing its definition, enter

Positioning Title Elements

The report in the preceding exercises might look more attractive if you give the company name more emphasis and place the type of report and the department name on either end of a separate line. It may also help to reduce the linesize and thus center the titles more closely around the data.

You can accomplish these changes by adding some clauses to the TTITLE command and by resetting the system variable LINESIZE, as the following example shows.

You can format report headers and footers in the same way as BTITLE and TTITLE using the REPHEADER and REPFOOTER commands.

Example 4-19 Positioning Title Elements

To redisplay the personnel report with a repositioned top title, enter the following commands:

SQL*Plus displays the results:

The LEFT, RIGHT, and CENTER clauses place the following values at the beginning, end, and center of the line. The SKIP clause tells SQL*Plus to move down one or more lines.

Note that there is no longer any space between the last row of the results and the bottom title. The last line of the bottom title prints on the last line of the page. The amount of space between the last row of the report and the bottom title depends on the overall page size, the number of lines occupied by the top title, and the number of rows in a given page. In the above example, the top title occupies three more lines than the top title in the previous example. You will learn to set the number of lines per page later in this chapter.

To always print n blank lines before the bottom title, use the SKIP n clause at the beginning of the BTITLE command. For example, to skip one line before the bottom title in the example above, you could enter the following command:

Indenting a Title Element

You can use the COL clause in TTITLE or BTITLE to indent the title element a specific number of spaces. For example, COL 1 places the following values in the first character position, and so is equivalent to LEFT, or an indent of zero. COL 15 places the title element in the 15th character position, indenting it 14 spaces.

Exercise 4-20 Indenting a Title Element

To print the company name left-aligned with the report name indented five spaces on the next line, enter

Now rerun the current query to see the results:

Entering Long Titles

If you need to enter a title greater than 500 characters in length, you can use the SQL*Plus command DEFINE to place the text of each line of the title in a separate user variable:

Then, reference the variables in your TTITLE or BTITLE command as follows:

Displaying the Page Number and other System-Maintained Values in Titles

You can display the current page number and other system-maintained values in your title by entering a system value name as a title element, for example:

There are five system-maintained values you can display in titles, the most commonly used of which is SQL.PNO (the current page number). Refer to the TTITLE command for a list of system-maintained values you can display in titles.

Example 4-21 Displaying the Current Page Number in a Title

To display the current page number at the top of each page, along with the company name, enter the following command:

Now rerun the current query:

SQL*Plus displays the following results:

Note that SQL.PNO has a format ten spaces wide. You can change this format with the FORMAT clause of TTITLE (or BTITLE).

Example 4-22 Formatting a System-Maintained Value in a Title

To close up the space between the word PAGE: and the page number, re-enter the TTITLE command as shown:

Now rerun the query:

SQL*Plus displays the following results:

Listing, Suppressing, and Restoring Page Title Definitions

To list a page title definition, enter the appropriate title command with no clauses:

To suppress a title definition, enter:

These commands cause SQL*Plus to cease displaying titles on reports, but do not clear the current definitions of the titles. You may restore the current definitions by entering

Displaying Column Values in Titles

You may wish to create a master/detail report that displays a changing master column value at the top of each page with the detail query results for that value below. You can reference a column value in a top title by storing the desired value in a variable and referencing the variable in a TTITLE command. Use the following form of the COLUMN command to define the variable:

You must include the master column in an ORDER BY clause and in a BREAK command using the SKIP PAGE clause.

Example 4-23 Creating a Master/Detail Report

Suppose you want to create a report that displays two different managers' employee numbers, each at the top of a separate page, and the people reporting to the manager on the same page as the manager's employee number. First create a variable, MGRVAR, to hold the value of the current manager's employee number:

Because you will display the managers' employee numbers in the title, you do not want them to print as part of the detail. The NOPRINT clause you entered above tells SQL*Plus not to print the column MGR.

Next, include a label and the value in your page title, enter the proper BREAK command, and suppress the bottom title from the last example:

Finally, enter and run the following query:

SQL*Plus displays the following output:

If you want to print the value of a column at the bottom of the page, you can use the COLUMN command in the following form:

SQL*Plus prints the bottom title as part of the process of breaking to a new page--after finding the new value for the master column. Therefore, if you simply referenced the NEW_VALUE of the master column, you would get the value for the next set of detail. OLD_VALUE remembers the value of the master column that was in effect before the page break began.

Displaying the Current Date in Titles

You can, of course, date your reports by simply typing a value in the title. This is satisfactory for ad hoc reports, but if you want to run the same report repeatedly, you would probably prefer to have the date automatically appear when the report is run. You can do this by creating a variable to hold the current date.

To create the variable (in this example named _DATE), you can add the following commands to your SQL*Plus LOGIN file:

When you start SQL*Plus, these commands place the value of SYSDATE (the current date) into a variable named _DATE. To display the current date, you can reference _DATE in a title as you would any other variable.

The date format model you include in the SELECT command in your LOGIN file determines the format in which SQL*Plus displays the date. See your Oracle7 Server SQL Language Reference Manual for more information on date format models. For more information about the LOGIN file, see 'Modifying Your LOGIN File'.

You can also enter these commands interactively at the command prompt; see COLUMN for an example.

Setting Page Dimensions

Typically, a page of a report contains the number of blank line(s) set in the NEWPAGE variable of the SET command, a top title, column headings, your query results, and a bottom title. SQL*Plus displays a report that is too long to fit on one page on several consecutive pages, each with its own titles and column headings. The amount of data SQL*Plus displays on each page depends on the current page dimensions.

The default page dimensions used by SQL*Plus are shown below:

• number of lines before the top title: 1

• number of lines per page, from the top title to the bottom of the page: 24

• number of characters per line: 80

You can change these settings to match the size of your computer screen or, for printing, the size of a sheet of paper.

You can change the page length with the system variable PAGESIZE. For example, you may wish to do so when you print a report, since printed pages are customarily 66 lines long.

To set the number of lines between the beginning of each page and the top title, use the NEWPAGE variable of the SET command:

If you set NEWPAGE to zero, SQL*Plus skips zero lines and displays and prints a formfeed character to begin a new page. On most types of computer screens, the formfeed character clears the screen and moves the cursor to the beginning of the first line. When you print a report, the formfeed character makes the printer move to the top of a new sheet of paper, even if the overall page length is less than that of the paper.

Where does google drive download to. To set the number of lines on a page, use the PAGESIZE variable of the SET command:

You may wish to reduce the linesize to center a title properly over your output, or you may want to increase linesize for printing on wide paper. You can change the line width using the LINESIZE variable of the SET command:

Example 4-24 Setting Page Dimensions

To set the page size to 66 lines, clear the screen (or advance the printer to a new sheet of paper) at the start of each page, and set the linesize to 32, enter the following commands:

Now enter and run the following commands to see the results:

SQL*Plus displays a formfeed followed by the query results:

Now reset PAGESIZE, NEWPAGE, and LINESIZE to their default values:

To list the current values of these variables, use the SHOW command:

Through the SQL*Plus command SPOOL, you can store you query results in a file or print them on your computer's default printer.

Sending Results to a File

To store the results of a query in a file--and still display them on the screen--enter the SPOOL command in the following form:

SQL*Plus stores all information displayed on the screen after you enter the SPOOL command in the file you specify.

Storing and Printing Query Results

Send your query results to a file when you want to edit them with a word processor before printing or include them in a letter, memo, or other document.

To store the results of a query in a file--and still display them on the screen--enter the SPOOL command in the following form:

If you do not follow the filename with a period and an extension, SPOOL adds a default file extension to the filename to identify it as an output file. The default varies with the host operating system; on most hosts it is LST or LIS. See the Oracle installation and user's manual(s) provided for your operating system for more information.

SQL*Plus continues to spool information to the file until you turn spooling off, using the following form of SPOOL:

Creating a Flat File

When moving data between different software products, it is sometimes necessary to use a 'flat' file (an operating system file with no escape characters, headings, or extra characters embedded). For example, if you do not have SQL*Net, you need to create a flat file for use with SQL*Loader when moving data from Oracle Version 6 to Oracle7.

To create a flat file with SQL*Plus, you first must enter the following SET commands:

After entering these commands, you use the SPOOL command as shown in the previous section to create the flat file.

The SET COLSEP command may be useful to delineate the columns. For more information, see the SET command.

Sending Results to a Printer

To print query results, spool them to a file as described in the previous section. Then, instead of using SPOOL OFF, enter the command in the following form:

SQL*Plus stops spooling and copies the contents of the spooled file to your host computer's standard (default) printer. SPOOL OUT does not delete the spool file after printing.

Example 4-25 Sending Query Results to a Printer

To generate a final report and spool and print the results, create a command filenamed EMPRPT containing the following commands.

First, use EDIT to create the command file with your host operating system text editor. (Do not use INPUT and SAVE, or SQL*Plus will add a slash to the end of the file and will run the command file twice--once as a result of the semicolon and once due to the slash.)

Next, enter the following commands into the file, using your text editor:

If you do not want to see the output on your screen, you can also add SET TERMOUT OFF to the beginning of the file and SET TERMOUT ON to the end of the file. Save the file (you automatically return to SQL*Plus). Now, run the command file EMPRPT:

SQL*Plus displays the output on your screen (unless you set TERMOUT to OFF), spools it to the file TEMP, and sends the contents of TEMP to your default printer:

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index