I’m new to Toad and the script output is set at 80 columns so there’s all kinds of wrapping. Need to widen it. I changed the “Visible right margin” but apparently that applies to the editor, not script output.

1. Toad Set Column Width In Excel

This chapter explains how to format your query results to produce a finished report. This chapter does not discuss HTML output, but covers the following topics:

• TOAD allows you to change the data that appears in the Data tab of the results panel. The data columns being selected must include the ROWID column as well. Another way to select data from an object for update is by using edit syntax.
• Dec 05, 2017  Using Toad Edge 1.1 with MySQL Database – New Database & New Table Wizards,Text Search, Import Data Wizard. Which was called active database in Toad Edge 1.0. Only one database may be set as the default database. To set test as the default database right-click on test and select Set Database as Default. Increase the column width for the.

Read this chapter while sitting at your computer and try out the examples shown. Before beginning, make sure you have access to the HR sample schema described in SQL*Plus Quick Start.

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 following sections describe how default headings are derived and how to alter them using the COLUMN command. See the COLUMN command for more details.

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 following format:

Example 6-1 Changing a Column Heading

To produce a report from EMP_DETAILS_VIEW with new headings specified for LAST_NAME, SALARY, and COMMISSION_PCT, enter the following commands:

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. Later sections describe the default display and how you can alter it with the COLUMN command. The format model will stay in effect until you enter a new one, reset the column's format with

or exit from SQL*Plus.

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 if possible, or displays hashes if the number is too large.

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 9s 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:

Use format models to add commas, dollar signs, angle brackets (around negative values), and 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 6-4). See COLUMN for a complete list of format models and further details.

Example 6-4 Formatting a NUMBER Column

To display SALARY 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:

Formatting Datatypes

When displaying datatypes, you can either accept the SQL*Plus default display width or you can change it using the COLUMN command. The format model will stay in effect until you enter a new one, reset the column's format with

or exit from SQL*Plus. Datatypes, in this manual, include the following types:

• CHAR

• NCHAR

• VARCHAR2 (VARCHAR)

• NVARCHAR2 (NCHAR VARYING)

• DATE

• LONG

• CLOB

• NCLOB

• XMLType

Default Display

The default width of datatype columns is the width of the column in the database. The column width of a LONG, CLOB, NCLOB or XMLType defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever is the smaller.

The default width and format of unformatted DATE columns in SQL*Plus is determined by the database NLS_DATE_FORMAT parameter. Otherwise, the default format width is A9. See the FORMAT clause of the COLUMN command for more information on formatting DATE columns.

Left justification is the default for datatypes.

Changing the Default Display

You can change the displayed width of a datatype or DATE, 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. See the COLUMN command for more details.

Example 6-5 Formatting a Character Column

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

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 6-7 Copying a Column's Display Attributes

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

Rerun the query:

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:

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:

Example 6-8 Resetting Column Display Attributes to their Defaults

To reset all column display attributes to their default values, enter:

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:

OFF 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 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:

Example 6-9 Printing a Line of Characters after Wrapped Column Values

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

Finally, enter the following query:

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

Run the query:

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 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.

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 6-11 Inserting Space when a Break Column's Value Changes

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

Now rerun the query:

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 character to be generated 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 6-12 Combining Spacing Techniques

Type the following:

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

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

Run the new query to see the results:

Listing and Removing Break Definitions

Before continuing, turn off the top title display without changing its definition:

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:

You may wish to place the command CLEAR BREAKS at the beginning of every script 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. For more information see the COMPUTE command.

The following table lists compute functions and their effects

Table 6-1 Compute Functions

Function..	Computes the..
Sum of the values in the column.
Minimum value in the column.
Maximum value in the column.
Average of the values in the column.
Standard deviation of the values in the column.
Variance of the values in the column.
Number of non-null values in the column.
Number of rows in the column.

The function you specify in the COMPUTE command applies to all columns you enter after OF 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. If you use the NOPRINT option for the column on which the COMPUTE is being performed, the COMPUTE result is also suppressed.

Example 6-13 Computing and Printing Subtotals

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

which displays current BREAK definitions:

To compute the salaries just at the end of the 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 6-14 Computing and Printing a Grand Total

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

Next, enter and run a new query:

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 6-15 Computing the Same Type of Summary Value on Different Columns

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

You do not have to enter a BREAK command; the BREAK you entered in Example 6-14, 'Computing and Printing a Grand Total' is still in effect. Now, change the first line of the select query to include COMMISSION_PCT:

Listing and Removing COMPUTE Definitions

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

Example 6-17 Removing COMPUTE Definitions

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

Defining Page and Report Titles and Dimensions

The word page refers to a screen full 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:

For descriptions of all TTITLE, BTITLE, REPHEADER and REPFOOTER clauses, see the TTITLE command and the REPHEADER command.

Example 6-18 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:

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.

Example 6-21 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 substitution variable:

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

Displaying 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). See TTITLE for a list of system-maintained values you can display in titles.

Example 6-22 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:

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 underneath. 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 6-24 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 only 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 MANAGER_ID.

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:

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.

You can reference the predefined substitution variable _DATE to display the current date in a title as you would any other variable.

The date format model you include in your LOGIN file or in your SELECT statement determines the format in which SQL*Plus displays the date. See your Oracle Database SQL Reference for more information on date format models. See Modifying Your LOGIN File for more information about the LOGIN file.

You can also enter these commands interactively. See COLUMN for more information.

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 underneath:

• number of lines before the top title: 1

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

• 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.

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. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between report pages.

To set the number of lines on a page, use the PAGESIZE variable of the SET command:

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

Example 6-25 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 line size to 70, enter the following commands:

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

Now run the following query:

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, email, 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 operating system; on most hosts it is LST or LIS. The extension is not appended when you spool to system generated files such as /dev/null and /dev/stderr. See the platform-specific Oracle documentation 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 Oracle Net, you need to create a flat file for use with SQL*Loader when moving data from Oracle9i to Oracle Database 10g.

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 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.

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 computer's standard (default) printer. SPOOL OUT does not delete the spool file after printing.

Example 6-26 Sending Query Results to a Printer

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

First, use EDIT to create the script with your operating system text editor.

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 and close the file in your text editor (you will automatically return to SQL*Plus). Now, run the script EMPRPT:

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

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, DELETE or MERGE).
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.
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.
Sets the default filename for the EDIT command.
Controls where on a page each report begins.
Enables recording of SQL, PL/SQL and SQL*Plus errors to an error log table which you can query later.
Defines the character you enter as the escape character.
Specifies a special character to escape in a filename. Prevents character translation causing an error.
Specifies whether the default EXIT behavior is COMMIT or ROLLBACK.
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.
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 BLOB, BFILE, 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, BLOB, BFILE, 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, BLOB, BFILE, CLOB, NCLOB or XMLType value.
Outputs HTML marked up text.
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.
Enables correct alignment for terminals that display shift characters.
Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET.
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.
Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).
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].
Sets the SQL*Plus prefix character.
Sets the SQL*Plus command prompt.
Sets the character used to end and execute SQL commands to c.
Sets the default file that SQL*Plus uses in commands that refer to scripts.
Determines how SQL*Plus formats white space in terminal output.
Controls the display of output generated by commands executed from a script.
Controls the display of the current time.
Controls the display of timing statistics.
Determines whether SQL*Plus puts trailing blanks at the end of each displayed line.
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.
Specifies that only fully optimized XML queries and DML operations are executed. Only to assist in developing and debugging, not for production.
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.

SET APPI[NFO]{ON OFFtext}

Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.

This enables the performance and resource usage of each script to be monitored by your DBA. The registered name appears in the MODULE column of the V$SESSION and V$SQLAREA virtual tables. You can also read the registered name using the DBMS_APPLICATION_INFO.READ_MODULE procedure.

ON registers scripts invoked by the @, @@ or START commands. OFF disables registering of scripts. Instead, the current value of text is registered. text specifies the text to register when no script is being run or when APPINFO is OFF, which is the default. The default for text is 'SQL*Plus'. If you enter multiple words for text, you must enclose them in quotes. The maximum length for text is limited by the DBMS_APPLICATION_INFO package.

The registered name has the format nn@xfilename where: nn is the depth level of script; x is '<' when the script name is truncated, otherwise, it is blank; and filename is the script name, possibly truncated to the length allowed by the DBMS_APPLICATION_INFO package interface.

For more information on the DBMS_APPLICATION_INFO package, see the Oracle Database Performance Tuning Guide.

Example

To display the value of APPINFO, as it is SET OFF by default, enter

To change the default text, enter

To make sure that registration has taken place, enter

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.

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 (such as SELECT, INSERT, UPDATE, DELETE or MERGE).

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

SQL*Plus report output may differ for DML if dynamic sampling is in effect.

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 Language 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.

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 COLSEP { text}

Sets the column separator character printed between columns in output.

If the COLSEP variable contains blanks or punctuation characters, you must enclose it with single quotes. The default value for text is a single space.

In multi-line rows, the column separator does not print between columns that begin on different lines. The column separator does not appear on blank lines produced by BREAK .. SKIP n and does not overwrite the record separator. See SET RECSEP {WR[APPED] EA[CH] OFF} for more information.

Example

To set the column separator to ' ' enter

SET CON[CAT] {. cON OFF}

Sets the character used to terminate a substitution variable reference when SQL*Plus would otherwise interpret the next character as a part of the variable name.

SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.

SET COPYC[OMMIT] {0n}

Controls the number of rows after which the COPY command commits changes to the database.

COPY commits rows to the destination database each time it copies n row batches. Valid values are zero to 5000. You can set the size of a batch with the ARRAYSIZE variable. If you set COPYCOMMIT to zero, COPY performs a commit only at the end of a copy operation.

SET COPYTYPECHECK {ON OFF}

Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.

This is to facilitate copying to DB2, which requires that a CHAR be copied to a DB2 DATE.

SET DEF[INE] {&cON OFF}

Sets the character used to prefix substitution variables to c.

ON or OFF controls whether SQL*Plus will scan commands for substitution variables and replace them with their values. ON changes the value of c back to the default '&', not the most recently used character. The setting of DEFINE to OFF overrides the setting of the SCAN variable.

See SET SCAN {ON OFF} (obsolete) for more information on the SCAN variable.

SET DESCRIBE [DEPTH {1n ALL}] [LINENUM {ON OFF}] [INDENT {ON OFF}]

Sets the depth of the level to which you can recursively describe an object.

The valid range of the DEPTH clause is from 1 to 50. If you SET DESCRIBE DEPTH ALL, then the depth will be set to 50, which is the maximum level allowed. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. Use the SET LINESIZE command to control the width of the data displayed.

See DESCRIBE for more information about describing objects.

Example

To create an object type ADDRESS, enter

To create the table EMPLOYEE that contains a nested object, EMPADDR, of type ADDRESS, enter

To describe the table EMPLOYEE to a depth of two levels, and to indent the output and display line numbers, 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]

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 ERRORL[OGGING] {ON OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]

Turns SQL*Plus error logging ON or OFF. Error logging records SQL, PL/SQL and SQL*Plus errors and associated parameters in an error log table. You can then query the log table to review errors resulting from a query. When error logging is ON, errors are recorded whether the query is run interactively or from a script. This is particularly useful for capturing errors generated from long running queries and avoids capturing all output using the SPOOL command, or having to remain present during the run.

By default, errors are written to a the table SPERRORLOG in your schema. If this table does not exist, it is created automatically. You can also use the TABLE schema.tablename option to specify other tables to use. When using a table other than SPERRORLOG, it must already exist, and you must have access to it. See Creating a User Defined Error Log Table.

If an internal error occurs, to avoid recursion errors caused by the errorlog calling itself, errorlogging is automatically set OFF.

Error logging is set OFF by default.

ON

Writes ORA, PLS and SP2 errors to the default table, SPERRORLOG.

OFF

Disables error .

TABLE [schema.]tablename

Specifies a user defined table to use instead of the default, SPERRORLOG. If you omit schema. the table is created in the current schema. The table you specify must exist, and you must have access permissions.

If the table specified does not exist, or you do not have access, an error message is displayed and the default table, SPERRORLOG, is used.

TRUNCATE

Clears all existing rows in the error log table and begins recording errors from the current session.

IDENTIFIER identifier

A user defined string to identify errors. You can use it to identify errors from a particular session or from a particular version of a query.

Creating a User Defined Error Log Table

You can create one or more error log tables to use other than the default. Before specifying a user defined error log table with the TABLE schema.tablename option, you must create it and ensure that you have permissions to access it. The error log table has the following column definitions:

Table 12-4 SQL*Plus Error Log Column Definitions

Column	Type	Description
username	VARCHAR(256)	Oracle account name.
timestamp	TIMESTAMP	Time when the error occurred.
script	VARCHAR(1024)	Name of the originating script if applicable.
identifier	VARCHAR(256)	User defined identifier string.
message	CLOB	ORA, PLA or SP2 error message. No feed back messages are included. For example, 'PL/SQL Block Created' is not recorded.
statement	CLOB	The statement causing the error.

Using User Defined Error Log Tables

To use a user defined log table, you must have permission to access the table, and you must issue the SET ERRORLOGGING command with the TABLE schema.tablename option to identify the error log table and the schema if applicable.

Querying Error Log Tables

To view the records recorded in an error log table, you query the columns you want to view as for any table. The columns available are shown in Table 12-4, 'SQL*Plus Error Log Column Definitions'.

Example

To use the default error log table to record query errors from a script, myfile.sql, which contains the following:

Enter the following:

which produces the following output:

To view the errror log written to the default error log table, SPERRORLOG, enter:

which produces the following output:

TIMESTAMP	USERNAME	SCRIPT	IDENTIFIER	STATEMENT	MESSAGE
Mon May 08 21:30:03 2006	SYSTEM	d:myfile.sql	open :u for select * from dept;	ORA-06550: line 2, column 27:
Mon May 08 21:30:05 2006	SYSTEM	d:myfile.sql	open :u for select * from dept;	PL/SQL: ORA-00942: table or view does not exist
Mon May 08 21:30:05 2006	SYSTEM	d:myfile.sql	open :u for select * from dept;	ORA-06550: line 2, column 13:
Mon May 08 21:30:05 2006	SYSTEM	d:myfile.sql	open :u for select * from dept;	PL/SQL: SQL Statement ignored
Mon May 08 21:30:06 2006	SYSTEM	d:myfile.sql	show errors procedure 'sss'	ORA-00907: missing right parenthesis
Mon May 08 21:30:09 2006	SYSTEM	d:myfile.sql	set garbage	SP2-0158: unknown SET option 'garbage'
Mon May 08 21:30:10 2006	SYSTEM	d:myfile.sql	garbage	ORA-00942: table or view does not exist

Example 2

To use a user defined error log table to record query errors from a script, myfile.sql, which contains the following:

Enter the following:

which produces the following output:

To view the errror log written to the default error log table, SPERRORLOG, enter:

which produces the following output:

TIMESTAMP	USERNAME	SCRIPT	IDENTIFIER	STATEMENT	MESSAGE
Mon May 08 21:30:03 2006	SYSTEM	d:myfile.sql	open :u for select * from dept;	ORA-06550: line 2, column 27:
Mon May 08 21:30:05 2006	SYSTEM	d:myfile.sql	open :u for select * from dept;	PL/SQL: ORA-00942: table or view does not exist
Mon May 08 21:30:05 2006	SYSTEM	d:myfile.sql	open :u for select * from dept;	ORA-06550: line 2, column 13:
Mon May 08 21:30:05 2006	SYSTEM	d:myfile.sql	open :u for select * from dept;	PL/SQL: SQL Statement ignored
Mon May 08 21:30:06 2006	SYSTEM	d:myfile.sql	show errors procedure 'sss'	ORA-00907: missing right parenthesis
Mon May 08 21:30:09 2006	SYSTEM	d:myfile.sql	set garbage	SP2-0158: unknown SET option 'garbage'
Mon May 08 21:30:10 2006	SYSTEM	d:myfile.sql	garbage	ORA-00942: table or view does not exist

Example 3

To use an error log table other than the default:

• Create the error log table you want to use

• Specify the table you want to use with the TABLE option of the SET ERRORLOGGING ON command.

The error log table must have the column definitions defined in Table 12-4, 'SQL*Plus Error Log Column Definitions'.

John wants to use an error log table named john_sperrorlog. John would run the following SQL statements to create the new error log table:

John then issues the following SET command to enable error logging using the newly created error log table

All error logging for John is now recorded to john_sperrorlog, and not to the default error log table, SPERRORLOG.

Access privileges for the error log table are handled in the same way as for any user table. See the Oracle Database SQL Language Reference for more information about granting access to tables.

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 escape character back to the default value of (backslash), enter

SET ESCCHAR {@ ? % $ OFF}

Specifies a character to be escaped and not interpreted when used in a file name for the SPOOL, START, @, RUN and EDIT commands. These special characters are translated to the following:

While it is not recommended that these characters are used in filenames, if you have legacy files that do use them, it might be useful to include a SET ESCCHAR command in your GLogin file to implement it across your site.

Example

If you include the character '$' in your filename, then

behaves normally.

SET EXITC[OMMIT] {ON OFF}

Specifies whether the default EXIT behavior is COMMIT or ROLLBACK.

The default setting is ON, which means that work is committed on exit, whether you expected it to be committed or not. Set EXITCOMMIT OFF to rollback work on exit.

Table 12-5 shows the exit action associated with combinations of SET commands (AUTOCOMMIT & EXITCOMMIT) and the EXIT command.

Table 12-5 Exit Behavior: AUTOCOMMIT, EXITCOMMIT, EXIT

AUTOCOMMIT	EXITCOMMIT	EXIT	Exit Behavior
ON	ON	-	COMMIT
ON	OFF	-	COMMIT
OFF	ON	-	COMMIT
OFF	OFF	-	ROLLBACK
ON	ON	COMMIT	COMMIT
ON	ON	ROLLBACK	COMMIT
ON	OFF	COMMIT	COMMIT
ON	OFF	ROLLBACK	COMMIT
OFF	ON	COMMIT	COMMIT
OFF	ON	ROLLBACK	ROLLBACK
OFF	OFF	COMMIT	COMMIT
OFF	OFF	ROLLBACK	ROLLBACK

SET FEED[BACK] {6nON OFF}

Displays the number of records returned by a script when a script selects at least n records.

ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.

SET FEEDBACK OFF also turns off the statement confirmation messages such as 'Table created' and 'PL/SQL procedure successfully completed' that are displayed after successful SQL or PL/SQL statements.

SET FLAGGER {OFF ENTRY INTERMED[IATE] FULL}

Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.

If any non-standard constructs are found, the Oracle Database Server flags them as errors and displays the violating syntax. This is the equivalent of the SQL language ALTER SESSION SET FLAGGER command.

You may execute SET FLAGGER even if you are not connected to a database. FIPS flagging will remain in effect across SQL*Plus sessions until a SET FLAGGER OFF (or ALTER SESSION SET FLAGGER = OFF) command is successful or you exit SQL*Plus.

When FIPS flagging is enabled, SQL*Plus displays a warning for the CONNECT, DISCONNECT, and ALTER SESSION SET FLAGGER commands, even if they are successful.

SET FLU[SH] {ON OFF}

Controls when output is sent to the user's display device. OFF enables the operating system to buffer output. ON disables buffering. FLUSH only affects display output, it does not affect spooled output.

Use OFF only when you run a script non-interactively (that is, when you do not need to see output and/or prompts until the script finishes running). The use of FLUSH OFF may improve performance by reducing the amount of program I/O.

SET HEA[DING] {ON OFF}

Controls printing of column headings in reports.

ON prints column headings in reports; OFF suppresses column headings.

The SET HEADING OFF command does not affect the column width displayed, it only suppresses the printing of the column header itself.

Example

To suppress the display of column headings in a report, enter

If you then run a SQL SELECT command

the following output results:

To turn the display of column headings back on, enter

SET HEADS[EP] { cON OFF}

Defines the character used as a line break in column headings.

The heading separator character cannot be alphanumeric or white space. You can use the heading separator character in the COLUMN command and in the old forms of BTITLE and TTITLE to divide a column heading or title onto more than one line. ON or OFF turns heading separation on or off. When heading separation is OFF, SQL*Plus prints a heading separator character like any other character. ON changes the value of c back to the default ' '.

SET INSTANCE [instance_pathLOCAL]

Changes the default instance for your session to the specified instance path.

Using the SET INSTANCE command does not connect to a database. The default instance is used for commands when no instance is specified. Any commands preceding the first use of SET INSTANCE communicate with the default instance.

To reset the instance to the default value for your operating system, you can either enter SET INSTANCE with no instance_path or SET INSTANCE LOCAL.

Note, you can only change the instance when you are not currently connected to any instance. That is, you must first make sure that you have disconnected from the current instance, then set or change the instance, and reconnect to an instance in order for the new setting to be enabled.

This command may only be issued when Oracle Net is running. You can use any valid Oracle Net connect identifier as the specified instance path. See your operating system-specific Oracle Database documentation for a complete description of how your operating system specifies Oracle Net connect identifiers. The maximum length of the instance path is 64 characters.

Example

To set the default instance to 'PROD1' enter

To set the instance back to the default of local, enter

You must disconnect from any connected instances to change the instance.

SET LIN[ESIZE] {80n}

Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.

It also controls the position of centered and right-aligned text in TTITLE, BTITLE, REPHEADER and REPFOOTER. Changing the linesize setting can affect text wrapping in output from the DESCRIBE command. DESCRIBE output columns are typically allocated a proportion of the linesize. Decreasing or increasing the linesize may give unexpected text wrapping in your display. You can define LINESIZE as a value from 1 to a maximum that is system dependent.

SET LOBOF[FSET] {1n}

Sets the starting position from which BLOB, BFILE, CLOB and NCLOB data is retrieved and displayed.

Example

To set the starting position from which a CLOB column's data is retrieved to the 22nd position, enter

The CLOB data will wrap on your screen; SQL*Plus will not truncate until the 23rd character.

SET LOGSOURCE [pathname]

Specifies the location from which archive logs are retrieved during recovery.

The default value is set by the LOG_ARCHIVE_DEST initialization parameter in the Oracle Database initialization file, init.ora. Using the SET LOGSOURCE command without a pathname restores the default location.

Example

To set the default location of log files for recovery to the directory '/usr/oracle10/dbs/arch' enter

SET LONG {80n}

Sets maximum width (in bytes) for displaying BLOB, BFILE, CLOB, LONG, NCLOB and XMLType values; and for copying LONG values.

Querying LONG columns requires enough local memory to store the amount of data specified by SET LONG, irrespective of the value of the SET LONGCHUNKSIZE command. This requirement does not apply when querying LOBs.

It is recommended that you do not create tables with LONG columns. LONG columns are supported only for backward compatibility. Use LOB columns (BLOB, BFILE, CLOB, NCLOB) instead. LOB columns have fewer restrictions than LONG columns and are still being enhanced.

The maximum value of n is 2,000,000,000 bytes. It is important to check that the memory required by your SET LONG command is available on your machine, for example:

assumes that available RAM (random access memory) on the machine exceeds 2 gigabytes.

Example

To set the maximum number of bytes to fetch for displaying and copying LONG values, to 500, enter

The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st byte. The default for LONG is 80 bytes.

SET LONGC[HUNKSIZE] {80n}

Sets the size (in bytes) of the increments SQL*Plus uses to retrieve a BLOB, BFILE, CLOB, LONG, NCLOB or XMLType value.

LONGCHUNKSIZE is not used for object relational queries such as CLOB, or NCLOB.

Example

To set the size of the increments in which SQL*Plus retrieves LONG values to 100 bytes, enter

The LONG data will be retrieved in increments of 100 bytes until the entire value is retrieved or the value of SET LONG is reached, whichever is the smaller.

SET MARK[UP] HTML [ON OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON OFF}] [SPOOL {ON OFF}] [PRE[FORMAT] {ON OFF}]

Outputs HTML marked up text.

To be effective, SET MARKUP commands that change values in dynamic report output must occur before statements that produce query output. The first statement that produces query output triggers the output of information affected by SET MARKUP such as HEAD and TABLE settings. Subsequent SET MARKUP commands have no effect on the information already sent to the report.

SET MARKUP only specifies that SQL*Plus output will be HTML encoded. You must use SET MARKUP HTML ON SPOOL ON and the SQL*Plus SPOOL command to create and name a spool file, and to begin writing HMTL output to it. SET MARKUP has the same options and behavior as SQLPLUS -MARKUP.

See MARKUP Options for detailed information. For examples of usage, see SET MARK[UP] HTML [ON OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON OFF}] [SPOOL {ON OFF}] [PRE[FORMAT] {ON OFF}], and Generating HTML Reports from SQL*Plus.

Use the SHOW MARKUP command to view the status of MARKUP options.

Example

The following is a script which uses the SET MARKUP HTML command to enable HTML marked up text to be spooled to a specified file:

Note:

The SET MARKUP example command is laid out for readability using line continuation characters '–' and spacing. Command options are concatenated in normal entry.

Use your favorite text editor to enter the commands necessary to set up the HTML options and the query you want for your report.

As this script contains SQL*Plus commands, do not attempt to run it with / (slash) from the buffer because it will fail. Save the script in your text editor and use START to execute it:

As well as writing the HTML spool file, employee.htm, the output is also displayed on screen because SET TERMOUT defaults to ON. You can view the spool file, employee.htm, in your web browser. It should appear something like the following:

Description of the illustration markup.gif

SET NEWP[AGE] {1n NONE}

Sets the number of blank lines to be printed from the top of each page to the top title. A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.

SET NULL text

Sets the text displayed whenever a null value occurs in the result of a SQL SELECT command.

Use the NULL clause of the COLUMN command to override the setting of the NULL variable for a given column. The default output for a null is blank (').

SET NUMF[ORMAT] format

Sets the default format for displaying numbers. See the FORMAT clause of the COLUMN command for number format descriptions. Enter a number format for format. To use the default field width and formatting model specified by SET NUMWIDTH, enter

SET NUM[WIDTH] {10n}

Sets the default width for displaying numbers. See the FORMAT clause of the COLUMN command for number format descriptions.

COLUMN FORMAT settings take precedence over SET NUMFORMAT settings, which take precedence over SET NUMWIDTH settings.

SET PAGES[IZE] {14n}

Sets the number of lines on each page of output. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

SET PAU[SE] {ON OFFtext}

Enables you to control scrolling of your terminal when running reports. You need to first, SET PAUSE text, and then SET PAUSE ON if you want text to appear each time SQL*Plus pauses.

SET PAUSE ON pauses output at the beginning of each PAGESIZE number of lines of report output. Press Return to view more output. SET PAUSE text specifies the text to be displayed each time SQL*Plus pauses. Multiple words in text must be enclosed in single quotes.

You can embed terminal-dependent escape sequences in the PAUSE command. These sequences allow you to create inverse video messages or other effects on terminals that support such characteristics.

SET RECSEP {WR[APPED] EA[CH] OFF}

RECSEP tells SQL*Plus where to make the record separation.

For example, if you set RECSEP to WRAPPED, SQL*Plus prints a record separator only after wrapped lines. If you set RECSEP to EACH, SQL*Plus prints a record separator following every row. If you set RECSEP to OFF, SQL*Plus does not print a record separator.

SET RECSEPCHAR { c}

Defines the character to display or print to separate records.

A record separator consists of a single line of the RECSEPCHAR (record separating character) repeated LINESIZE times. The default is a single space.

SET SERVEROUT[PUT] {ON OFF} [SIZE {nUNL[IMITED]}] [FOR[MAT] {WRA[PPED] WOR[D_WRAPPED] TRU[NCATED]}]

Controls whether to display output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus. The DBMS_OUTPUT line length limit is 32767 bytes.

OFF suppresses the output of DBMS_OUTPUT.PUT_LINE. ON displays the output.

ON uses the SIZE and FORMAT of the previous SET SERVEROUTPUT ON SIZE n FORMAT f, or uses default values if no SET SERVEROUTPUT command was previously issued in the current connection.

SIZE sets the number of bytes of the output that can be buffered within the Oracle Database server. The default is UNLIMITED. n cannot be less than 2000 or greater than 1,000,000.

Resources are not pre-allocated when SERVEROUTPUT is set. As there is no performance penalty, use UNLIMITED unless you want to conserve physical memory.

Every server output line begins on a new output line.

When WRAPPED is enabled SQL*Plus wraps the server output within the line size specified by SET LINESIZE, beginning new lines when required.

When WORD_WRAPPED is enabled, each line of server output is wrapped within the line size specified by SET LINESIZE. Lines are broken on word boundaries. SQL*Plus left justifies each line, skipping all leading whitespace.

When TRUNCATED is enabled, each line of server output is truncated to the line size specified by SET LINESIZE.

For detailed information about using UTL_FILE and associated utilities, see the Oracle Database PL/SQL Packages and Types Reference .

For more information on DBMS_OUTPUT.PUT_LINE, see your Oracle Database Advanced Application Developer's Guide.

Example

To enable text display in a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter

The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:

The following example shows what happens when you create a trigger with SET SERVEROUTPUT ON:

To set the output to WORD_WRAPPED, enter

To set the output to TRUNCATED, enter

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

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

where ':' = visible shift character uppercase represents multibyte characters

lowercase represents singlebyte 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. ON lists the settings; OFF suppresses the listing. SHOWMODE ON has the same behavior as the obsolete SHOWMODE BOTH.

SET SQLBL[ANKLINES] {ON OFF}

Controls whether SQL*Plus puts blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.

Toad Set Column Width In Excel

Enter the BLOCKTERMINATOR to stop SQL command entry without running the SQL command. Enter the SQLTERMINATOR character to stop SQL command entry and run the SQL statement.

Example

To allow blank lines in a SQL statement, enter

The following output results:

SET SQLC[ASE] {MIX[ED] LO[WER] UP[PER]}

Converts the case of SQL commands and PL/SQL blocks just prior to execution.

SQL*Plus converts all text within the command, including quoted literals and identifiers, to uppercase if SQLCASE equals UPPER, to lowercase if SQLCASE equals LOWER, and makes no changes if SQLCASE equals MIXED.

SQLCASE does not change the SQL buffer itself.

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 (–).

Example

To set the SQL*Plus command continuation prompt to an exclamation point followed by a space, enter

SQL*Plus will prompt for continuation as follows:

The default continuation prompt is '> '.

SET SQLN[UMBER] {ON OFF}

Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block. ON sets the prompt to be the line number. OFF sets the prompt to the value of SQLPROMPT.

SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}

Sets the behavior to that of the release or version specified by x.y[.z].

Where x is the version number, y is the release number, and z is the update number. For example, 8.1.7, 9.0.1 or 10.2. The features affected by SQLPLUSCOMPATIBILITY are tabulated in the SQL*Plus Compatibility Matrix shown. You can also set the value of SQLPLUSCOMPATIBILITY using the -C[OMPATIBILITY] argument of the SQLPLUS command when starting SQL*Plus from the command line.

The default setting for SQLPLUSCOMPATIBILITY is the value of the SQL*Plus client.

It is recommended that you add SET SQLPLUSCOMPATIBILITY 11.2 to your scripts to maximize their compatibility with future versions of SQL*Plus.

SQL*Plus Compatibility Matrix

The SQL*Plus Compatibility Matrix tabulates behavior affected by each SQL*Plus compatibility setting. SQL*Plus compatibility modes can be set in three ways:

• You can include a SET SQLPLUSCOMPATIBILITY command in your site or user profile. On installation, there is no SET SQLPLUSCOMPATIBILITY setting in glogin.sql. Therefore the default compatibility is 11.2.

• You can use the SQLPLUS -C[OMPATIBILITY] {x.y[.z]} command argument at startup to set the compatibility mode of that session.

• You can use the SET SQLPLUSCOMPATIBILITY {x.y[.z]} command during a session to set the SQL*Plus behavior you want for that session.

The following table shows the release of SQL*Plus which introduced the behavior change, and hence the minimum value of SQLPLUSCOMPATIBILITY to obtain that behavior. For example, to obtain the earlier behavior of the VARIABLE command, you must either use a version of SQL*Plus earlier than 9.0.1, or you must use a SQLPLUSCOMPATIBILITY value of less than 9.0.1. The lowest value that can be set for SQLPLUSCOMPATIBILITY is 7.3.4

Table 12-6 Compatibility Matrix

Value	Consequence	When available
>=10.1	SHOW ERRORS sorts PL/SQL error messages using new columns only available in Oracle Database 10g.	10.1
>=10.1	SPOOL Options CREATE, REPLACE, SAVE were added which may affect filename parsing on some platforms.	10.1
>=10.1	SET SQLPROMPT	10.1
>=10.1	Whitespace characters are allowed in Windows file names that are enclosed in quotes. Some other special punctuation characters are now disallowed in Windows.	10.1
>=10.1	Glogin/login files are called for each reconnect.	10.1
<10.1	Uses the obsolete DOC> prompt when echoing /* comments.	10.1
>= 9.2	A wide column defined FOLD_AFTER may be displayed at the start of a new line. Otherwise it is incorrectly put at the end of the preceding line with a smaller width than expected.	9.2.
>= 9.0	Whitespace before a slash ('/') in a SQL statement is ignored and the slash is taken to mean execute the statement. Otherwise the slash is treated as part of the statement, for example, as a division sign.	9.0.1.4.
>= 9.0	The length specified for NCHAR and NVARCHAR2 types is characters. Otherwise the length may represent bytes or characters depending on the character set.	9.0.1

SET SQLPRE[FIX] {#c}

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}

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:

Text in nested quotes is not parsed for substitution. To have a SQL*Plus prompt of your username, followed by '@', and then your connection identifier, enter:

SET SQLT[ERMINATOR] {;cON OFF}

Sets the character used to end script or data entry for PL/SQL blocks or SQL statements, to execute the script, and to load it into the buffer.

It cannot be an alphanumeric character or a whitespace. OFF means that SQL*Plus recognizes no command terminator; you terminate a SQL command by entering an empty line or a slash (/). If SQLBLANKLINES is set ON, you must use the BLOCKTERMINATOR to terminate a SQL command. ON resets the terminator to the default semicolon (;).

SET SUF[FIX] {SQLtext}

Sets the default file extension that SQL*Plus uses in commands that refer to scripts. SUFFIX does not control extensions for spool files.

Example

To change the default command-file extension from the default, .SQL to .TXT, enter

If you then enter

SQL*Plus will look for a file named EXAMPLE.TXT instead of EXAMPLE.SQL.

SET TAB {ON OFF}

Determines how SQL*Plus formats white space in terminal output. OFF uses spaces to format white space in the output. ON uses the TAB character. TAB settings are every eight characters. The default value for TAB is system dependent.

SET TERM[OUT] {ON OFF}

Controls the display of output generated by commands in a script that is executed with @, @@ or START. OFF suppresses the display so that you can spool output to a file without displaying the output on screen. ON displays the output on screen. TERMOUT OFF does not affect output from commands you enter interactively or redirect to SQL*Plus from the operating system.

SET TI[ME] {ON OFF}

Controls the display of the current time. ON displays the current time before each command prompt. OFF suppresses the time display.

SET TIMI[NG] {ON OFF}

Controls the display of timing statistics.

ON displays timing statistics on each SQL command or PL/SQL block run. OFF suppresses timing of each command.

See TIMING for information on timing multiple commands.

Example

The format of timing statistics is dependent on the operating system. In Linux and Windows, the timing statistics are in 24 hour format displaying hours, minutes, seconds and hundredths of seconds

If you enter

SQL*Plus displays output like

SET TRIM[OUT] {ON OFF}

Determines whether SQL*Plus puts trailing blanks at the end of each displayed line. ON removes blanks at the end of each line, improving performance especially when you access SQL*Plus from a slow communications device. OFF enables SQL*Plus to display trailing blanks. TRIMOUT ON does not affect spooled output.

SET TRIMS[POOL] {ON OFF}

Determines whether SQL*Plus puts trailing blanks at the end of each spooled line. ON removes blanks at the end of each line. OFF enables SQL*Plus to include trailing blanks. TRIMSPOOL ON does not affect terminal output.

SET UND[ERLINE] {-cON OFF}

Sets the character used to underline column headings in reports. The underline character cannot be an alphanumeric character or a white space. ON or OFF turns underlining on or off. ON changes the value of c back to the default '-'.

SET VER[IFY] {ON OFF}

Controls whether to list the text of a SQL statement or PL/SQL command before and after replacing substitution variables with values. ON lists the text; OFF suppresses the listing.

SET WRA[P] {ON OFF}

Controls whether to truncate the display of a selected row if it is too long for the current line width. OFF truncates the selected row; ON enables the selected row to wrap to the next line.

Use the WRAPPED and TRUNCATED clauses of the COLUMN command to override the setting of WRAP for specific columns.

SET XMLOPT[IMIZATIONCHECK] [ON OFF]

Controls whether only XML queries and DML operations that are fully optimized are executed. ON prevents the execution of any XML query or DML operation that cannot be fully optimized and writes the reason in the trace file. OFF does not prevent the execution of such queries and operations. OFF is the default.

SET XMLOPT[IMIZATIONCHECK] ON is only to assist during development and debugging an XML query or DML operation.

SET XQUERY BASEURI {text}

Specifies the base URI used to resolve relative URIs in functions. It enables the prefix of the file accessed by an XQuery to be changed.

To unset the BASEURI, set an empty string, for example:

Take care to enter valid values as values are checked only when an XQUERY command is issued.

Example

This is equivalent to:

SET XQUERY ORDERING {UNORDERED ORDERED DEFAULT}

Sets the ordering of output from an XQuery. There are three values:

UNORDERED specifies that results are sorted in the order they are retrieved from the database.

ORDERED specifies that results are sorted as defined by the XQuery.

DEFAULT specifies the database default. In Oracle Database 10g the default is UNORDERED.

When SET XQUERY ORDERING is not set, the default is DEFAULT (UNORDERED).

Example

This is equivalent to:

SET XQUERY NODE {BYVALUE BYREFERENCE DEFAULT}

Sets the node identity preservation mode. The preservation mode applies to all expressions that either create a new node (such as element constructors) or return an item or sequence containing nodes (such as path expressions). There are three values:

BYVALUE specifies that the node identity need not be preserved. This means that any node operation such as creation, or that is returned as a result of an expression is deep copied and loses its context from the original tree. Subsequent operations on this node that test for node identity, parent or sibling axes or ordering will be undefined.

BYREFERENCE specifies that node identities are to be preserved. Subsequent operations on this node preserve the node's context and definition.

DEFAULT specifies the database default. In Oracle Database 10g the default is BYVALUE.

When SET XQUERY NODE is not set, the default is DEFAULT (BYVALUE).

Example

Hulk games free download. This is equivalent to:

SET XQUERY CONTEXT {text}

Specifies an XQuery context item expression. A context item expression evaluates to the context item, which may be either a node (as in the expression fn:doc('bib.xml')//book[fn:count(./author)>1]), or an atomic value (as in the expression (1 to 100)[. mod 5 eq 0]).

To unset the XQUERY CONTEXT, set an empty string, for example:

Take care to enter valid values as values are checked only when an XQUERY command is issued.

Example

This is equivalent to: