input buffer for each connection can be saved into file on disconnect and automatically restored during next connect (see ‘sqlplus-session-cache-dir’ variable); if you place some SQL*Plus commands between ‘/* init */’ and ‘/* end */’ comments in saved input buffer, they will be automatically executed on every connect

if you use plsql.el for editing PL/SQL files, you can compile such sources and error messages will be parsed and displayed for easy source navigation

in sqlplus-mode or plsql-mode you can easy go to the definition of database object name under point (table, view, synonym, index, trigger, package, function, procedure) in filesystem.

Simplified usage:

C-RET execute command under point

C-S-RET execute command under point and show result table in HTML buffer

M-RET explain execution plan for command under point

C-cC-s show database object definition (retrieved from Oracle)

C-cC-c compile PL/SQL buffer (must be in plsql-mode) and show errors

M-. or C-mouse-1 find the definition of database object name under point in filesystem (use arrow button on toolbar to go back).

Tested in Linux only for now, feedback from other sys users expected. Please use Emacs 22+.

As Ide Skel plugin

If you decide to use sqlplus.el together with IdeSkel package, your Emacs will look more like TOAD or Tora:

Bugs

There is a bad interaction with require-final-newline when it is set to a value that means that the user is asked each time. When saving the output in HTML, for instance, there seems to be a hook loop that keeps asking if a final newline is to be appended. Setting the value of require-final-newline to t does not seem to trigger this bug.

Other

From the description above and after a quick look at the source, this looks awesome! I just hacked together something much smaller, some code to send queries to Oracle via SQL*Plus and to get that into lists that I can then manipulate. Can your hack do that as well? If so I can ditch my stuff 😊 – MaDa

Yes. First, you must provide a function that will handle result of sql command:

CONTEXT parameter is an alist with info about command executed (SQL text in particular). The DATA parameter is a list (COLUMN-INFOS ROWS MSG) if result is a table, or string in any other case. COLUMN-INFOS is a list of column descriptors (name of column etc.), ROWS is a table of records (record is a list of strings), MSG is a message under the table (something like “28 rows selected.”).

Now, you can execute any sql command (eg. query):

(let ((connect-string "scott/tiger@xe") ; must be password here
(sql "select * from emp;")) ; a ';' or '/' at end of command is important;; create sqlplus process for connect-string (if doesn't exist yet)
(sqlplus connect-string nil 'dont-create-output-buffer)
;; send command - it will be executed in idle time,;; so your function 'my-handler' will be called asynchronously
(sqlplus-user-command connect-string sql 'my-handler))

I’m using sqlplus with ide-skel on Windows Emacs version 22.0.92.1. Works great. – Ben

Hi! Really like sqlplus. Using it on windows XP with emacs 22.1. Best regards – Pierre

Hi. I’ve been using this for a few days and really like it. However, I’m experiencing some bugs with plsql.el. It throws a RegEx error when trying to format PL/SQL Functions, also it doesn’t capitalize keywords. Has anyone experienced this and/or found a fix?

sqlplus is really good! But is there any way to assign a connection to a buffer? The only way I found till now, when I edit an existing file, is to set the mode to sqlplus (M-x sqlplus-mode) and then restart the connection (M-x sqlplus-restart-connection). Best regards – George

Really quite impressive. I’ve tried it under Win32, you just need to customize sqlplus-command (set it to the full path of sqlplus.exe).

As a developer you have to deal with several different databases. It’s not easy to keep all of your environments in mind. Let’s use Org-mode to help yourself. Keep an environment’s description in “org-mode” table:

Place the point to a connection line, call sqlplus-x-connect and you will get connected sqlplus-mode buffer. If different users have the same password you can write them in one cell. Here is the sqlplus-x-connect

sqlplus mode seems really nice. But after lot of efforts I can’t get it to work with our setup. Problem is our DB installation uses kerberos authentications and we don’t have user/pass@SID to connect to DB. All we got is a kerberos ticket and SID e.g we login using sqlplus /@mydb. somehow I can’t get it to work with sqlplus mode. Please help! Part of the reason could be I can’t read/debug LISP code. – Arvind Deshpande

To use kerberos, just enter “/” as user and leave password blank. hth… don

This work is licensed to you under version 2 of the
GNUGeneral Public License.
Alternatively, you may choose to receive this work under any other
license that grants the right to use, copy, modify, and/or distribute
the work, as long as that license imposes the restriction that
derivative works have to grant the same rights and impose the same
restriction. For example, you may choose to receive this work under
the
GNUFree Documentation License, the
CreativeCommonsShareAlike
License, the XEmacs manual license, or
similar licenses.