Connecting
Typical:
using SID: sqlplus user/password@SID
Service Name (from tnsnames.ora using full connect):
sqlplus user/password@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=<host>)(Port=<PORT>)) (CONNECT_DATA=SERVICE_NAME=<servicename>)))"
Using Substitution Variables
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:
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:
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 = '&myname'
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).
The "new" line of the verification shows the query executes as if you originally entered:
SQL> select employee_id from employees where last_name = 'King';
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.
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.
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 value is discarded and the variable remains undefined. If the variable is referenced twice, even in the same command, 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
-----------
195Storing 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". When the SELECT finishes, the variable "mynv" holds the last retrieved value from column "last_name":
SQL> define mynv
DEFINE mynv = "King" (CHAR)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.
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:
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.sqlCommands 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.
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:
is the same as:
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".
