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

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1741086663, '3.145.170.61', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/SQL-Plus/define/index(175): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78