SQL*Plus 10.1 Substitution Variables

This article from 2004 was on OTN for many years. It wasn't flagged for saving when they did an infrastructure migration in 2010 but I think it still has some value because SQL*Plus is fundamentally unchanged. Ignore the reference manual links, which don't point to the current manual. Also ignore the section on iSQL*Plus, a product obsoleted by the introduction of Oracle Apex and SQL Developer.


Contents

1 Introduction
2 Using Substitution Variables
2.1 Creating, Showing and Deleting Substitution Variables
2.2 Referencing Substitution Variables
2.3 Prompting for Undefined Variables
2.4 Difference Between "&" and "&&" Prefixes
2.5 Storing a Query Column Value in a Substitution Variable
2.6 Predefined Substitution Variables
2.7 Script Parameters
2.8 More on Substitution Variables
3 Using Bind Variables
3.1 Assigning Substitution Variables to Bind Variables
3.2 Assigning Bind Variables to Substitution Variables
4 Using System Variables
4.1 System Variables Influencing Substitution Variables
4.1.1 SET CONCAT
4.1.2 SET DEFINE
4.1.3 SET ESCAPE
4.1.4 SET NULL
4.1.5 SET NUMFORMAT
4.1.6 SET NUMWIDTH
4.1.7 SET SQLPROMPT
4.1.8 SET VERIFY
4.2 System Variables in Titles and EXIT
5 SQL*Plus Substitution Variable Commands
5.1 ACCEPT Command
5.2 COLUMN Command
5.3 DEFINE Command
5.4 EDIT Command
5.5 EXIT Command
5.6 HOST Command
5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands
5.7.1 Using "&" Prefixes With Title Variables
5.7.2 Variables and Text Spacing in Titles
5.8 UNDEFINE Command
5.9 WHENEVER Command
6 Substitution Variables Namespace, Types, Formats and Limits
6.1 Substitution Variable Namespace
6.2 Substitution Variable Types
6.3 Substitution Variable Formats
6.4 Substitution Variable Limits
7 iSQL*Plus and Substitution Variables
7.1 iSQL*Plus 9 and SQL*Plus Substitution Variable Compatibility
7.2 iSQL*Plus Parameters
8 Substitution Variable Summary
9 Substitution Variable Examples
9.1 Setting a Substitution Variable's Value
9.2 Using a Substitution Variable
9.3 Finding All Defined Substitution Variables
9.4 Inserting Data Containing "&" Without Being Prompted
9.5 Putting the Current Date in a Spool File Name
9.6 Appending Alphanumeric Characters Immediately After a Substitution Variable
9.7 Putting a Period After a Substitution Variable
9.8 Using a Fixed Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER
9.9 Using a Changing Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER
9.10 Using the Value of a Bind Variable in a SQL*Plus Command Like SPOOL
9.11 Passing Parameters to SQL*Plus Substitution Variables
9.12 Passing Operating System Variables to SQL*Plus
9.13 Passing a Value to a PL/SQL Procedure From the Command Line
9.14 Allowing Script Parameters to be Optional and Have a Default Value
9.15 Passing a Value to an iSQL*Plus Dynamic Report for the Web
9.16 Customizing Parameter Prompts for an iSQL*Plus Dynamic Report for the Web
9.17 Using a Variable for the SQL*Plus Return Status
9.18 Putting the Username and Database in the Prompt

1 Introduction

This document explains how SQL*Plus substitution variables work and where they can be used. It shows the relationship between the three types of variable (substitution, bind, and system) used in SQL*Plus.

Substitution variables can replace SQL*Plus command options or other hard-coded text. They can be used to customize SQL*Plus script output. Substitution variable references in a statement are pre-processed and replaced before SQL*Plus executes the statement. Variable values can be pre-defined, prompted for, or set to script parameters. Variables can also hold values returned from queries. Sometimes substitution variables are known as user variables or define variables.

Bind variables store data values for SQL and PL/SQL statements executed in the RDBMS. They can hold single values or complete result sets.

System variables contain values directly controlling SQL*Plus, such as the line size and page size of reports. Some system variables affect how substitution variables are processed. System variables are sometimes called SET variables.

This document is a complete reference but you can understand and use substitution variables in a few minutes by reading the overview in Section 2 and looking at the examples in the last section.

Section 3 and Section 4 discuss bind and system variables respectively, and how they interact with substitution variables.

Section 5 discusses in detail all SQL*Plus commands that interact with substitution variables.

Section 6 gives substitution variable limits and some finer points.

Section 7 covers substitution variables in the iSQL*Plus web based interface.

Section 8 is a brief summary of substitution variables.

Section 9 gives a number examples of using substitution variables.

This document was updated in February 2004 for SQL*Plus Version 10.1. However all examples in this document apply to previous versions of SQL*Plus, except where noted.

Small sections were borrowed from the SQL*Plus User's Guide and Reference and the SQL*Plus FAQ. Some examples were derived from Support Notes and from questions posted on the iSQL*Plus Forum.

Thanks to all reviewers, especially Glenn Stokol, Sharon Castledine and the SQL*Plus Team.

2 Using Substitution Variables

2.1 Creating, Showing and Deleting Substitution Variables

Substitution variables can be explicitly created with the DEFINE command. Defining a variable means storing a value for future use:

    SQL> define myv = 'King'

This creates a variable called "myv" containing the text "King".

Another way to create substitution variables is with the ACCEPT command. This can be used to prompt for a value:

    SQL> accept myv2 char prompt 'Enter a last name: '

This command causes SQL*Plus to stop and prompt you to enter a character string:

    Enter a last name: _

What you enter is stored in the variable "myv2".

The DEFINE command can also be used to display known variables. It shows the variable name, value and type. Any variable that DEFINE lists is said to be defined:

    SQL> define myv
    DEFINE MYV             = "King" (CHAR)

All variables that are currently defined can be shown by executing the DEFINE command with no arguments:

    SQL> define
    DEFINE MYV             = "King" (CHAR)
    DEFINE MYV2            = "Taylor" (CHAR)
    ...

Any variable not listed is undefined:

    SQL> define abc

    SP2-0135: symbol abc is UNDEFINED

Substitution variables can be removed with the UNDEFINE command:

    SQL> undefine myv

2.2 Referencing Substitution Variables

Variables can be referenced by prefixing their name with an ampersand (&):

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

SQL*Plus lists the statement line number and line containing the substitution variable "myv" before and after substitution:

    old   1: select employee_id from employees where last_name = '&myv'
    new   1: select employee_id from employees where last_name = 'King'

Lines verifying substitution are displayed for SQL or PL/SQL statements. The lines can be hidden with SET VERIFY OFF. Verification never occurs for variables in SQL*Plus commands (e.g. SPOOL and SET).

A more practical use of substitution variables is to prompt for a value before referencing the variable:

    SQL> accept myv char prompt 'Enter a last name: '
    SQL> select employee_id from employees where last_name = '&myv';

If these two commands are stored in a SQL*Plus script, a different last name can be entered each time the script is run.

2.3 Prompting for Undefined Variables

If a variable is referenced using an "&" prefix, but the variable value is not yet defined, SQL*Plus prompts you for a value:

    SQL> define myname
    SP2-0135: symbol myname is UNDEFINED

    SQL> select employee_id from employees where last_name = '&myname';
    Enter value for myname:

After you enter a value, SQL*Plus substitutes the variable and executes the query.

The Oracle Globalization Language setting (e.g. the language component of the NLS_LANG environment variable) determines the exact language used for the "Enter value for" prompt. The prompt text cannot otherwise be changed.

2.4 Difference Between "&" and "&&" Prefixes

Both single ampersand (&) and double ampersand (&&) can prefix a substitution variable name in a statement. SQL*Plus pre-processes the statement and substitutes the variable's value. The statement is then executed. If the variable was not previously defined then SQL*Plus prompts you for a value before doing the substitution.

If a single ampersand prefix is used with an undefined variable, the value you enter at the prompt is not stored. Immediately after the value is substituted in the statement the variable is discarded and remains undefined. If the variable is referenced twice, even in the same statement, then you are prompted twice. Different values can be entered at each prompt:

    SQL> prompt Querying table &mytable
    Enter value for mytable: employees
    Querying table employees
    SQL> select employee_id from &mytable where last_name = 'Jones';
    Enter value for mytable: employees

    EMPLOYEE_ID
    -----------
            195

If a double ampersand reference causes SQL*Plus to prompt you for a value, then SQL*Plus defines the variable as that value. Any subsequent reference to the variable (even in the same command) using either "&" or "&&" substitutes the newly defined value. SQL*Plus will not prompt you again:

    SQL> prompt Querying table &&mytable
    Enter value for mytable: employees
    Querying table employees
    SQL> select employee_id from &mytable where last_name = 'Jones';

    EMPLOYEE_ID
    -----------
            195

2.5 Storing a Query Column Value in a Substitution Variable

Data stored in the database can be put into substitution variables:

    SQL> column last_name new_value mynv
    SQL> select last_name from employees where employee_id = 100;

The NEW_VALUE option in the COLUMN command implicitly creates a substitution variable called "mynv". The variable is not physically created until a query references the column LAST_NAME. When the query finishes, the variable "mynv" holds the last retrieved value from column "last_name":

    SQL> define mynv
    DEFINE mynv      = "King" (CHAR)

2.6 Predefined Substitution Variables

The predefined substitution variables created when you start SQL*Plus can be seen by entering DEFINE with no arguments. Each predefined variable is prefixed with an underscore. The predefined variables can be undefined or redefined just like user defined substitution variables.

In SQL*Plus Release 10.1 the predefined variables are:

    _CONNECT_IDENTIFIER
    _DATE
    _EDITOR
    _O_RELEASE
    _O_VERSION
    _PRIVILEGE
    _SQLPLUS_RELEASE
    _USER

The variables _DATE, _PRIVILEGE, and _USER were introduced in SQL*Plus 10.1. The variable _CONNECT_IDENTIFIER was introduced in SQL*Plus 9.2.

The variable _CONNECT_IDENTIFIER contains the connection identifier used to start SQL*Plus. For example, if the SQL*Plus connection string is "hr/my_password@MYSID" then the variable contains MYSID. If you use a complete Oracle Net connection string like "hr/my_password@(DESCRIPTION=(ADDRESS_LIST=...(SERVICE_NAME=MYSID.MYDOMAIN)))" then _CONNECT_IDENTIFIER will be set to MYSID. If the connect identifier is not explicitly specified then _CONNECT_IDENTIFIER contains the default connect identifier Oracle uses for connection. For example, on UNIX it will contain the value in the environment variable TWO_TASK or ORACLE_SID. If SQL*Plus is not connected then the variable is defined as an empty string.

The variable _DATE can be either dynamic, showing the current date or it can be set to a fixed string. The date is formatted using the value of NLS_DATE_FORMAT and may show time information. By default a DEFINE or dereference using &_DATE will give the date at the time of use. _DATE can be UNDEFINED, or set to a fixed string with an explicit DEFINE command. Dynamic date behavior is re-enabled by defining _DATE to an empty string.

The variable _EDITOR contains the external text editor executable name. See 5.4 EDIT Command.

The variable _O_RELEASE contains contains a string representation of the Oracle database version number. If your Oracle database version is 9.2.0.3.0 then the variable contains "902000300". The Oracle version may be different from the SQL*Plus version if you use Oracle Net to connect to a remote database.

The variable _O_VERSION contains a text string showing the database version and available options.

When SQL*Plus is connected as a privileged user the variable _PRIVILEGE contains the connection privilege "AS SYSBDA" or "AS SYSOPER". If SQL*Plus is connected as a normal user the variable is defined as an empty string.

The variable _SQLPLUS_RELEASE contains the SQL*Plus version number in a similar format to _O_RELEASE.

The variable _USER contains the current username given by SHOW USER. If SQL*Plus is not connected, the variable is defined as an empty string.

2.7 Script Parameters

Parameters can be passed to SQL*Plus scripts. For example, from the command line:

    sqlplus hr/my_password @myscript.sql King

You can also pass parameters when calling a SQL*Plus script from within a SQL*Plus session, for example:

    SQL> @myscript.sql King

Script parameters become defined substitution variables. The variable name for the first parameter is "1", the second is "2", etc. The effect is as if you start SQL*Plus and type:

    SQL> define 1 = King
    SQL> @myscript.sql

Commands in myscript.sql can reference "&1" to get the value "King". A DEFINE command shows the parameter variable:

    SQL> define 1
    DEFINE 1      = "King" (CHAR)

Script parameter variables have type CHAR, similar to variables explicitly created with DEFINE.

Quoting parameters with single or double quotes is allowed. This lets whitespace be used within parameters. Operating systems and scripting languages that call SQL*Plus handle quotes in different ways. They may or may not pass quotes to the SQL*Plus executable. For example, in a standard Bourne shell on UNIX, quotes around parameters are stripped before the parameters are passed to SQL*Plus, and SQL*Plus never sees the quotes.

It is recommended to check how quoted parameters are handled on your operating system with your patch level of SQL*Plus. For portability between UNIX and Windows environments use double quotes around parameters containing whitespace.

SQL*Plus Releases 8.1.7, 9.2.0.3 (and other 9.x versions patched for bug 2471872) and 10.1 onwards remove an outer set of single or double quotes from parameters passed on the SQL*Plus command line. This makes SQL*Plus behave the same way on operating systems that do not themselves strip quotes as it does when the operating system strips the quotes before calling SQL*Plus.

As an example of passing parameters, when SQL*Plus 10.1 is called in the UNIX shell script:

    #! /bin/sh
    sqlplus hr/my_password @myscript.sql "Jack and Jill"

only one program parameter is defined. References in myscript.sql to "&1" are replaced with "Jack and Jill" (without quotes - because the shell script does not pass quotes to SQL*Plus).

From SQL*Plus Release 9.0 onwards, an empty string can be passed as a parameter.

2.8 More on Substitution Variables

Substitution variable references are pre-processed and substituted before the command is otherwise parsed and executed. For each statement SQL*Plus will:

    1. Loop for each "&" and "&&" variable reference
           If the variable is defined
               Replace the variable reference with the value
           else
               Prompt for a value
               Replace the variable reference with the value
               If the variable is prefixed with "&&" then
                   define (i.e. store) the variable for future use
    2. Execute the statement

Step 1 happens inside the SQL*Plus client tool. SQL*Plus then sends the final statement to the database engine where step 2 occurs.

It is not possible to repeatedly prompt in a PL/SQL loop. This example prompts once and the entered value is substituted in the script text. The resulting script is then sent to the database engine for execution. The same entered value is stored five times in the table:

    begin
      for i in 1 .. 5 loop
        insert into mytable values (&myv);
      end loop;
    end;
    /

Substitution variables are not recursively expanded. If the value of a referenced variable contains an ampersand, then the ampersand is used literally and is not treated as a second variable prefix:

    SQL> set escape \
    SQL> define myv = \&mytext
    SQL> prompt &myv
    &mytext

You cannot use a substitution variable as the first token of a command. Each command name must be hard coded text otherwise an error is displayed. For example:

    SQL> &myv * from dual;
    SP2-0734: unknown command beginning "&myv * fro..." - rest of line ignored.

Substitution variables cannot be used in buffer editing commands like APPEND, CHANGE, DEL, and INPUT. Ampersands (&) in these commands are treated literally.

If you wish to use alphanumeric characters immediately after a substitution variable name, put the value of SET CONCAT - by default a period (.) - to separate the variable name from the following characters. For example, if "mycity" is defined as "Melbourne" then:

    SQL> spool &mycity.Australia.log

is the same as:

    SQL> spool MelbourneAustralia.log

If you want to append a period immediately after a substitution variable name then use two periods together. For example, if "myfile" is defined as "reports" then the command:

    SQL> spool &myfile..log

is the same as:

    SQL> spool reports.log

Text in ANSI "/* */" or "--" comments that looks like a substitution variable may be treated as one, for example:

    SQL> select department_id, location_id /* get dept & loc */ from departments;
    Enter value for loc: _

Here the text "& loc" in the comment is interpreted as a variable reference. SQL*Plus prompts you for a value for the variable "loc".

3 Using Bind Variables

Bind variables are used in SQL and PL/SQL statements for holding data or result sets. They are commonly used in SQL statements to optimize statement performance. A statement with a bind variable may be re-executed multiple times without needing to be re-parsed. Their values can be set and referenced in PL/SQL blocks. They can be referenced in SQL statements e.g. SELECT. Except in the VARIABLE and PRINT commands, bind variable references should be prefixed with a colon.

Bind variables are created with the VARIABLE command. The following PL/SQL block sets a bind variable:

    SQL> variable bv number
    SQL> begin
      2    :bv := 8;
      3  end;
      4  /


    PL/SQL procedure successfully completed.

Once a value is set, you can show it with the PRINT command.

    SQL> print bv

            BV
    ----------
             8

Numeric bind variables can be used in the EXIT command to return a value to the operating system:

    SQL> EXIT :bv

Other SQL*Plus commands do not recognize bind variables.

There is no way to undefine or delete a bind variable in a SQL*Plus session. However, bind variables are not remembered when you exit SQL*Plus.

For information about automatically displaying values and using REFCURSOR bind variables for whole result sets, see Using Bind Variables and VARIABLE, in the SQL*Plus User's Guide and Reference.

3.1 Assigning Substitution Variables to Bind Variables

You can assign a substitution variable to a bind variable:

    SQL> define mysubv = 123
    SQL> variable mybndv number
    SQL> execute :mybndv := &mysubv;

SQL*Plus executes the PL/SQL assignment statement after it substitutes the value of "mysubv". If "mysubv" was not already defined, you would be prompted for a value.

The bind variable can be used in subsequent SQL or PL/SQL commands.

3.2 Assigning Bind Variables to Substitution Variables

Sometimes it is useful to make the value of a bind variable available to SQL*Plus commands like TTITLE or SPOOL. For example, you might want to call a PL/SQL function that returns a string and use the value for a SQL*Plus spool file name. The SPOOL command does not understand bind variable syntax so the bind variable value needs to be assigned to a substitution variable first.

This is done using COLUMN NEW_VALUE and SELECT commands. For example, declare a bind variable in SQL*Plus and instantiate it in a PL/SQL block. Its value can be returned from a PL/SQL function, or like here, set by a direct assignment:

    SQL> variable mybv varchar2(14)
    SQL> begin
      2    /* ... */
      3    :mybv := 'report.log';
      4  end;
      5  /

Pass the bind variable's value to a new substitution variable "nv" by using a query:

    SQL> column mybvcol new_value nv noprint
    SQL> select :mybv mybvcol from dual;

Now you can use the substitution variable in a SPOOL command:

    SQL> spool &nv

The SPOOL command executes as if you had typed

    SQL> spool report.log

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

In SQL*Plus 10g, substitution variables in the prompt are dynamically substituted each time the prompt is printed. Like variables used in TTITLE, they should not be prefixed with '&' else they are substituted only once when the SET SQLPROMPT command is executed.

This example shows the HR user setting the prompt and re-connecting to the OE schema. The predefined substitution variables _USER and _CONNECT_IDENTIFIER are used in the prompt to give the current username and database:

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

Each time the prompt is printed, SQL*Plus checks each word to see if it is a defined substitution variable. If it is, it will have its value printed. Otherwise it is displayed verbatim. Text in nested quotes will never be substituted. For performance reasons, the word SQL in the default prompt "SQL> " is never treated as a substitution variable.

Variables in the prompt are dynamically substituted only when SET SQLPLUSCOMPATIBILITY is 10.1 or greater. Otherwise, and for SQL*Plus versions 9.2 and earlier, "&"-prefixed variables can be substituted once when the SET SQLPROMPT command is executed.

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:

  • SQL.PNO - page number
  • SQL.LNO - line number
  • SQL.USER - current username
  • SQL.RELEASE - SQL*Plus version
  • SQL.SQLCODE - last Oracle "ORA" error number

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.

5 SQL*Plus Substitution Variable Commands

Substitution variables can be used to replace options and values in almost all SQL*Plus commands. Several of the commands have special significance for substitution variables. These are discussed below.

5.1 ACCEPT Command

The ACCEPT command always prompts for a variable's value, creating a new variable or replacing an existing one. ACCEPT has advantages over a double ampersand (&&) variable reference that causes a prompt. ACCEPT allows the prompting text to be customized and allows a default value to be specified. ACCEPT does type and format checking.

    SQL> accept myv number default 10 prompt 'Enter a number: '
    Enter a number: _

In this example, if you enter alphabetic characters then an error is shown and you are re-prompted. If you press Enter without typing anything then the variable takes the value "10".

The ACCEPT command understands numbers, strings and dates. If a FORMAT clause is used, SQL*Plus validates the input against the given format. If the input is not valid, you are re-prompted for a value. For a list of format models, see Format Models in the Oracle Database SQL Reference 10g Release 1.

If a FORMAT specifier such as "A10" is used for a CHAR variable, the entered value may be any length up to and including 10 characters.

If a FORMAT specifier is used for a NUMBER type, the allowed inputs depend on the specifier used. For example, a specifier of "9EEEE" (for exponential notation) allows "3e2" but not "300" or "12e2". A format specifier of "999.99" allows both "123.45" and "67" to be entered.

DATE variables are validated against an explicitly supplied FORMAT or against the default session date format (like "DD-MON-YYYY").

After successful validation against the format model, variables are stored in the appropriate variable type. See 6.2 Substitution Variable Types. In particular, substitution variables created with an ACCEPT ... DATE command are stored with type CHAR.

The ACCEPT ... HIDE option can be used to prevent the value you enter from being displayed on the screen. This can be useful for scripts that need to prompt for passwords. Note some operating systems cannot redirect batch program script output into an ACCEPT ... HIDE command.

See ACCEPT in theSQL*Plus User's Guide and Reference for the full ACCEPT command syntax.

5.2 COLUMN Command

The COLUMN NEW_VALUE and COLUMN OLD_VALUE commands can be used to associate a substitution variable with a SELECT column's data.

    SQL> column department_id new_value dnv

When column "department_id" selected, a substitution variable "dnv" is created to hold each row of the column in turn. The variable remains defined after the query completes:

    SQL> select department_id
      2  from departments
      3  where department_id between 60 and 100;

    DEPARTMENT_ID
    -------------
               60
               70

               80
               90
              100

    SQL> define dnv
    DEFINE DNV             =        100 (NUMBER)

COLUMN OLD_VALUE and NEW_VALUE substitution variables can be used for basic numeric and text column types. They are used for any query executed that has a column of the same name as the COLUMN command. Their current value can be displayed in report headings and titles during query execution.

COLUMN NEW_VALUE variables should be used in TTITLE and REPHEADER titles at start of a page. They hold data from the new row about to be printed on the page. After the query finishes the variable has value of the last row.

COLUMN OLD_VALUE variables are used in BTITLE and REPFOOTER titles at the end of a page. They contain data from the old row most recently printed on the page. See 5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands for discussion and examples.

Variables change type as required. If another query with the same column name is run, the variable may take on a new type. Also if a number column contains null values, a substitution variable on the column changes from type NUMBER to CHAR for that row. This lets it hold the current string for the SET NULL option. The variable changes back to NUMBER when the next numeric value is fetched.

If no rows are selected by a query, and the substitution variable does not already exist, then a zero length CHAR variable is created. If the substitution variable exists then its value and type are not changed.

Similar to the DATE option for the ACCEPT command, a variable on a DATE column is stored as type CHAR.

COLUMN NEW_VALUE and OLD_VALUE can be used to transfer a value from a bind variable to a substitution variable. See 3.2 Assigning Bind Variables to Substitution Variables.

The COLUMN option NOPRINT can be used to suppress query results and stop them appearing in a final report.

See COLUMN in the SQL*Plus User's Guide and Reference for the command syntax.

5.3 DEFINE Command

Use the DEFINE command to explicitly create substitution variables:

    SQL> define myv = 'King'

The DEFINE command can also be used to display the value of a known variable. It shows the variable name, value and type:

    SQL> define myv
    DEFINE MYV             = "King" (CHAR)

Using DEFINE with no arguments lists all defined substitution variables. Any variable that DEFINE lists is said to be defined.

A variable may be redefined by repeating the DEFINE command with a different value.

The DEFINE command only ever creates variables with type CHAR.

See DEFINE in the SQL*Plus User's Guide and Reference for the command syntax.

5.4 EDIT Command

The EDIT command starts an external editor such as Notepad or Vi. On most operating systems SQL*Plus has a predefined substitution variable called _EDITOR set to the default editor's executable:

    SQL> define _editor
    DEFINE _EDITOR             = "Notepad" (CHAR)

EDIT can edit a named file. It can also edit the current SQL buffer (which holds the most recently executed SQL statement). When the external editor is closed the changed statement is loaded back into the SQL buffer. EDIT writes the SQL buffer to a temporary file called afiedt.buf. The temporary file name can be changed with the SET EDITFILE command.

You can redefine the value of the _EDITOR substitution variable to any editor.

On Windows _EDITOR can be set to "write.exe" to invoke WordPad. However, if the SQL buffer is being edited, the buffer is not automatically updated with the modified script. This is because SQL*Plus cannot tell when the WordPad editor has been closed.

A recommended way to create SQL*Plus scripts is to explicitly specify a file name for EDIT and then use the START or "@" commands to run this file.

See EDIT and SET EDITFILE, both in the SQL*Plus User's Guide and Reference.

5.5 EXIT Command

On many operating systems the EXIT command can pass the value of a numeric bind variable or substitution variable to the operating system environment. On UNIX, the return status from SQL*Plus can be displayed with the command "echo $?" in the Bourne, Korn and Bash shells, or with "echo $status" in the C shell.

To return a substitution variable, it is recommended not to use an ampersand prefix before its name. If you use "&" or "&&", the command preprocessor does the substitution using default number formatting rules (see 6.3 Substitution Variable Formats) before the EXIT command is finally parsed and executed. This is normal pre-processing of a numeric substitution variable in a command. Since the Oracle format specifier may include decimal and group separators which are not digits, or the number may overflow the format and be substituted as pound signs (#), there may be problems doing the final conversion from the resulting formatted string to the operating system return status. For example, if the formatting rules return exponential format and the number is formatted as "4E+05", then only the value "4" is returned to the operating system by the EXIT command.

When "&" does not prefix the substitution variable name, e.g. "EXIT myv", SQL*Plus internally uses the more practical format specifier "9999999990" to convert from the internal number format to the string used as the EXIT command parameter.

Note some operating systems limit the number range that can be returned from a program. On such systems the returned value may overflow and contain an unexpected number. This commonly limits the use of the system variable SQL.SQLCODE which contains the last Oracle error number. Typically this number is larger than an operating system supports as an exit return status.

If a non numeric variable is referenced in an EXIT statement the EXIT command exits but reports an error. The operating system return value is the same as for EXIT FAILURE.

Substitution variables are not saved when SQL*Plus exits. Only the predefined substitution variables and any variables set in the site and user profiles (e.g. glogin.sql and login.sql) are defined when you next start SQL*Plus.

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

5.6 HOST Command

The HOST command runs a specified operating system command or opens a command window. On some operating systems a character like "!" or "$" is a synonym for HOST.

After a HOST command finishes then the substitution variable _RC is defined. Its value is port specific and may contain a text message. On UNIX it is defined as "0" if the command is successful, or "1" if not. It may also be the operating exit status returned from the host program. On other platforms the value of _RC is not well defined and its value should not be relied on.

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

5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands

Variables are used in report titles to make each page relate to the data on that page, for example to give the product item that the report page describes. Any substitution variable can be used in a title command. However, the COLUMN NEW_VALUE or OLD_VALUE commands are often used to associate variables with column values in a report query.

Use NEW_VALUE variables in TTITLE and REPHEADER commands. Use OLD_VALUE variables in BTITLE and REPFOOTER commands. For example, the script:

   column last_name new_value ttnv old_value btov
   ttitle left 'First employee is: ' ttnv
   btitle left 'Last employee is: ' btov
   select last_name from employees where department_id = 60 order by last_name;

gives the output:

    First employee is: Austin
    LAST_NAME
    -------------------------
    Austin
    Ernst
    Hunold
    Lorentz
    Pataballa


    Last employee is: Pataballa

See TTITLE, BTITLE, REPHEADER, and REPFOOTER in the SQL*Plus User's Guide and Reference.

5.7.1 Using "&" Prefixes With Title Variables

The title commands (TTITLE, BTITLE, REPHEADER and REPFOOTER) substitute variables differently to most other commands. (The exceptions are the EXIT and SET SQLPROMPT commands, which are similar to the title commands). In general you do not need, and will not want, to put an "&" prefix before a variable name in a title command. For example, if your TTITLE command is:

    ttitle left 'Urgent: ' &2 ' Days High: ' &days

you should possibly change it to:

    ttitle left 'Urgent: ' 2 ' Days High: ' days

The guidelines for variables in titles are:

  • If you want the same value for a variable to be printed on every page then use an "&" prefix and put the variable inside a quoted string:

        accept mycustomer char prompt 'Enter your company name: '
        ttitle left 'Report generated for company &mycustomer'
        select last_name, job_id from employees order by job_id;
    
  • If you want each title to have data from the query that is unique to each report page then do not use an "&" prefix for the variable and do not put the variable inside quotes.

        column job_id new_value ji_nv noprint
        break on job_id skip page
        ttitle left 'Employees in job: ' ji_nv
        select last_name, job_id from employees order by job_id;
    

SQL*Plus substitution variables are expanded before each command is executed. After this happens in a title command, the resulting string is stored as the title text. What makes variables in titles special is that they need to be re-substituted for each page of query results. This is so the current COLUMN NEW_VALUE and OLD_VALUE substitution variable values are displayed on each page, customizing each title for the results displayed on its page. If "&" is used inadvertently or incorrectly to prefix title variables, it is possible to get double substitution. This is dependent on the variable's value and is easily overlooked when you write scripts.

Any non-quoted, non-keyword in a title is checked when the page is printed to see if it is a variable. If it is, its value is printed. If not, then the word is printed verbatim. This means that if you use "&myvar" in a title command, and the text substituted for it can itself be interpreted as another variable name then you get double variable substitution. For example, the script:

   define myvar = scottsvar
   ttitle left &myvar

   define scottsvar = Hello

   select * from dual;

causes the text "left scottsvar" to be stored as the title. When the title is printed on each page of the query this string is re-evaluated. The word "scottsvar" in the title is itself treated as a variable reference and substituted. The query output is:

   Hello
   D
   -
   X

Using "&" in titles most commonly causes a problem with the numeric variable names of the SQL*Plus script parameters. If the value of an arbitrary "&"-prefixed title variable is the same as a script parameter variable name, then double substitution will occur.

To display an "&" in a title, prefix it with the SET ESCAPE character. The ampersand (&) is stored as the title text and is not substituted when page titles are printed.

5.7.2 Variables and Text Spacing in Titles

Unquoted whitespace in titles is removed. Use whitespace instead of the SET CONCAT character to separate variables from text that should appear immediately adjacent. Use whitespace inside quotes to display a space. For example, the script:

    define myvar = 'ABC'
    ttitle left myvar myvar Text ' Other words'
    select ...;

gives a title of:

    ABCABCText Other words

5.8 UNDEFINE Command

Use UNDEFINE to remove a defined substitution variable:

    SQL> undefine myv
    SQL> define myv
    SP2-0135: symbol myv is UNDEFINED

Any variable not listed by DEFINE is said to be undefined.

Undefining unused substitution variables may help improve SQL*Plus performance because SQL*Plus can look up variables faster. This is especially true when variables are used in the SQLPROMPT.

See UNDEFINE in the SQL*Plus User's Guide and Reference for the command syntax.

5.9 WHENEVER Command

Substitution variables used for return statuses in WHENEVER OSERROR EXIT or WHENEVER SQLERROR EXIT commands follow the same general guidelines as variables in EXIT commands. Specifically, no ampersand (&) prefix is required, for example:

    SQL> whenever sqlerror exit myv

Be careful of using an ampersand (&) prefix for substitution variables in WHENEVER ... EXIT commands. Using an ampersand causes the current value of the variable at the time the WHENEVER command is run to be used, not the value that is in effect when the program later exits. For example, in the script:

    define myv = 5
    whenever sqlerror exit &myv
    define myv = 10
    -- This query should fail
    select * from non_existent_table;

the operating system return status is 5. This is because the WHENEVER statement is pre-processed and executed as if you typed:

    whenever sqlerror exit 5

However, if you remove the ampersand:

    define myv = 5
    whenever sqlerror exit myv

    define myv = 10
    -- This query should fail
    select * from non_existent_table;

the return status is "10" which is the value of "myv" at the time of exit.

See WHENEVER OSERROR and WHENEVER SQLERROR in the SQL*Plus User's Guide and Reference.

6 Substitution Variable Namespace, Types, Formats and Limits

6.1 Substitution Variable Namespace

In a SQL*Plus session there is just one global name space for substitution variables. If you reconnect using CONNECT, or run subscripts using "@", all variables ever defined are available for use and may be overridden or undefined.

When a child script finishes, all substitution variables it defined or changed are visible to the calling script. This is particularly noticeable when a subscript executed with "@" or START is given script parameters. The parameters "&1" etc. get redefined and the parent script sees the new values.

To minimize problems, and for general readability, use symbolic variable names for command parameters. All other references should use the new variable name instead of "&1". For example:

    define myuser = '&1'
    @myscript.sql King
    select first_name from employees where last_name = '&myuser';

The call to myscript.sql changes the value of "&1" to "King". By saving the original value of "&1" in "myuser" and using "&myuser" instead of "&1" in the SELECT, the query executes correctly.

6.2 Substitution Variable Types

The substitution variable types stored by SQL*Plus are:

  • CHAR
  • NUMBER
  • BINARY_FLOAT
  • BINARY_DOUBLE

The CHAR type is a generic text format similar to the database table VARCHAR2 column type. All variables created by:

  • DEFINE
  • from prompts for "&" variables
  • from script parameters

are of type CHAR. This ensures that values entered are substituted verbatim with no conversion loss.

Variables created by COLUMN NEW_VALUE or OLD_VALUE for the columns in Oracle number format will have the type NUMBER. These substitution variables are stored in Oracle's internal number representation as they are in the database. This allows display formats to be altered without any internal value loss. Substitution variables of BINARY_FLOAT and BINARY_DOUBLE types are similarly created for Oracle BINARY_FLOAT and BINARY_DOUBLE columns. These variables are stored in native machine representation. The CHAR type is used for NEW_VALUE and OLD_VALUE variables with all other column types.

There is no explicit DATE type. The DATE keyword in the ACCEPT command is used solely to allow correct format validation against a date format. Substitution variables created by ACCEPT ... DATE, or by COLUMN NEW_VALUE on a date column, are stored as type CHAR. For example:

    SQL> accept mydvar date format 'DD-MON-YYYY' prompt 'Enter a date: '
    Enter a date: 03-APR-2003
    SQL> define mydvar

    DEFINE MYDVAR              = "03-APR-2003" (CHAR)

If a variable already exists and is redefined, its old type is discarded and the new type used.

The type of a substitution variable is generally transparent. Substitution variables are weakly typed. For example, a COLUMN NEW_VALUE variable takes on the particular type of the named column in each new query. It may also change type during a query. For example, the type of a substitution variable used on a NUMBER column changes from NUMBER to CHAR when a NULL value is fetched. It changes back to NUMBER when the next numeric value is fetched.

No type comparison semantics are defined for any type since there is no direct comparison of variables. All variables are textually substituted before any SQL or PL/SQL statement that could do a comparison is executed.

6.3 Substitution Variable Formats

When a variable is substituted, or its value is shown by a DEFINE command, it is formatted as text before the command referencing the variable is finally executed.

CHAR variables are substituted verbatim.

NUMBER variables are formatted according to SET NUMWIDTH (by default) or SET NUMFORMAT (if you have explicitly set one):

The display format of a number can be changed even after the variable is created. To show this, first create a NUMBER variable. You cannot use DEFINE to do this because it makes the type of all new variables CHAR. Instead use a COLUMN NEW_VALUE command which inherits the NUMBER type from a NUMBER column:

    SQL> column c2 new_val m
    SQL> select 1.1 c2 from dual

            C2
    ----------
           1.1

    SQL> define m
    DEFINE M               =        1.1 (NUMBER)

Changing the format affects the display of the number but not the stored value:

    SQL> set numformat 99.990

    SQL> define m
    DEFINE M               =   1.100 (NUMBER)

For a list of format models, see Format Models in the Oracle Database SQL Reference 10g Release 1.

6.4 Substitution Variable Limits

The maximum number of substitution variables allowed is 2048. SQL*Plus gives an error an attempt is made to create more. The limit includes the predefined variables, however these can be undefined if necessary. Leaving a large number of unnecessarily defined variables can reduce the performance of SQL*Plus because variable lookups are slower.

A character substitution variable can be up to 240 bytes long.

A numeric substitution variable holds the full range of Oracle numbers. See NUMBER Datatype in the Oracle Database SQL Reference 10g Release 1.

When a command line undergoes variable substitution, the resulting line length can be no more than:

  • 3000 bytes if it is a line of SQL (like SELECT or INSERT) or PL/SQL text (like BEGIN or CREATE PROCEDURE)
  • 2499 bytes if it a line of a SQL*Plus command (like TTITLE or COLUMN)

Otherwise an error is displayed.

These limits may be lower in old versions of SQL*Plus.

7 iSQL*Plus and Substitution Variables

iSQL*Plus Release 10.1 interactively prompts for substitution values as the statement referencing them is executed. Each undefined variable is prompted for on its own HTML page. This is similar to command-line SQL*Plus.

Sometimes it is convenient to prompt for more than one value at a time. A separate HTML form can be used to prompt for all input. The values can be passed to iSQL*Plus as script parameters and referenced as substitution variables. See 7.2 iSQL*Plus Parameters.

7.1 iSQL*Plus 9i and SQL*Plus Substitution Variable Compatibility

The prompting model is different in iSQL*Plus 9i. When a script is run in iSQL*Plus Release 9.0 or 9.2, a single HTML page for undefined substitution variables is displayed. After you enter a value for each variable, the script executes and generates its results.

Some differences may be noticed between command-line SQL*Plus and iSQL*Plus Releases 9.0 or 9.2 when your script does one of the following:

  • contains DEFINE
  • contains UNDEFINE
  • contains SET DEFINE
  • uses "&" and "&&" prefixes for the same variable

These differences include being unnecessarily prompted in iSQL*Plus for values, and an empty string being used instead of the expected value. These problems do not occur in iSQL*Plus 10.1.

iSQL*Plus Releases 9.0 and 9.2 cannot prompt for input in the middle of an executing script. This is due to the way the iSQL*Plus server interacts with the SQL*Plus engine. (The engine is the same statement-executing code used by command-line SQL*Plus. The iSQL*Plus server generates the iSQL*Plus HTML interface and handles HTTP requests). If a script explicitly changes variable definitions, for example by undefining a variable, then iSQL*Plus cannot subsequently prompt for a new value. In this example an empty string is used instead.

In iSQL*Plus, parsing for "&" is performed twice, once by the iSQL*Plus server and once by the SQL*Plus engine. The iSQL*Plus server scans each script for "&" and "&&" references and creates a page with entry fields for undefined variables. When you have given values for the variables, they are transparently sent to the engine as DEFINE commands at the start of your script.

For example, if you start iSQL*Plus Release 9.x and enter:

    define mytable = employees

    break on &sortcol

    select &sortcol, salary
    from &mytable

    where salary > 12000
    order by &sortcol

the iSQL*Plus server:

  1. Finds the session's current values of SET DEFINE, SET ESCAPE and SET CONCAT. By default these are "&", OFF and "." respectively. (The values of SET ESCAPE and SET CONCAT are not relevant for this example.)
  2. Parses the script as if it were a single stream of arbitrary words. Since SET DEFINE is not OFF, all "&" variables that were undefined prior to the script being started are recorded. In this example these are "sortcol" and "mytable". No SQL*Plus statements are recognized or processed so the "define mytable" is ignored and the iSQL*Plus server records that an unknown variable "mytable" was referenced in the line "from &mytable".
  3. Creates a page prompting for values of "sortcol" and "mytable". Each variable name occurs only once on the page.
  4. After you enter a value for each variable on the page and click "OK", iSQL*Plus prepends explicit DEFINE commands for the variables and their values to your script. Because only single "&" prefixes were used in this example, iSQL*Plus also appends matching UNDEFINE commands at the end of the script. All extra commands are removed when the script finishes and do not display in the iSQL*Plus Work screen.

The modified script is then sent to the SQL*Plus engine for processing. The engine:

  1. Finds the session's current values of SET DEFINE, SET ESCAPE and SET CONCAT. These are still "&", OFF and "." respectively. Again, only the value of SET DEFINE is relevant to this example.
  2. When the script in this example is run then the two variables get defined by the new, transparently added, DEFINE commands. The script's original define of "mytable" runs next and its value "employees" is the one finally used by the rest of the script.

    Since SET DEFINE is not OFF, when "&sortcol" and "&mytable" are seen by the engine's preprocessor, the defined values are used.

    At the conclusion of the script, the two variables are undefined in the session by the explicit UNDEFINE commands of the transparently modified script.

The undesired behavior in this example is being prompted for a value for "mytable" despite the script explicitly defining it. However, the prompted value is not used and the correct results are displayed.

The iSQL*Plus server creates the page to enter variable values unless SET DEFINE is OFF before the script is submitted to the SQL*Plus engine for execution. To stop iSQL*Plus prompting for "&" values, make sure DEFINE is OFF. In iSQL*Plus Release 9.2 go to the System Variable page and change the DEFINE radio button to OFF. Then execute your script. In iSQL*Plus Release 9.0 you need to execute an explicit second script containing "SET DEFINE OFF" prior to entering and executing the main script.

If you want to use "&" prefixed variables in a script but know all values are generated in the script (using DEFINE, COLUMN NEW_VALUE or OLD_VALUE) then make sure SET DEFINE is OFF prior to executing the script (to stop the variable entry page being displayed). Also make the first line of the script "SET DEFINE ON" (to allow the SQL*Plus engine to do variable substitution using the script-created variables) and make the last line "SET DEFINE OFF" (to prevent subsequent re-executions of the script from unnecessarily creating the variable entry page).

If you want the main script to prompt for some values, but not others, explicitly define these latter variables before running your main script. The variables you explicitly define do not generate prompts when the main script is later executed. These variables can be defined by executing an initial script containing DEFINE commands. This initial script does not have to give valid values to the variables if the main script is later going to provide them. When the main script is run, iSQL*Plus sees that the variables have already been defined in the current session and does not include them on the variable entry page.

If variables you do want to be prompted for in a script are prefixed with "&&" then make sure the script undefines them at its end. Otherwise the variables become defined in the session. When the script is re-run iSQL*Plus sees the variables have a value and does not include them on the variable entry page. Explicitly undefining the variables allows the script to re-prompt for values each time it is run.

7.2 iSQL*Plus Parameters

Variables can be passed to iSQL*Plus dynamic reports using the URL syntax. These become defined as if they are named parameters. For example, the iSQL*Plus 10.1 URL:

    http://machine/isqlplus/dynamic?script=http://machine/mys.sql&myv=emp

would define "myv" as "emp" and then run mys.sql. The script can reference "&myv". In SQL*Plus 9.2, the equivalent URL is:

    http://machine/isqlplus?script=http://machine/mys.sql&myv=emp

For compatibility with command-line SQL*Plus scripts, you can use numeric names for parameters, e.g. "1=employees"

The "&" in the URL is the character for separating URL variables. It is only coincidentally the same as the default SQL*Plus substitution variable prefix and cannot be changed using SET DEFINE.

8 Substitution Variable Summary

SQL*Plus substitution variables can be used to customize reports and can be used instead of hard-coded text. Substitution variables can interact with bind and system variables. Substitution variables that have values stored are said to be defined.

  • Substitution variables can be defined explicitly:

    • with ACCEPT

    • with DEFINE

    • by passing parameters to SQL*Plus or iSQL*Plus scripts

  • Substitution variables can be defined implicitly:

    • with a COLUMN NEW_VALUE or COLUMN OLD_VALUE command

    • by using a double ampersand (&&) prefix on an undefined variable

  • Substitution variables references have an "&" or "&&" prefix.

  • If a variable is referenced but is not defined, SQL*Plus stops and prompts for a value.

  • Substitution variable references are pre-processed and substituted before the command is otherwise parsed and executed.

  • Substitution variables have a current type, such as CHAR. Substitution variables are weakly typed and change type as necessary.

  • Values are substituted as text. So even if the type of a variable is NUMBER, its value is formatted as a text string, substituted, and then the command executed.

  • In titles, EXIT and SET SQLPROMPT, substitution variables do not have to be prefixed with "&" or "&&".

  • The predefined substitution variables are named with a leading underscore (_). They can be undefined or redefined.

9 Substitution Variable Examples

9.1 Setting a Substitution Variable's Value

A substitution variable can be set in several ways. The common ways are given below.

  1. The DEFINE command sets an explicit value:

        define myv = 'King'
    

    This creates a character variable "myv" set to the value "King".

  2. The ACCEPT command:

        accept myv char prompt 'Enter a last name: '
    

    prompts you for a value and creates a character variable "myv" set to the text you enter.

  3. Using "&&" before an undefined variable prompts you for a value and uses that value in the statement:

        select first_name from employees where last_name = '&&myuser';
    

    If the substitution variable "myuser" is not already defined then this statement creates "myuser" and sets it to the value you enter.

  4. Using COLUMN NEW_VALUE to set a substitution variable to a value stored in the database:

        column last_name new_value mynv
        select last_name from employees where employee_id = 100;
    

    This creates a substitution variable "mynv" set to the value in the "last_name" column.

9.2 Using a Substitution Variable

Once a substitution variable has a value, it can be referenced by prefixing the variable name with an ampersand (&).

If the variable "myv" is already defined it can be used like:

    select employee_id from employees where last_name = '&myv';

9.3 Finding All Defined Substitution Variables

The DEFINE command with no parameters shows all defined substitution variables, their values, and their types. For example:

    define

might give:

    DEFINE MYV             = "King" (CHAR)
    ...

9.4 Inserting Data Containing "&" Without Being Prompted

There are two ways to make an "&" be treated as text and not cause a prompt. The first turns all variable substitution off:

    set define off
    create table mytable (c1 varchar2(20));
    insert into mytable (c1) values ('thick & thin');

The INSERT statement stores the text "thick & thin" in the table.

The second method is useful for ignoring individual occurrences of "&" while allowing others to prefix substitution variables:

    set escape \
    create table mytable (c1 varchar2(20));
    insert into mytable (c1) values ('thick \& thin');
    insert into mytable (c1) values ('&mysubvar');

The first INSERT statement in this method stores the text "thick & thin" in the table. The second INSERT causes SQL*Plus to prompt you for a value, which is then stored.

9.5 Putting the Current Date in a Spool File Name

Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

    column dcol new_value mydate noprint
    select to_char(sysdate,'YYYYMMDD') dcol from dual;

    spool &mydate.report.txt
    -- my report goes here
    select last_name from employees;
    spool off

In this example the first query puts the date in the substitution variable "mydate". There is no visible output from this query because of the NOPRINT option in the COLUMN command. In the SPOOL command the first period (.) indicates the end of the variable name and is not included in the resulting string. If "mydate" contained "20030120" from the first query then the spool file name would be "20030120report.txt".

You can use this technique to build up any string for the file name.

The period is the default value of SET CONCAT. If you have assigned another character then use it instead of a period to end the substitution variable name.

9.6 Appending Alphanumeric Characters Immediately After a Substitution Variable

If you wish to append alphanumeric characters immediately after a substitution variable, use the value of SET CONCAT to separate the variable name from the following text. The default value of SET CONCAT is a single period (.). For example:

    define mycity = Melbourne
    spool &mycity.Australia.txt

creates a file with the name "MelbourneAustralia.txt"

9.7 Putting a Period After a Substitution Variable

If SET CONCAT is a period (.) and you want to append a period immediately after a substitution variable then use two periods together. For example:

    define mycity = Melbourne
    spool &mycity..log

is the same as:

    spool Melbourne.log

9.8 Using a Fixed Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER

This example makes every page of a report have exactly the same heading. It can be used for TTITLE, BTITLE, REPHEADER or REPFOOTER commands.

In a TTITLE command prefix the variable name "dept" with "&" and place it inside a quoted string:

    define dept = '60'
    ttitle left 'Salaries for department &dept'
    select last_name, salary from employees where department_id = &dept;

9.9 Using a Changing Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER

This example uses a different title on every page of a report. Each title contains a value derived from query results shown on that particular page.

In a TTITLE command do not put an "&" before the variable name "dv". Put the variable name outside a quoted string:

    column department_id new_value dv noprint
    ttitle left 'Members of department ' dv
    break on department_id skip page
    select department_id, last_name from employees order by department_id, last_name;

In a BTITLE or REPFOOTER command use a COLUMN OLD_VALUE variable instead of a COLUMN NEW_VALUE variable.

9.10 Using the Value of a Bind Variable in a SQL*Plus Command Like SPOOL

If you want to use the value of a bind variable in a SQL*Plus command it must first be copied to a substitution variable.

SQL*Plus commands like SPOOL, SET and TTITLE are executed in the SQL*Plus program and are not passed to the database for execution. Because of this they do not understand bind variables.

To use a bind variable's value as the name of a spool file:

    -- Set a bind variable to a text string
    variable mybindvar varchar2(20)
    begin
      :mybindvar := 'myspoolfilename';
    end;

    -- Transfer the value from the bind variable to the substitution variable
    column mc new_value mysubvar noprint
    select :mybindvar mc from dual;

    -- Use the substitution variable
    spool &mysubvar..txt
    select * from employees;
    spool off

9.11 Passing Parameters to SQL*Plus Substitution Variables

You can pass parameters on the command line to a SQL*Plus script:

    sqlplus hr/my_password @myscript.html employees "De Haan"

They can be referenced in the script using "&1" and "&2". For example, myscript.sql could be:

    set verify off
    select employee_id from &1 where last_name = '&2';

Here the "SET VERIFY OFF" command stops SQL*Plus from echoing the SQL statement before and after the variables are substituted. The query returns the employee identifier for the employee "De Haan" from the "employees" table.

Parameters can also be passed to scripts called within SQL*Plus:

    SQL> @myscript.sql employees "De Haan"

9.12 Passing Operating System Variables to SQL*Plus

You can pass an operating system variable to a SQL*Plus script as a command line parameter. For example, on UNIX:

    sqlplus hr/my_password @myscript.sql $USER

or in a Windows command window:

    sqlplus hr/my_password @myscript.sql %USERNAME%

The script myscript.sql could reference the substitution variable "&1" to see the passed name.

9.13 Passing a Value to a PL/SQL Procedure From the Command Line

If you create a procedure "myproc":

    create or replace procedure myproc (p1 in number) as
    begin
      dbms_output.put_line('The number is '||p1);
    end;

and myscript.sql contains:

    begin
      myproc(&1);
    end;
    /

then calling:

    sqlplus hr/my_password @myscript.sql 88

executes the script as if it is:

    begin
      myproc(88);
    end;
    /

This method does not work if the parameter "p1" to "myproc" is "IN OUT". The variable reference is pre-processed and is effectively a hard coded value which cannot contain an OUT value. To get around this you can assign the substitution variable to a bind variable. The script myscript.sql becomes:

    variable mybindvar number
    begin
      :mybindvar := &1;
      myproc(:mybindvar);
    end;
    /

9.14 Allowing Script Parameters to be Optional and Have a Default Value

The goal is to create a script which accepts an optional parameter. If a parameter is passed from the command line then its value should be used. However, if there is no parameter, then SQL*Plus should ask for a value with a customized prompt.

Perhaps the closest solution is with a PROMPT/DEFINE sequence like this. If myscript.sql is:

    -- Name: myscript.sql
    prompt Enter a value for PAGESIZE
    set termout off
    define mypar = &1
    set termout on

    prompt Setting PAGESIZE to &mypar
    set pagesize &mypar

    select last_name from employees where rownum < 20;
    exit

you can call the script with or without a parameter. If you enter "12" at the prompt your screen looks like:

    % sqlplus hr/my_password @myscript.sql

    SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 5 15:19:40 2003
    ...

    Enter a value for PAGESIZE
    12
    Setting PAGESIZE to 12

    LAST_NAME
    -------------------------
    King
    Kochhar
    De Haan
    ...

or if you call it with a parameter "8":

    % sqlplus hr/my_password @myscript.sql 8

    SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 5 15:20:38 2003
    ...


    Enter a value for PAGESIZE
    Setting PAGESIZE to 8

    LAST_NAME
    -------------------------
    King
    Kochhar
    De Haan
    ...

Note when you pass a parameter the PROMPT text is still displayed, but you do not enter a value. The PROMPT command is the SQL*Plus "echo" or "print" statement. (It does not read input).

The only occurrence of "&1" should be where "mypar" is defined. All other references to the parameter should use "&mypar" or "&&mypar".

9.15 Passing a Value to an iSQL*Plus Dynamic Report for the Web

Variables can be passed as URL parameters to an iSQL*Plus report. For example with iSQL*Plus 10.1:

    http://machine/isqlplus/dynamic?script=http://machine/mys.sql&myv=emp&v2=dept

or in iSQL*Plus 9i:

    http://machine/isqlplus?script=http://machine/mys.sql&myv=emp&v2=dept

These define the substitution variable "&myv" as "emp" and the substitution variable "v2" as "dept" before running the script mys.sql. The script can use "&myv" and "&v2" anywhere substitution variables are allowed.

Note that iSQL*Plus prompts for a username and password before defining the variables and running the script.

9.16 Customizing Parameter Prompts for an iSQL*Plus Dynamic Report for the Web

A customized HTML form can be used to enter and validate variables to be passed to an iSQL*Plus Release 9.2 dynamic report.

Create and save a SQL*Plus script employee_name.sql on your Oracle HTTP Server. Check it can be loaded into a web browser to verify iSQL*Plus is able to access the file over HTTP. The script is:

    -- Name: employee_name.sql
    set verify off
    set pagesize 200
    set feedback off
    prompt Employee Details for Employee(s) with Last Name like &last_name%
    select *
    from employees
    where upper(last_name) like upper('&last_name%')
    /

Create an HTML file myreport.html on your Oracle HTTP Server. The file is:

    <html>
    <head>

      <title>iSQL*Plus Dynamic Report - Query by Last Name</title>
    </head>
    <body>
      <h1>iSQL*Plus Dynamic Report - Query by Last Name</h1>
      <form method=get action="http://machine/isqlplus">
      <input type="hidden" name="script"
            value="http://machine/employee_name.sql">
        Enter last name of employee:
        <input type="text" name="last_name" size="20">
        <input type="submit" value="Run Report">
        </form>
    </body>
    </html>

Replace "http://machine/" with the appropriate host name, domain name and port number of your Oracle HTTP Server, for example, "http://machine.oracle.com:7777/".

The name of the INPUT TYPE should be the same as the substitution variable name in the SQL*Plus script. For example, the input field:

    <input type="text" name="last_name" size="20">

maps to the substitution variable "&last_name" in employee_name.sql.

Load myreport.html in your web browser. Enter a name or partial name in the text field, for example, "Fay". Click the Run Report button. iSQL*Plus will prompt for database connection details and then execute the script. The Employee Details report is displayed in your web browser.

You could add Javascript to the HTML form to do any desired browser-side validation of the values entered.

9.17 Using a Variable for the SQL*Plus Return Status

To use the value of a substitution variable called "myv" as the SQL*Plus return status, use:

    EXIT myv

No ampersand (&) prefix is required before the substitution variable name.

A numeric bind variable requires a colon (:) prefix:

    EXIT :mybv

9.18 Putting the Username and Database in the Prompt

In SQL*Plus 10g add this to your glogin.sql or login.sql:

    set sqlprompt "_user'@'_connect_identifier:SQL> "

For customized prompts that query the database make sure to explicitly DEFINE any referenced substitution variables. Glogin.sql and login.sql can get run when there is no database connection. Defining variables prevents the user being prompted for values when the query fails and the variables do not get defined by it:

    set termout off
    define myv = 'Not connected'
    column myc new_value myv
    select user||'@'||global_name myc from global_name;
    set sqlprompt '&myv:SQL> '
    set termout on

SQL*Plus 9.2 and earlier don't re-execute glogin.sql and login.sql after CONNECT commands. Also variables in the SQLPROMPT are not dynamically substituted. It is possible to use the query script given above, but beware that the prompt will only be valid for the original connection.

Comments:

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today