SqlPlus

Lisp:sqlplus.el

EmSqlPlus

Available on MELPA!

Facilitates interaction with Oracle via SQL*Plus. Moreover, this package complements Lisp:plsql.el (Kahlil Hodgson) upon convenient compilation of PL/SQL source files.

SqlPlus

This package was inspired by sqlplus-mode.el (Rob Riepel, Peter D. Pezaris, Martin Schwenke), but offers more features:

Simplified usage:

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:

SqlPlusIdeSkel

Explore pop-up menu options in right window after mouse-3 click.

Lisp:sqlplus.el

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

If you find this package useful, send me a postcard to address:

  Peter Karpiuk
  Scott Tiger S.A.
  ul. Kolektorska 15
  01-692 Warsaw
  Poland

😊

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:

    (defun my-handler (connect-string context data)
      (message (format "connect-string: %S\ncontext: %S\ndata: %S" connect-string context data)))

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))

When sqlplus process is not needed, you can kill it:

    ;; kill sqlplus process if no buffer uses it
    (sqlplus-soft-shutdown connect-string))

PeterKarpiuk

I saw this pop up on the Recent Changes and had to take a look. I only had time for a little work with it but wow! See my screenshot here: http://picasaweb.google.com/hostetlerm/Geek/photo#5138784267792412514-- MikeHostetler

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?

Cheers! – LaurenceRochfort

I’ve started using it yesterday (Emacs 23.1.94.1 of 2010-03-23, Windows XP). Right now I wonder how to load and save my foo@bar.sqp buffers into files and return to it next time.

Our database uses UTF-8:

(add-hook 'sqlplus-mode-hook
	  (lambda ()
	    (set-process-coding-system (get-process (sqlplus-get-process-name sqlplus-connect-string))
				       'latin-1 'latin-1)))

AlexSchroeder

sqlplus mode is really good. I no longer need sql developer to browse schemas - good old emacs can do it all !

HarishSharma

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).

FabriceGabolde

Connection Manager for the Sqlplus Mode.

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:

* environments
** databases
|---------------+-----------+--------|
| service (sid) | user      | pwd    |
|---------------+-----------+--------|
| prod          | owner app | god    |
| dev           | dbo       | love   |
| uat           | dba       | secret |
|---------------+-----------+--------|

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

(require 'sqlplus)
(require 'org-table)
(defvar sqlplus-x-columns '(sqlplus-x-service sqlplus-x-user sqlplus-x-pwd))
(defun sqlplus-x-connect ()
  "Build a connection string and make a connection. The point must be in an org-mode table.
Columns of the table must correspond to the `sqlplus-x-columns' variable."
  (interactive)
  (org-table-force-dataline)
  (let
      ((cur-row (nth (org-table-current-dline) (org-table-to-lisp)))
       (is-user-selected (= (org-table-current-column) (+ 1 (position 'sqlplus-x-user sqlplus-x-columns)))))
    (sqlplus
     (format
      "%s/%s@%s"
      (if is-user-selected
          (thing-at-point 'symbol)
        (nth (position 'sqlplus-x-user sqlplus-x-columns) cur-row))
      (nth (position 'sqlplus-x-pwd sqlplus-x-columns) cur-row)
      (nth (position 'sqlplus-x-service sqlplus-x-columns) cur-row))
     (concat (nth (position 'sqlplus-x-service sqlplus-x-columns) cur-row) ".sqp"))))

(global-set-key [f4] 'sqlplus-x-connect)

Advice. Use EasyPG package to keep your connections table in secret.

VjacheslavIgnatyev

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

(defadvice switch-to-buffer…) breaks dired’s switch-to-buffer now on ntemacs 24. Commenting out ad-activate underneath solved it

That’d be great if this would be somehow merged with sql.el – in order to be used for MS SQL as well. with the IDE, it REALLY is STUNNING! Felicidades…

Something else: it’d be good to use the natural Comint shortcuts (C-c C-b to send buffer, C-c C-r to send region, etc.) as these are common to multiple modes (R buffer, etc.).


CategorySql