Literate Database Work with Oracle

Literate Database Work by Howard Abrams was eye opening.

My only problem: I am currently in the Oracle world and orgmode ob-sql doesn’t support sqlplus. With SQLi-Mode it’s at least possible to use Emacs to connect to a database. In this case Oracle. But ob-sql supports Perl dbi-shell. For Perl there is DBD::Oracle which brings us full circle.

Being on Mac OS X I followed this HowTo. After installing

• instantclient-basic
• instantclient-sdk
• instantclient-sqlplus

in /opt/oracle/instantclient_11_2 and running ln -s libclntsh.dylib.11.1 libclntsh.dylib in that directory .bashrc needs some adjustment:

export PATH=$PATH:/usr/local/bin:/opt/oracle/instantclient_11_2
export ORACLE_HOME=/opt/oracle/instantclient_11_2
export DYLD_LIBRARY_PATH=/opt/oracle/instantclient_11_2
export TNS_ADMIN=/opt/oracle/instantclient_11_2/network/admin
export CLASSPATH=$CLASSPATH:$ORACLE_HOME
export NLS_LANG=".AL32UTF8"

Now we are ready to install

• DBD::Oracle
• DBI::Shell

via cpan.

Finally we are able to follow Howard Abrams with slightly different properties in our org-file:

* dbitest
:PROPERTIES:
:engine: dbi
:cmdline: dbi:Oracle://127.0.0.1:1521/SID scott tiger
:END:
#+NAME: dualtest
#+begin_src sql
  SELECT sysdate FROM dual;
#+end_src

#+RESULTS: dualtest