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
                  || 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.

Bookmark the permalink.

2 Responses to afiedt.buf whats with that

  1. Stephan Borsodi says:

    Clever idea … here’s a variation for FILE_NAME with timestamp (target OS: Windows):

    select ‘c:\temp\sql_’ || sys_context (‘USERENV’, ‘SESSION_USER’ ) || ‘_’
    || to_char(sysdate, ‘RRMMDD_HH24-MI-SS’) || ‘_’
    || sys_context (‘USERENV’, ‘SESSIONID’ )
    || ‘.sql’ as FILE_NAME
    from DUAL;

    • trudkin says:

      A date with time can be handy if it is useful to track by date. But the file name is set on log in and I have sessions opened for weeks and date would become stale. But by all means if date is a better way to track go for it, Thanks.

Leave a Reply