I love to use Emacs, mostly for org-mode. I also use Notepad++ as an text editor (when using MS Windows), although I would like to use Emacs more. I noticed I choose Notepad++ when I quickly need an editor buffer to paste some text to because Notepad++ has this „New“ icon in its toolbar that creates a new buffer quickly.

Emacs is easily extendible, so how about creating a „quick new buffer“ function myself? It is really easy.

To create a new buffer you use Ctrl-x b (for function switch-to-buffer) and type a buffer name. But even typing in a name is too much for me. Automatically name the buffer „New“ and add a unique number to it! There is a function called rename-uniquely which does what it says. Now we can create a function make-new-buffer:

  1. (defun make-new-buffer ()
  2.   "makes a new buffer, uniquely named"
  3.   (interactive)
  4.   (switch-to-buffer "New")
  5.   (rename-uniquely))

Super easy: it creates a buffer named „New“ and renames it so that it has a unique name. Bind this to a key, I use F5:

  1. (global-set-key (kbd "<f5>") 'make-new-buffer)

Put this code in your .emacs or whatever.

When you have a bunch of buffers called „New<2>“, „New<3>“ etc., take a look at buffer-menu. You get a list of all buffers. Now you can operate on them. For instance, mark a buffer for deletion with d, repeat for other buffers and execute the command with x. Now you have killed a bunch of buffers quickly and easily.

If you are new to customizing keybindings and writing custom functions, Sacha Chua wrote a nice article „Read Lisp Tweak Emacs (Beginner 3/4): How can I make things more convenient?

Update: Xah Lee over at ErgoEmacs has published a similar article. He came up with the following, slightly more complex function:

  1. (defun xah-new-empty-buffer ()
  2.   "Open a new empty buffer."
  3.   (interactive)
  4.   (let ((buf (generate-new-buffer "untitled")))
  5.     (switch-to-buffer buf)
  6.     (funcall (and initial-major-mode))
  7.     (setq buffer-offer-save t)))

What is the difference? It uses generate-new-buffer to generate a new buffer and make sure it has a unique name (line 4). Then it switches to that buffer (line 5). It sets the major mode of the new buffer to the one declared in variable initial-major-mode, if any (line 6). Setting buffer-offer-save to t let Emacs ask you for saving that buffer before exiting Emacs (line 7).

The outstanding Emacs Org-mode enables you to include source code blocks our text. Org-Babel then enables you to execute that code.
Yesterday I came across the Orgmode cookbook. In a section it shows how to write a SQL statement and execute it agains a postgresql database. Very nice I thought, but what about Oracle DB? Because that what I use at work.

No official support

Oracle DB is not supported as of now. There is a proposed patch. It adds the call to sqlplus, Oracle DBs command line client. But on the mailing list there was no discussion about it. 🙁 One trick it applies is to add the „.sql“ suffix to the input file to sqlplus.

  1. (in-file (org-babel-temp-file "sql-in-" ".sql"))

So now I got the following code that is an additional branch in the org-babel-execute:sql function:

  1. ('oracle (format
  2.                               "sqlplus -s %s"
  3.                   (cond ( (and dbuser dbhost dbpassword)
  4.                       (format "%s/%s@%s @%s > %s"
  5.                           dbuser dbpassword dbhost
  6.                           (org-babel-process-file-name in-file)
  7.                           (org-babel-process-file-name out-file)
  8.                           )
  9.                       )
  10.                     ;; user specified dbuser, dbpassword, & dbhost
  11.                     ( (or dbuser dbhost dbpassword)
  12.                       (error (format "Must specify dbuser/dbpassword@dbhost, missing %s %s %s"
  13.                              (if dbuser "" ":dbuser")
  14.                              (if dbpassword "" ":dbpassword")
  15.                              (if dbhost "" ":dbhost")
  16.                              )
  17.                          )
  18.                       ) ;; if one specified, they all must be
  19.                     ( t
  20.                       (format "%s @%s > %s"
  21.                           (or cmdline "")
  22.                           (org-babel-process-file-name in-file)
  23.                           (org-babel-process-file-name out-file)
  24.                           )
  25.                       ))))

Formating the result

Now I got the SQL executing against my db. The output was a bit weired. I experimented with sqlplus formatting with mixed results.

I consulted the elisp source. The result is converted into an orgmode table using TAB as a delimiter. In an example that had two rather long columns with short column names, sqlplus used several tabs between the two column names and so screwed the output quite a bit.

I don’t have a good solution for that. My workaround is to use comma as a column seperator (set colsep ',') and let orgmode guess that. For that I had to touch one line:

  1. (org-table-import out-file (if (eq (intern engine) 'oracle) nil '(16)))

Also I deleted the first empty line using an oracle-branch in org-babel-result-cond/with-temp-buffer:

  1. ((eq (intern engine) 'oracle)
  2.        (with-temp-buffer
  3.            (insert-file-contents out-file)
  4.            (goto-char (point-min))
  5.            (kill-line)
  6.            (write-file out-file)))

Conclusion

In principle, it works. How nice formatted the output it is, depends on your query. When just documenting a query, as an alternative to orgmode I use SQL-Mode and format the result myself.