afiedt.buf whats with that

The file afiedt.buf  is the default buffer for editing files from SQL*Plus.  It is a throw back to long ago before SQL*Plus existed.  Before SQL*Plus there was User-Friendly-Interface.  Then the edit buffer was ufiedt.buf.   Then an early name for SQL*Plus was Advanced-User-Friendly-Interface and the edit buffer became afiedt.buf.    AUFI got changed to sqlplus but the buffer name stayed the same.

There are several problems with the name afiedt.buf.   There is only one name,  but it ends up in the current working directory.   The long sql you worked on for a new project gets over written when you start a new session and forgot to save.  You cannot write to the file as another session has the file open.  Then also your editor does not know that the file should be treated as SQL.  So what to do?

-- Part of login.sql
define _EDITOR = "vim -c 'colorscheme evening'  "

--  make the name of the edit buffer a . sql to editor knows to highlight
define efile=sqltmp1.SQL

-- now we make it unique to the session and user in /tmp for system cleanup
column file_name new_value efile
SELECT '/tmp/sql' || SYS_CONTEXT ('USERENV', 'SESSION_USER' ) 
                  || SYS_CONTEXT ('USERENV', 'SESSIONID' ) 
                  || '.sql' file_name  from dual;
set editfile '&efile'

The first define sets the editor to vim,  I use SlickEdit (vs) myself but that’s another blog. However, any on Linux would have vi or a derivative.  The next define just sets a place holder for the variable efile.    The column command lets efile be the target of a select.  The select populates efile with the string  /tmp/sqlUSER#####.sql    Lastly the set sets editfile to the value of efile.   Now each sessions get it’s own file.  The sql statement from five sessions ago is still there (at least until tmp gets cleaned up; I actually use /var/sql).

I hope this makes your experience in SQL*Plus a little better.