Setting Up SQL*Plus in emacs

Emacs is a delightfully complex editor and, for those who love it, the only way they want to interact with the world. I’m a convert. I also work with relational databases. I wanted to add more functionality to emacs – I wanted to query Oracle directly from my text editor.

Install SQL*Plus

To get SQL*Plus working, grab a copy of the Oracle Instant Client and get cracking. Oracle have already documented this process, and your favorite distribution has probably done so, too. Ubuntu and Arch Linux have instructions available. And, since Oracle Linux and Red Hat share a common base, they can share packages, too.

Install the SqlPlus emacs library.

If you’re not familiar with emacs packages, run M-x list-packages and then search (C-s) for sqlplus. Once you find it, mark it for installation – put the cursor on the sqlplus line and press i. And then install it – press x.

Then you just have to wait for the sqlplus emacs library to be installed.

Configure emacs

Unfortunately, emacs doesn’t pick up your environment variables. That means it’s not going to see the location of the SQL*Plus executable, unless you’ve done something crafty, like create a symlink. Instead, you’re going to have to do something more to get things working.

Step one, edit your emacs customization file. Mine is ~/.emacs.d/jeremiah.el, but yours might be something else.

As a result of setting up the Oracle Instant Client, you should have a bunch of Oracle related environment variables. You’ll have to read those variables in yourself to get things working.

;;; ---------------------------------------------------------------------
;;; Set up for Oracle
(let ((oracle-home (shell-command-to-string ". ~/.zshrc; echo -n $ORACLE_HOME")))
  (if oracle-home
      (setenv "ORACLE_HOME" oracle-home))
  (setenv "PATH" (concat (getenv "PATH")
                         (format "%s/%s" oracle-home "bin")))
  (add-to-list 'exec-path (format "%s/%s" oracle-home "bin"))
)

This assumes that you’re using ZSH. Obviously, you should change the .zshrc reference to the shell configuration file of your choice.

After you’ve added this to your emacs customization file, you can either tell emacs to reload the buffer or to just evaluate that line of text. To evaluate the line, place the cursor at the end of the (let ... ) and then run C-x C-e to evaluate the current line. Sometimes it’s easier to restart emacs.

You can test it by running M-x sqlplus to load sqlplus mode.

Running A Query

First off, switch over to sqlplus mode with M-x sqlplus. Emacs is going to prompt you for your connection information. Once you’re all connected, it should look something like this:

Type your query into the top buffer and execute it by pressing C-Enter. You should see results that look like this:

Some data types don’t display well, but if you’re running quick and dirty investigatory queries, this is a great way to stay in your primary editor until you need to switch into a different tool. Heck, you can even display an explain plan, so maybe you won’t need to leave at all!

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu