emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: "John Dell'Aquila" <dellaq@ml1.net>
To: emacs-orgmode@gnu.org
Subject: patch: Oracle engine for SQL blocks
Date: Wed, 11 Jun 2014 00:10:32 -0500	[thread overview]
Message-ID: <1402463432.31873.127469781.7224901D@webmail.messagingengine.com> (raw)

[-- 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


             reply	other threads:[~2014-06-11  5:10 UTC|newest]

Thread overview: 3+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2014-06-11  5:10 John Dell'Aquila [this message]
2014-06-12 20:00 ` patch: Oracle engine for SQL blocks Eric Schulte
     [not found]   ` <1402632199.9139.128293873.498FDE9C@webmail.messagingengine.com>
2014-06-13 11:44     ` Eric Schulte

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://www.orgmode.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=1402463432.31873.127469781.7224901D@webmail.messagingengine.com \
    --to=dellaq@ml1.net \
    --cc=emacs-orgmode@gnu.org \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).