Emacs and Oracle

After learning so much about Emacs to get my Rails setup working the way I wanted it, I though it was about time I figured out how to use SQLPLUS in Emacs too. Turns out it was really easy, as sql-mode is built right in, no .emacs changes required or extra files to download.

To connect to SQLPLUS in an emacs buffer, fire up emacs and type ALT-x sql-oracle. Emacs will prompt you for a username and password and a database to connect to. The database name will need to be in your local tnsnames.ora – in other words, if you cannot sqlplus username/password@database, emacs will not be able to connect either.

You can now enter commands into SQLPLUS just like normal, only inside Emacs. The thing that always frustrated me about SQLPLUS is that there is no command history recall, so I always found myself writing a query in an editor, and copying and pasting into SQLPLUS. Not anymore … thanks to Emacs.

When you enter sql-oracle mode and login, Emacs splits your window in two. You can then edit your SQL in the original window and send the query to SQLPLUS in the other buffer in (at least) one of two ways:

• Send the entire buffer by typing ALT-x sql-send-buffer
• Select a region to send by typing ALT-x sql-send-region, or if you buffer is in sql-mode, type CTRL-C CTRL-r

You can of course bind a key sequence to each of these commands to save on the typing if you use them a lot!

If you have an SQLPLUS buffer open for a while, it could get very large. To truncate it, use the command ALT-x comint-truncate-buffer, or add the following to your .emacs:

(add-hook 'sql-interactive-mode-hook
              (function (lambda ()
                          (setq comint-output-filter-functions 'comint-truncate-buffer
                                comint-buffer-maximum-size 5000
                                comint-scroll-show-maximum-output t
                                comint-input-ring-size 500))))

Which will keep your buffer under 5000 lines.

About these ads