patch: Oracle engine for SQL blocks

["John Dell'Aquila" <dellaq@ml1.net>]

[-- Attachment #1: Type: text/plain, Size: 613 bytes --]

Hi,

This patch makes Oracle a supported database for SQL source code
blocks:

  - separate or unified logins, take your pick of:
    :dbuser user :dbpassword password :dbhost connect_id
    :dbuser user/password@connect_id
    :cmdline user/password@connect_id

  - support for :colnames on/off

  - helpful error messages in minibuffer if execution fails

  - good looking result block (most of the time)

I've tested this extensively on Windows 7. It only needs the sqlplus
executable and < > file redirection, so it should work everywhere.

I am willing to execute an FSF copyright assignment.

Regards,
John

[-- Attachment #2: 0001-ob-sql.el-Implement-Oracle-engine.patch --]
[-- Type: application/octet-stream, Size: 2985 bytes --]

From 08e01124a7f65fbdeec8d14f0e7b555bb1565ad9 Mon Sep 17 00:00:00 2001
From: John Dell'Aquila <dellaq@ml1.net>
Date: Tue, 10 Jun 2014 20:33:31 -0500
Subject: [PATCH] ob-sql.el: Implement Oracle engine

* lisp/ob-sql.el (org-babel-execute:sql): Add Oracle support with
proper error handling.
---
 lisp/ob-sql.el | 41 +++++++++++++++++++++++++++++++++++++++++
 1 file changed, 41 insertions(+)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 7b85df8..42a03a1 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -128,11 +128,31 @@ This function is called by `org-babel-execute-src-block'."
 				  (org-babel-process-file-name in-file)
 				  (org-babel-process-file-name out-file)
 				  (or cmdline "")))
+		    ('oracle (format
+			      "sqlplus -S -L %s %s <%s >%s"
+			      (if (and dbuser dbpassword dbhost)  ; build login
+				  (format "%s/%s@%s" dbuser dbpassword dbhost)
+				(or dbuser ""))  ; or just pass dbuser
+			      (or cmdline "")
+			      (org-babel-process-file-name in-file)
+			      (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
+	  ;; Inject suitable SQL*Plus settings
+	  (concat
+	   (if colnames-p
+	       "set heading on underline '-'\n"  ; column headings
+	     "set heading off\n")          ; no headings
+	   "set colsep '|' tab off\n"  ; "|" between columns, no tabs
+	   "set linesize 150\n"        ; Oracle default width
+	   "set newpage none\n"        ; no page breaks
+	   "set feedback off\n"        ; no informational messages
+	   "prompt ~_SentineL~!\n"     ; marker for post-processing
+	   ))
 	 (t ""))
        (org-babel-expand-body:sql body params)))
     (message command)
@@ -155,6 +175,27 @@ This function is called by `org-babel-execute-src-block'."
 	       (insert "-\n")
 	       (setq header-delim "-")
 	       (write-file out-file)))))
+	  ((eq (intern engine) 'oracle)
+	   (with-temp-buffer
+	     (insert-file-contents out-file)
+	     ;; Delete up to and including sentinel (anything before
+	     ;; sentinel is garbage from user's login.sql script)
+	     (goto-char (point-min))
+	     (if (re-search-forward "~_SentineL~!\n" nil t)
+		 (delete-region (point-min) (point)))
+	     ;; Anything left?
+	     (if (eq (buffer-size) 0)
+		 (if (string-match ";" body)
+		     (error "No rows selected") 
+		   (error "No rows selected (need final ; perhaps?)")))
+	     ;; Scan for *last* Oracle or SQL*Plus error, include up
+	     ;; to 5 preceding lines in error message
+	     (goto-char (point-max))
+	     (if (re-search-backward "^\\(ORA\\|SP2\\)-[0-9]+: " nil t)
+		 (let ((p (line-end-position)))
+		   (forward-line -5)
+		   (error (buffer-substring-no-properties (point) p))))
+	     (write-file out-file)))
 	  (t
 	   ;; Need to figure out the delimiter for the header row
 	   (with-temp-buffer
-- 
1.9.2.msysgit.0