Search notes:

SQL*Plus: DEFINE

define assigns a value to a substitution variable (user-defined or predefined) or prints the value that is a assigned to the variable.
--
--  Assign the value val to the variable var
--
define var = 'val'
The variable is substituted with its value by prepending it with an ampersand &var:
--
-- Execute a statement and substitute the value of var with its value
--
select
   '&var'  as theAssignedValue
from
   dual;
Without assigning a value to a variable (define var), the command prints the currently assigned value:
define var
define by itself prints all currently assigned values:
define

Suppressing old and new values

By default, when SQL*Plus substitutes a defined variable, it prints the original and the substituted lines:
SQL> define num=42
SQL> select &num from dual;
old   1: select &num from dual
new   1: select 42 from dual

        42
----------
        42
This behavior can be turned of by setting verify to off:
SQL> set verify off
SQL> select &num from dual;

        42
----------
        42

Multi-line values

The value of a variable can be spread over multiple lines by terminating a line to be continued with a hyphen:
set verify on

define cols="     -
  a  number     , -
  b  varchar(10), -
  c  date"

create table table_1 (&cols);
create table table_2 (&cols);
However, this technique does not retain the new line character:
SQL> create table table_1 (&cols);
old   1: create table table_1 (&cols)
new   1: create table table_1 (        a  number     ,    b  varchar(10),    c  date)
SQL> create table table_2 (&cols);
old   1: create table table_2 (&cols)
new   1: create table table_2 (        a  number     ,    b  varchar(10),    c  date)

Maximum size

For an obscure reason, the maximum size of a value in a variable is 240 characters (19c):
SQL> define too_long= "-
123456789012345678901234567890-
123456789012345678901234567890-
123456789012345678901234567890-
123456789012345678901234567890-
123456789012345678901234567890-
123456789012345678901234567890-
123456789012345678901234567890-
123456789012345678901234567890"
string beginning "" 12345678..." is too long. maximum size is 240 characters.
However, Donna Kray and Seth Goodman pointed me towards a workaround for this limitation which allows to assign values longer than 240 characters:
column tempalias new_value not_too_long noprint

select
   '123456789012345678901234567890'||
   '123456789012345678901234567890'||
   '123456789012345678901234567890'||
   '123456789012345678901234567890'||
   '123456789012345678901234567890'||
   '123456789012345678901234567890'||
   '123456789012345678901234567890'||
   '123456789012345678901234567890'||
   '123456789012345678901234567890'||
   '123456789012345678901234567890'   as tempalias
from
   dual;

--define not_too_long

set serveroutput on 
exec dbms_output.put_line('Length is: ' || length('&not_too_long'));

Interactively assigning values to a variables

A value can interactively be assigned to a varable with the accept command.
This is sometimes useful in SQL scripts being run in SQL*Plus.

Predefined variables

Besides user defined variables, there are also some predefined variables:
_CONNECT_IDENTIFIER The connection-identifier with which the connection was established.
_DATE Evaluates to the current date in nls_date_format format
_EDITOR Specifies the editor that is fired up with the edit command.
_O_VERSION and _O_RELEASE Version and release of the currently installed database
_PRIVILEGE Privilege level of current connection
_RC
_SQLPLUS_RELEASE Release number of SQL*Plus component
_USER Name of user that made the connection
_SQL_ID sql_id of the (most recent?) SQL statement executed

See also

Using variables in SQL*Plus.
set define specifies the character that indicates a substitution varible by prefixing it with the specified character.

Index