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.
[sourcecode language=“lisp“]
(in-file (org-babel-temp-file „sql-in-“ „.sql“))
[/sourcecode]
So now I got the following code that is an additional branch in the org-babel-execute:sql
function:
[sourcecode language=“lisp“]
(‚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)
)
))))
[/sourcecode]
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:
[sourcecode language=“lisp“]
(org-table-import out-file (if (eq (intern engine) ‚oracle) nil ‚(16)))
[/sourcecode]
Also I deleted the first empty line using an oracle-branch in org-babel-result-cond/with-temp-buffer
:
[sourcecode language=“lisp“]
((eq (intern engine) ‚oracle)
(with-temp-buffer
(insert-file-contents out-file)
(goto-char (point-min))
(kill-line)
(write-file out-file)))
[/sourcecode]
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.