The outstanding Emacs Org-mode enables you to include source code blocks our text. Org-Babel then enables you to execute that code.
Yesterday I came across the Orgmode cookbook. In a section it shows how to write a SQL statement and execute it agains a postgresql database. Very nice I thought, but what about Oracle DB? Because that what I use at work.
No official support
Oracle DB is not supported as of now. There is a proposed patch. It adds the call to sqlplus, Oracle DBs command line client. But on the mailing list there was no discussion about it. 🙁 One trick it applies is to add the „.sql“ suffix to the input file to sqlplus.
- (in-file (org-babel-temp-file "sql-in-" ".sql"))
So now I got the following code that is an additional branch in the org-babel-execute:sql function:
- ('oracle (format
- "sqlplus -s %s"
- (cond ( (and dbuser dbhost dbpassword)
- (format "%s/%s@%s @%s > %s"
- dbuser dbpassword dbhost
- (org-babel-process-file-name in-file)
- (org-babel-process-file-name out-file)
- )
- )
- ;; user specified dbuser, dbpassword, & dbhost
- ( (or dbuser dbhost dbpassword)
- (error (format "Must specify dbuser/dbpassword@dbhost, missing %s %s %s"
- (if dbuser "" ":dbuser")
- (if dbpassword "" ":dbpassword")
- (if dbhost "" ":dbhost")
- )
- )
- ) ;; if one specified, they all must be
- ( t
- (format "%s @%s > %s"
- (or cmdline "")
- (org-babel-process-file-name in-file)
- (org-babel-process-file-name out-file)
- )
- ))))
Formating the result
Now I got the SQL executing against my db. The output was a bit weired. I experimented with sqlplus formatting with mixed results.
I consulted the elisp source. The result is converted into an orgmode table using TAB as a delimiter. In an example that had two rather long columns with short column names, sqlplus used several tabs between the two column names and so screwed the output quite a bit.
I don’t have a good solution for that. My workaround is to use comma as a column seperator (set colsep ',') and let orgmode guess that. For that I had to touch one line:
- (org-table-import out-file (if (eq (intern engine) 'oracle) nil '(16)))
Also I deleted the first empty line using an oracle-branch in org-babel-result-cond/with-temp-buffer:
- ((eq (intern engine) 'oracle)
- (with-temp-buffer
- (insert-file-contents out-file)
- (goto-char (point-min))
- (kill-line)
- (write-file out-file)))
Conclusion
In principle, it works. How nice formatted the output it is, depends on your query. When just documenting a query, as an alternative to orgmode I use SQL-Mode and format the result myself.
The angle brackets are getting eaten so the sed pipe diapppears.
The sqlplus command should be:
"sqlplus -S %s/%s@%s <%s | sed ‚%s‘ >%s"
Let’s try again. Add this to the command case statement:
(‚oracle (format
„sqlplus -S %s/%s@%s %s“
dbuser dbpassword dbhost
(org-babel-process-file-name in-file)
„/^Session altered.$/d; /^PL[/]SQL procedure successfully completed.$/d; /^\\s*\\S/!d“
(org-babel-process-file-name out-file)))
Then add this to the with-temp-file insert case statement:
(‚oracle „set linesize 200 newpage none colsep ‚|‘ tab off feedback off esc off\n“)
This works very well for me.
diff -c „~/.emacs.d/elpa/org-20140602/ob-sql.el“ „~/.emacs.d/lisp/ob-sql.el“
*** ~/.emacs.d/elpa/org-20140602/ob-sql.el Fri Jun 6 10:12:51 2014
— ~/.emacs.d/lisp/ob-sql.el Fri Jun 6 10:28:09 2014
***************
*** 127,137 ****
— 127,144 —-
(org-babel-process-file-name in-file)
(org-babel-process-file-name out-file)
(or cmdline „“)))
+ (‚oracle (format
+ „sqlplus -S %s/%s@%s %s“
+ dbuser dbpassword dbhost
+ (org-babel-process-file-name in-file)
+ „/^Session altered.$/d; /^PL[/]SQL procedure successfully completed.$/d; /^\\s*\\S/!d“
+ (org-babel-process-file-name out-file)))
(t (error „No support for the %s SQL engine“ engine)))))
(with-temp-file in-file
(insert
(case (intern engine)
(‚dbi „/format partbox\n“)
+ (‚oracle „set linesize 200 newpage none colsep ‚|‘ tab off feedback off esc off\n“)
(t „“))
(org-babel-expand-body:sql body params)))
(message command)
Diff finished. Fri Jun 6 10:28:39 2014