{"id":562,"date":"2014-03-31T23:04:06","date_gmt":"2014-03-31T21:04:06","guid":{"rendered":"http:\/\/www.epischel.de\/wordpress\/?p=562"},"modified":"2014-03-31T23:04:06","modified_gmt":"2014-03-31T21:04:06","slug":"emacs-org-babel-sql-with-oracle-db","status":"publish","type":"post","link":"https:\/\/www.epischel.de\/wordpress\/2014\/03\/emacs-org-babel-sql-with-oracle-db\/","title":{"rendered":"Emacs Org Babel SQL with Oracle DB"},"content":{"rendered":"

The outstanding Emacs Org-mode enables you to include source code blocks our text. Org-Babel then enables you to execute that code.
\nYesterday I came across the Orgmode cookbook<\/a>. In a section it shows how to write a SQL statement and execute it<\/a> agains a postgresql database. Very nice I thought, but what about Oracle DB? Because that what I use at work.<\/p>\n

No official support<\/h1>\n

Oracle DB is not supported as of now. There is a proposed patch<\/a>. It adds the call to sqlplus, Oracle DBs command line client. But on the mailing list there was no discussion about it. \ud83d\ude41 One trick it applies is to add the „.sql“ suffix to the input file to sqlplus.<\/p>\n

\n\n\t\t
\n\n\t\t
\n\t\t\t\t\t\t< View plain text<\/span> ><\/a>\n\t\t\t\t\t<\/div>\n\n\t\t
code<\/div>\n\n\t\t\n\t\t
\n\n\t<\/div>\n\t\n\t
\n\t\t
  1. (in-file (org-babel-temp-file "sql-in-" ".sql"))<\/div><\/li>\n<\/ol>\t<\/div>\n\n<\/div>\n\n

    So now I got the following code that is an additional branch in the org-babel-execute:sql<\/code> function:<\/p>\n

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

      Formating the result<\/h1>\n

      Now I got the SQL executing against my db. The output was a bit weired. I experimented with sqlplus formatting with mixed results.<\/p>\n

      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.<\/p>\n

      I don’t have a good solution for that. My workaround is to use comma as a column seperator (set colsep ','<\/code>) and let orgmode guess that. For that I had to touch one line:<\/p>\n

      \n\n\t\t
      \n\n\t\t
      \n\t\t\t\t\t\t< View plain text<\/span> ><\/a>\n\t\t\t\t\t<\/div>\n\n\t\t
      code<\/div>\n\n\t\t\n\t\t
      \n\n\t<\/div>\n\t\n\t
      \n\t\t
      1. (org-table-import out-file (if (eq (intern engine) 'oracle) nil '(16)))<\/div><\/li>\n<\/ol>\t<\/div>\n\n<\/div>\n\n

        Also I deleted the first empty line using an oracle-branch in org-babel-result-cond\/with-temp-buffer<\/code>:<\/p>\n

        \n\n\t\t
        \n\n\t\t
        \n\t\t\t\t\t\t< View plain text<\/span> ><\/a>\n\t\t\t\t\t<\/div>\n\n\t\t
        code<\/div>\n\n\t\t\n\t\t
        \n\n\t<\/div>\n\t\n\t
        \n\t\t
        1. ((eq (intern engine) 'oracle)<\/div><\/li>\n
        2.        (with-temp-buffer<\/div><\/li>\n
        3.            (insert-file-contents out-file)<\/div><\/li>\n
        4.            (goto-char (point-min))<\/div><\/li>\n
        5.            (kill-line)<\/div><\/li>\n
        6.            (write-file out-file)))<\/div><\/li>\n<\/ol>\t<\/div>\n\n<\/div>\n\n

          Conclusion<\/h1>\n

          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.<\/p>\n

          <\/div>","protected":false},"excerpt":{"rendered":"

          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?…<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"mf2_syndication":[],"webmentions_disabled_pings":false,"webmentions_disabled":false,"footnotes":""},"categories":[2],"tags":[72,73,74],"series":[],"class_list":["post-562","post","type-post","status-publish","format-standard","hentry","category-entwicklung","tag-emacs","tag-orgmode","tag-sql","kind-"],"kind":false,"_links":{"self":[{"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/posts\/562","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/comments?post=562"}],"version-history":[{"count":4,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/posts\/562\/revisions"}],"predecessor-version":[{"id":566,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/posts\/562\/revisions\/566"}],"wp:attachment":[{"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/media?parent=562"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/categories?post=562"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/tags?post=562"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/series?post=562"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}