René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

define [SQL*Plus]

define sets a user variable or displays its value.
The following command sets the variable varname to foo. 
define varname=foo
From now on, whenever SQL*Plus encounters a &varname, it replaces it with foo. The ampersand is the default prefix for defined variables. BTW, this default can be changed with set define.
The following command displays the value of varname. 
define varname
Alternatively, the value of varname can be displayed with (assuming the escape character was set to the ampersand (&): 
prompt &varname
A defined variable can be undefined with undefine.

Suppressing old and new values

By default, when SQL*Plus encounters a defined variable, it prints the original line and the line with the substitued values: 
define s="'some string'"
define d=dual

select &s from &d;

old   1: select &s from &d
new   1: select 'some string' from dual

'SOMESTRING
-----------
some string
This behavior can be turned with setting verify to off: 
set verify off

define thousand=1000
define twelve  =  12
define plus    =   +

select &thousand &plus &twelve from dual;

   1000+12
----------
      1012

Multiple lines

A variable can be defined over multiple lines if the line ends in 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, the value of the defined variable doesn't contain the new lines: 
old   1: create table table_1 (&cols)
new   1: create table table_1 (        a  number     ,    b  varchar(10),    c  date)

Table created.

old   1: create table table_2 (&cols)
new   1: create table table_2 (        a  number     ,    b  varchar(10),    c  date)

Table created.

Maximum size

For some obscure reason, the value of a defined variable cannot be longer than 240 characters (10g): 
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 know about a work around to that limitation. I'd like to thank both of them to share with me. Here's their work around: 
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'));

Length is: 300

Setting a value of a variable interactively

A user variable can (mostly useful in a script) be set interactively with accept.

Predefined variables

_connect_identifier

_date

_editor

The special user variable _editor specifies which editor is started when someone uses edit.

_o_version

_o_release

_privilege

_sqlplus_release

_user

Links

See also set define.