Not everyone wants to use sqlplus’ built in editor. It is a bit of a pain and not at all WYSIWYG. Thankfully, changing sqlplus to use another editor is an easy process where you change the _editor session variable to another value. For a more permanent change, you can alternatively change the setting in the glogin.sql file that will make the change every time you start sqlplus.

Linux

In Linux, using DEFINE in sqlplus to change the editor to VIM is extremely handy if you like to use VIM to edit documents. You can specify any text editor on your system to edit files using the _EDITOR session variable. Once it is set, when you use the ed command, it will open the SQL statement in VIM.

SQL> define _editor=vi
SQL> ed

If the editor is not in your PATH, then you will need to use the entire path and executable to the editor. If this program isn’t in your path, it probably should be.

SQL> define _editor=/usr/bin/vim/vi

To change this setting so it works every time you login without defining it yourself on the command line, change the file found at $ORACLE_HOME/sqlplus/admin/glogin.sql. Within that that file, you will find a line with define _editor. Change it as you would on the command line.

Windows

In Windows, the default editor for sqlplus is Notepad. When notepad opens, it opens the afiedt.buf file. You can make changes, then you save and close notepad. Your changes are moved to sqlplus and the edited SQL statement is displayed immediately. You can change the editor to be whichever text editor you prefer, such as notepad++. If notepad isn’t in your PATH, you can either add it to your path or put the entire path in the _editor session variable.

SQL> define _editor=notepad++
SQL> ed
Wrote file afiedt.buf
'notepad++' is not recognized as an internal or external command,
operable program or batch file.

 1* select count(*) from tbl_locations
SQL> define _editor=c:appsnotepad++notepad++.exe;
SQL> define;
DEFINE _DATE = "24-JUN-15" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "XE" (CHAR)
DEFINE _USER = "RSIMS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR)
DEFINE _EDITOR = "c:appsnotepad++notepad++.exe" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Express Edition Release 11.2.0.2.0
 - 64bit Production" (CHAR)
DEFINE _O_RELEASE = "1102000200" (CHAR)
DEFINE _RC = "1" (CHAR)
SQL> ed
Wrote file afiedt.buf

Once you define _editor with the path and executable for Notepad++, when you edit on the sqlplus command line, it will open your most recent SQL statement in notepad++. When you save in Notepad++, it will return you to sqlplus and show the command in its current format.

Note: any program that you use in Windows for the sqlplus editor, go into the properties of the executable. Under Compatability, find the section Privilege Level. Check on Run this program as an administrator.

Just like in Linux, to change this setting so it works every time you login without defining it yourself on the command line, change the file found at $ORACLE_HOME/sqlplus/admin/glogin.sql. Within that that file, you will find a line with define _editor. Change it as you would on the command line.