Elisp utility to open SQLplus from a remote host

If you are in a situation when you have to login to an intermediate or jump host to open SQLplus session to your database, here is a utility that you can make use of. You can store this utility in your .emacs file. Then the utility can be invoked interactively from inside Emacs, by typing "Alt-x open-remote-sqlplus-session-interactive".

With autocmpletion, you can specify the session alias that you would like to open.

To make the best use of the utility, you need to specify the list of jump hosts and the SQLplus connection strings in the "alias-to-jumphosts-alist" variable. If you need to run any custom commands immediately after opening SQLplus session (in addition to commands in your login.sql or glogin.sql files), you can add these commands in list-of-sqlplus-setup-commands.

I find this utility to be a tremendous time saver. Hope you also find it the same way.

Here is the utility as a gist:

	;; This utility is to open a SQLplus session from an intermediate jump host.
	;; You can invoke the "open-remote-sqlplus-session-interactive" function interactively,
	;; which will prompt you for a jump host and the connection string to the SQLplus session.
	;; Please change the "list-of-sqlplus-setup-commands" to fit your needs.
	;; Also you should change the entries in alias-to-jumphosts-alist to a suitable value.
	;; - Roy 09/15/2012.
	(defvar alias-to-jumphosts-alist '(("development" . ((:sshurl . "user@devhost") (:sqlurl . "devuser/devpassword@devel")))
	("integration" . ((:sshurl . "user@integhost") (:sqlurl . "intuser/intpassword@integ")))
	("qa" . ((:sshurl . "user@qahost") (:sqlurl . "qauser/qapassword@qa"))))
	"Contains the mapping from an alias to its jump host and SQL url combination.")
	;; List of SQLplus commands to run during start up.
	(defvar list-of-sqlplus-setup-commands
	'("SET LINESIZE 180"
	"SET PAGESIZE 100"
	"SET AUTOCOMMIT ON"
	"SET TRIMOUT ON"
	"SET TRIMSPOOL ON"
	"SET TAB OFF"
	"SET COLSEP '|'"
	"COLUMN NAME FORMAT A15"
	"COLUMN VALUE FORMAT 999999999999999")
	"This variable contains the list of commands that needs to be run as soon as the sqlplus session is started.")
	(defun setup-sqlplus-session ()
	"Sets up the sqlplus session in the current comint buffer."
	(dolist (cmd-to-run list-of-sqlplus-setup-commands)
	(let ((full-query-string (concat cmd-to-run "\C-m")))
	(comint-send-string (current-buffer) full-query-string))))
	(defun open-ssh-session (buffer-name sshurl)
	"To open ssh session in a comint session. the connection string given is used."
	(switch-to-buffer (make-comint buffer-name "ssh" nil sshurl)))
	(defun open-remote-sqlplus-session (buffer-name sshurl sqlurl)
	"Opens an ssh session in the given host. then opens a sqlplus session using the connection string."
	(if (get-buffer (format "*%s*" buffer-name))
	(switch-to-buffer (format "*%s*" buffer-name))
	(open-ssh-session buffer-name sshurl)
	(comint-send-string (current-buffer) (concat "sqlplus " sqlurl "\C-m"))
	(setup-sqlplus-session)))
	(defun get-sid-from-conn-str (sqlurl)
	"Returns the sid portion from the connection string. for e.g. user/password@sid returns sid."
	(let ((index 0))
	(while (not (equal (aref sqlurl index) 64))
	(setq index (1+ index)))
	(substring sqlurl (1+ index))))
	(defun get-all-jumphost-aliases ()
	"Returns the list of jump host aliases defined in the alias-to-jumphosts-alist."
	(let (ret-val)
	(dolist (entry alias-to-jumphosts-alist ret-val)
	(setq ret-val (cons (car entry) ret-val)))
	(reverse ret-val)))
	(defun open-remote-sqlplus-session-interactive ()
	"Interactively opens a remote SQLplus session by prompting the user for session alias."
	(interactive)
	(let* ((alias (completing-read "Please enter the session name: " (get-all-jumphost-aliases)))
	(entry (assoc alias alias-to-jumphosts-alist))
	(sshurl (cdr (assoc :sshurl entry)))
	(sqlurl (cdr (assoc :sqlurl entry)))
	(buffer-name (get-sid-from-conn-str sqlurl)))
	(open-remote-sqlplus-session buffer-name sshurl sqlurl)))

view raw open-remote-sqlplus-session.el hosted with ❤ by GitHub