Return to Contents

Next Page

4 Using System Variables

Most system variables are the SET command options used to control the behavior of the SQL*Plus system. For example, to set the output line size from SQL*Plus:

    SQL> set linesize 60

The current status of each system variable can be displayed with the SHOW command.

    SQL> show linesize
    linesize 80

System variables are sometimes known as SET variables.

Some system variables contain values that cannot be set. For example, RELEASE (a string representation of the SQL*Plus version) can only be shown.

See SET and SHOW in the SQL*Plus User's Guide and Reference.

4.1 System Variables Influencing Substitution Variables

Several system variables influence substitution variables.

4.1.1 SET CONCAT

Use SET CONCAT to define the character that separates the name of a substitution variable from alphanumeric characters that immediately follow the variable name. By default it is a single period (.).

For example, if "mycity" is defined as "Melbourne" then the command:

    SQL> spool &mycity.Australia.log

is the same as:

    SQL> spool MelbourneAustralia.log

See SET CONCAT in the SQL*Plus User's Guide and Reference.

4.1.2 SET DEFINE

Use SET DEFINE OFF to stop SQL*Plus performing any variable substitution. This makes SQL*Plus treat all ampersands (&) as literal characters and prevents SQL*Plus prompting you for values:

    SQL> set define off
    SQL> select 'B&W' MyHeading from dual;


     MYH
     ---
     B&W

The default substitution variable prefix is an ampersand (&). The SET DEFINE command can be used to change the variable-name prefix character. SET DEFINE ON turns variable substitution back on and resets the prefix character to "&"

Sometimes in SQL*Plus literature you may see references to the SET SCAN command. This is an obsolete alternative for SET DEFINE. To ensure maximum portability of scripts use SET DEFINE.

See SET DEFINE in the SQL*Plus User's Guide and Reference.

4.1.3 SET ESCAPE

Use SET ESCAPE to prevent isolated occurrences of "&" from being treated as the substitution variable prefix:

    SQL> set escape \
    SQL> select 'B\&W' MyHeading from dual;

     MYH

     ---
     B&W

Any "&" without the escape character is treated as a variable prefix.

See SET ESCAPE in the SQL*Plus User's Guide and Reference.

4.1.4 SET NULL

SET NULL sets the text that SQL*Plus displays when a NULL data value is printed.

A substitution variable may take the value of the SET NULL text if a COLUMN NEW_VALUE (or COLUMN OLD_VALUE) command associated the variable with a selected column and the current row contains a NULL value. The type of the substitution variable temporarily changes to CHAR while it contains NULL.

See SET NULL in the SQL*Plus User's Guide and Reference.

4.1.5 SET NUMFORMAT

SET NUMFORMAT and SET NUMWIDTH interact. Use SET NUMFORMAT to change the default display format of a numeric variable. Use SET NUMFORMAT "" to remove the format. When there is no format, the default number formatting uses the SET NUMWIDTH option:

    SQL> show numformat
    numformat ""
    SQL> define myn
    DEFINE MYN             =     123.45 (NUMBER)

    SQL> set numformat 0999.9
    SQL> define myn
    DEFINE MYN             =  0123.5 (NUMBER)
    SQL> set numformat 9.9EEEE
    SQL> define myn
    DEFINE MYN             =   1.2E+02 (NUMBER)
    SQL> prompt The number is &myn
    The number is   1.2E+02

See SET NUMFORMAT in the SQL*Plus User's Guide and Reference.

4.1.6 SET NUMWIDTH

SQL*Plus uses the value of SET NUMWIDTH only if there is no value for SET NUMFORMAT. Use SET NUMWIDTH to change the display width of a numeric variable:

    SQL> show numformat
    numformat ""
    SQL> show numwidth
    numwidth 15
    SQL> define myn
    DEFINE MYN             =     123.45 (NUMBER)
    SQL> set numwidth 6
    SQL> define myn
    DEFINE MYN             = 123.45 (NUMBER)
    SQL> set numwidth 15
    SQL> define myn
    DEFINE MYN             =          123.45 (NUMBER)

Note the value is right justified within the field width and the number of leading spaces changes in each example.

SQL*Plus displays pound signs (#) if the format or field width for a numeric substitution variable is too small for the value.

See SET NUMWIDTH in the SQL*Plus User's Guide and Reference.

4.1.7 SET SQLPROMPT

Substitution variables can be used in your prompt. Like variables used in TTITLE, they do not need to be prefixed with '&' else they will not be dynamically substituted. Text in nested quotes will never be substituted. This example shows the user HR setting prompt and connecting to the OE schema. The predefined substitution variables _USER and _CONNECT_IDENTIFIER are used in the prompt:

    SQL> set sqlprompt "_user'@'_connect_identifier:SQL> "
    HR@MYDB:SQL> connect system/manager
    SYSTEM@MYDB:SQL> disconnect
    @:SQL> connect OE/OE@otherdb
    OE@otherdb:SQL>

Each time the prompt is printed, SQL*Plus parses it and looks up the current values of the variables. For performance reasons, the word SQL in the default prompt "SQL> " is never treated as a substitution variable.

4.1.8 SET VERIFY

Use SET VERIFY to control whether SQL*Plus echoes the old and new statement text when it substitutes a variable's value. SET VERIFY only has an effect on substitution variables used in SQL and PL/SQL statements:

    SQL> set verify on

    SQL> define myv = 100

    SQL> select last_name from employees where employee_id = &myv;
    old   1: select last_name from employees where employee_id = &myv

    new   1: select last_name from employees where employee_id = 100

    LAST_NAME
    -------------------------
    King


    SQL> set verify off

    SQL> select last_name from employees where employee_id = &myv;

    LAST_NAME
    -------------------------
    King

Variables used in SQL*Plus commands (like SET and TTITLE) are not verified.

See SET VERIFY in the SQL*Plus User's Guide and Reference.

4.2 System Variables in Titles and EXIT

There is a special syntax to reference system variables in TTITLE, BTITLE, REPHEADER, REPFOOTER, and EXIT commands. The name of each special variable is the same as the SHOW option prefixed with "SQL.".

The special variables that can be referenced include:

For example:

    SQL> ttitle left 'Salary Report. Page: ' sql.pno
    SQL> select salary from employees;
    SQL> exit sql.sqlcode

System variables of numeric type e.g. SQL.SQLCODE are formatted using the same rules as numeric substitution variables.

The variables cannot be prefixed with an "&" (see 5.7.1 Using "&" Prefixes With Title Variables).

These variables are not substitution variables. The DEFINE command does not show them. They cannot be referenced in general commands. The system variables are not affected if you create substitution variables with the same name. For example, SQL.USER is not affected if you create a substitution variable called USER. The system variable SQL.RELEASE is not affected if the predefined substitution variable _O_RELEASE is changed.

Return to Contents

Next Page