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.

  1. (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:

  1. ('oracle (format
  2.                               "sqlplus -s %s"
  3.                   (cond ( (and dbuser dbhost dbpassword)
  4.                       (format "%s/%s@%s @%s > %s"
  5.                           dbuser dbpassword dbhost
  6.                           (org-babel-process-file-name in-file)
  7.                           (org-babel-process-file-name out-file)
  8.                           )
  9.                       )
  10.                     ;; user specified dbuser, dbpassword, & dbhost
  11.                     ( (or dbuser dbhost dbpassword)
  12.                       (error (format "Must specify dbuser/dbpassword@dbhost, missing %s %s %s"
  13.                              (if dbuser "" ":dbuser")
  14.                              (if dbpassword "" ":dbpassword")
  15.                              (if dbhost "" ":dbhost")
  16.                              )
  17.                          )
  18.                       ) ;; if one specified, they all must be
  19.                     ( t
  20.                       (format "%s @%s > %s"
  21.                           (or cmdline "")
  22.                           (org-babel-process-file-name in-file)
  23.                           (org-babel-process-file-name out-file)
  24.                           )
  25.                       ))))

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:

  1. (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:

  1. ((eq (intern engine) 'oracle)
  2.        (with-temp-buffer
  3.            (insert-file-contents out-file)
  4.            (goto-char (point-min))
  5.            (kill-line)
  6.            (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 Oracle DB function „add_months“ adds a given number of months to a given date:

SQL> select add_months(to_date('04.03.14','dd.mm.yy'),1) as result from dual;

RESULT
--------
04.04.14

Nice. But what about 2014-02-28 plus 1 month?

SQL> select add_months(to_date('28.02.14','dd.mm.yy'),1) as result from dual;

RESULT
--------
31.03.14

Ups. It turns out that (quote from documentation)

If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month.

The day component of the result can even be greater than the day component of the argument date:

SQL> select add_months(to_date('28.02.14','dd.mm.yy'),120) as result from dual;

RESULT
--------
29.02.24

This was the root cause of defect in a partner system. Of course, there is already a discussion on Stackoverflow over this issue.

As an alternative, you can use interval arithmetic:

SQL> select to_date('28.02.14','dd.mm.yy') + INTERVAL '10' YEAR as result from dual;

RESULT
--------
28.02.24

Nice. But interval arithmetic is not of much help because it does not work with days that does not exist in every month.

SQL> select to_date('28.02.14','dd.mm.yy') + INTERVAL '10' YEAR as result from dual;
select to_date('28.02.14','dd.mm.yy') + INTERVAL '10' YEAR as result from dual
                                      *
ERROR in Zeile 1:
ORA-01839: date not valid for month specified

Date arithmetic is not trivial, I guess.