Previous Table of Contents Next

The set Command

The most common command issued inside a script is the set command, which allows the user to configure SQL environment settings. A listing of the most commonly used SQL settings is presented in Table 3.5.

Table 3.5 The most commonly used SQL settings.

SQL Setting Functionality
define Defines the symbol used to prefix variables in SQL*Plus. This defaults to an ampersand (&).
echo Instructs SQL*Plus to echo each command to standard output as they are run from a script. This setting defaults to OFF and can also be set to ON.
editfile Sets the filename created by the ed command in SQL*Plus.
escape Sets the character that can be used to display the define character as a normal character. The default escape character is a backslash (\). The setting can also be set to ON or OFF. Turning the setting to ON restores the backslash as the escape character.
feedback Determines when SQL*Plus will display the number of records returned from a query and defaults to ON. The setting can also be set to OFF or n (an integer value greater than -1). A value of 0 is synonymous to setting the setting to ON ; a value of n greater than 0 instructs SQL*Plus to display the message if n records are returned.
flush Turns off standard output from a script file when set to OFF. The default value is ON.
heading Instructs SQL*Plus about the display of column headings. The default is ON. Turning the setting to OFF causes SQL*Plus to not display headers for columns.
linesize Sets the maximum line width that can be displayed in SQL*Plus. The default value is 80. The value of this setting is used to calculate column values for titles and footers displayed with an alignment of CENTER or RIGHT. The maximum value is 999.
long Sets the maximum width for the display of long variables. The default value is 80, but the setting can be set to any value between 1 and 32767 (the value must be less than the maxdata setting).
maxdata Defines the maximum row width that SQL*Plus can handle (including wrapped lines). The default and maximum values for this setting are OS dependent.
newpage Defines the number of blank lines printed between the bottom of one page and the title at the top of the next page. The default value is 1. A value of 0 sends a form feed character.
null Sets the value to be displayed when a column is NULL. The default is a string of blanks.
numformat Defines the default number format for numerical data. See Table 3.4 for a listing of valid formats.
numwidth Defines the default width for numerical columns. The default value is 10.
pagesize Defines the number of lines per page. The default is 14 lines.
pause Instructs SQL*Plus to pause while displaying data and require acknowledgement from the user before returning the next page of data returned from a query. The default value is OFF. The setting may be set to ON. Turning this setting to ON requires a carriage return before the first page of data is displayed.
serveroutput Allows the display of output from the DBMS_Output package inside SQL*Plus. The default is OFF. The setting may be set to ON as well.
showmode Instructs SQL*Plus to display both the old and new values of an SQL setting when the setting is changed using the set command.
sqlprompt Sets the text to be displayed for the SQL*Plus command prompt.
termout Determines if output will be spooled to standard output. The default is ON. Using OFF improves performance.
timing Instructs SQL*Plus to display the execution time of a command after the command has been completed. On Unix systems, the value is typically displayed in hundredths of a second.
verify Controls the display of old and new values for variables in SQL statements. The default is ON.

The show Command

The show command instructs SQL*Plus to display the current settings for a session. The command takes the following arguments:

  all—Shows all current settings.
  btitle—Shows the current btitle definition.
  lno—Shows the position on the current page.
  pno—Shows the current page number.
  release—Shows the release number of the Oracle database.
  spool—Shows the current spool status.
  sqlcode—Shows the value of the system variable SQL.SQLCODE (the most recent operation’s return code).
  sqlerrm—Shows the value of the system variable SQL.SQLERRM (the most recent operation’s error).
  ttitle—Shows the value of the current ttitle definition.
  user—Shows your user ID.
  wrap—Indicates if data is being wrapped or truncated in output.

You may also specify the name of any SQL setting as the target of the show command.

Previous Table of Contents Next