patch: Oracle engine for SQL blocks

patch: Oracle engine for SQL blocks

[John Dell'Aquila]

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

Re: patch: Oracle engine for SQL blocks

[Eric Schulte]

Hi John,

This patch looks great.  Given it's length FSF copyright assignment will
be required so please begin that process and when it is complete I'll be
very happy to apply this patch.

Thanks,
Eric

"John Dell'Aquila" <dellaq@ml1.net> writes:

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

-- 
Eric Schulte
https://cs.unm.edu/~eschulte
PGP: 0x614CA05D (see https://u.fsf.org/yw)

Re: patch: Oracle engine for SQL blocks

[Eric Schulte]

"John Dell'Aquila" <dellaq@ml1.net> writes:

> Thanks, Eric. I've started the FSF assignment process.
>
> I have a question about the header row delimiter in the results table. I
> didn't set the header-delim variable in the code, becasue the user can
> change the Oracle underline character, possibly multiple times, so it is
> difficult to work out a proper setting.
>
> The results appeared OK with header-delim left at "". I now realize that
> this causes the table to be mis-parsed when the first column is blank,
> even though it has a proper header.
>
> I put in a fix to scan for the first "set underline" command and use
> that character, otherwise set header-delim to "-". In the process of
> testing I found I could always set header-delim to '-' and the table
> parsed correctly even when the actual Oracle delimiter was set
> differently.
>
> I'm wondering how this is supposed to work. I'd rather not add
> unecessary complexity, but I don't want on rely on a bug or misfeature
> either.
>

I'm not familiar with this portion of ob-sql, so I'll defer to your best
judgment.

Best,

-- 
Eric Schulte
https://cs.unm.edu/~eschulte
PGP: 0x614CA05D (see https://u.fsf.org/yw)