Early in my career, when I was first introduced to Unix, my mentor at the time recommended that I use a text editor called emacs, but also learn how to use vi as well. I personally like both editors, but I prefer emacs for coding. Over the decades, I’ve continued to use emacs; I use it for nearly everything work-related that doesn’t fit in a web browser these days. I use it for email (GNUS), writing presentations and papers (org-mode), and coding every possible language and system imaginable(perl-mode, SQLi mode, gnuplot-mode, various lisp-modes, and more). I’ve found that I’m usually much more productive using emacs than using a full-featured integrated development environment like Eclipse, SQL*Developer, or Toad except in some very few special cases. Over the years, there have been many changes and improvements to the original SQL mode and related pl-sql mode; these have all been superseeded with a new mode called SQLi mode. SQLi mode has support for most databases today, including Oracle and mySQL among many others.
In this quick tutorial, I’ll show how to set up emacs version 24.2.1 to work with an Oracle database, and what you can do with this setup.
What does SQLi mode do?
SQLi mode makes it easier to interact with
Developing and administering Oracle ends up going something like this:
- Write a script or SQL statement
- Start a sqlplus session in another window
- Test your script in the new window by invoking
- Look at the output to see what went wrong
- Fix script
- Switch back to your editing buffer, and repeat from step 3 above.
It’s not very fun, because of the constant switching between the terminal in which you’re running sqlplus, and the terminal you’re editing in. It’s also not fun having to retype the SQL file name over & over again. SQLi mode automates this so that you can send either the latest statement, or a highlighted region, or an entire buffer to sqlplus with just a few quick keystrokes. It gives you a very useful ability to re-execute previous bits of SQL you’ve used. Finally, it highlights and color-codes your SQL and pl/sql code which makes it very nice to code in – for example, it becomes very easy to see comments, as they show up in red.
SQLi mode comes automatically with Emacs v24, so there’s nothing extra that you need to install.
Finding more in-depth help about SQLi mode
M-x sql-help[return] to open a buffer with documentation about SQLi mode.
Configuring sql-mode to access an Oracle database
The easiest way to set it up is to execute
configure-group for the
[escape] x customize-group [return] SQL [return], or
M-x customize-group [return] SQL [return]. A buffer will be created that has some options you’ll probably want to change as follows:
|Sql Database||change to your target database name|
|SQL Oracle Program||Full path to ‘sqlplus’|
|SQL Password||oracle account password|
|SQL Product||set to ‘Oracle’|
|SQL Server||database host (or localhost if using port forwarding)|
|SQL User||oracle username|
Note that I’ve been able to get this working even on Windows 7, under emacs running under cygwin/X, with sqlplus installed under windows. Under that environment, the cygwin path to sqlplus will need to be configured for the
SQL Oracle Program; for me it is
If you do not wish to be prompted for login credentials, you can optionally set the
(defalias 'sql-get-login 'ignore) in your .emacs initialization file.
If you do not have
global-font-lock-mode set, you may wish to do that so that whenever you visit some SQL, it will automatically show up displayed in color.
Starting it up
First, start the
*SQL* buffer, which actually runs sqlplus. Just type
M-x sql-oracle[return] – unless you have set
sql-get-login, it will prompt for the username,password, and database; if you have set the variables above they will be the defaults.
*SQL* buffer is not directly manipulated; instead, start up a new buffer/file.
M-x find-file[return] /home/jstanley/test.sql You’ll notice that it will automatically be put into
Then, start typing some SQL that you’d like to work with;
select object_name from user_objects;
You can then use
C-c C-b to send this to your sqlplus session!
You can use the following key bindings to interact with the your sql process:
|Key binding||What it does|
|C-c C-l a||sql-list-all|
|C-c C-l t||sql-list-table|
If you get an error that says:
No sql process started, then in your
test.sql buffer, enter:
M-x sql-set-sqli-buffer RET *SQL* RET
sql-list-all command basically lists all of the objects in the
sql-list-table command lists only the tables owned by the current user.
You can use emacs
SQLi mode to really make development much easier than doing it with vi and sqlplus.
For a more in-depth tutorial (for IBM DB2, but very similar), check out http://www.ibm.com/developerworks/data/library/techarticle/0206mathew/0206mathew.html
Date: 2012-10-26 Fri
HTML generated by org-mode 6.34c in emacs 24