Re: patch: Oracle engine for SQL blocks

[Eric Schulte <schulte.eric@gmail.com>]

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)