Oracle database

The DEFINE command

Written by meda

The DEFINE  command

Oracle define command enables us to define  and create session level variables.

when we select and run  SQL statements,  these session variables are referenced using double ampersand substitution(&&).

Before defining and creating these variables, we have to make sure that the oracle SQL client tools such as SQL*PLUS and SQL developer ,that we are working on supports or is enabled to save session variables.

We use the SET command to enable and disable the SQL client tools for session variables.

on SQL*PLUS run the following command :

---To enable SQL*PLUS to save session variables

SQL> SET DEFINE ON

---To disable SQL*PLUS to switch off define command

SQL> SET DEFINE OFF

And the  syntax of the command is:

SQL > DEFINE;

OR

SQL > DEFINE <variable>= value;

Let’s see  also an example of DEFINE commands, using SQL*PLUS command-line tool:

---To list all variables currently defined in your session
D:\>cd app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 24 17:04:04 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn mereba/mereba@orcl
Connected.

SQL> DEFINE;
DEFINE _DATE = "24-FEB-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "MEREBA" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE = "1202000100" (CHAR)

---To explicitly define initial value for the variable referenced as a substitution variable
SQL> define salvalue=8000;
SQL> DEFINE;
DEFINE _DATE = "24-FEB-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "MEREBA" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE = "1202000100" (CHAR)
DEFINE SALVAL = "8000" (CHAR)

As you can see in the query above, the 'SALVAL' session variable has a value of 8000.

SQL> select emp_id, &&salvalue from emp_sal;
old 1: select emp_id, &&salvalue from emp_sal
new 1: select emp_id, 8000 from emp_sal

EMP_ID 8000
---------- ----------
5      8000


---To prompt a user to enter the column name for the session variable preceded by double ampersand substitution

Example:

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 24 17:47:32 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn mereba/mereba@orcl
Connected.
SQL> set define on;
SQL> select emp_id, &&colmn_name from emp_sal;
Enter value for colmn_name: salary
old 1: select emp_id, &&colmn_name from emp_sal
new 1: select emp_id, salary from emp_sal

EMP_ID SALARY
---------- ----------
5 5000

SQL>

And to UNDEFINE a session variable:

SQL> UNDEFINE <variable>

Example to UNDEFINE the ‘DEFINE _DATE‘ session variable above:

SQL> UNDEFINE DEFINE _DATE;
SQL> DEFINE;
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "MEREBA" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE = "1202000100" (CHAR)

As you can see from the list, the ‘DEFINE _DATE‘ session variable is removed from the list.

N.B: The session variables exists only for the duration of the session. Besides, we can also undefine the session variable while the session is still on.