Re: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

[pierre.techoueyres@free.fr (Pierre Téchoueyres)]

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

Nicolas Goaziou <mail@nicolasgoaziou.fr> writes:
> ...
> Otherwise, it looks good. Would you mind adding an entry in ORG-NEWS
> about it?
Hello Nicolas,

What dou you think about the attached patch ?


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: Use Oracle's alias in ob-sql patch --]
[-- Type: text/x-patch, Size: 2678 bytes --]

From 0103a07b10915ce7c919b8a6858beff3dbd7e45f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Pierre=20T=C3=A9choueyres?= <pierre.techoueyres@free.fr>
Date: Thu, 8 Mar 2018 23:37:29 +0100
Subject: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

* lisp/ob-sql.el  (org-babel-sql-dbstring-oracle): don't use empty
args. This allow use of alias defined in Oracle's TNSNAMES files.
(org-babel-execute:sql): don't feed lines with trailing spaces. This
also improve speed for retrieving data.
---
 etc/ORG-NEWS   | 18 ++++++++++++++++++
 lisp/ob-sql.el | 11 +++++++++--
 2 files changed, 27 insertions(+), 2 deletions(-)

diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS
index 77373d442..11fe4395d 100644
--- a/etc/ORG-NEWS
+++ b/etc/ORG-NEWS
@@ -199,6 +199,24 @@ you should expect to see something like:
 #+BEGIN_EXAMPLE
   ,#+STARTUP: shrink
 #+END_EXAMPLE
+*** Add support for Oracle's database alias in Babel blocks
+=ob-sql= library already support running SQL blocks against an Oracle
+database using ~sqlplus~.  Now it's possible to use alias names
+defined in TNSNAMES file instead of specifying full connection
+parameters.  See example bellow.
+
+#+BEGIN_SRC org
+  you can use the previous full connection parameters
+  ,#+BEGIN_SRC sql :engine oracle :dbuser me :dbpassword my_insecure_password :database my_db_name :dbhost my_db_host :dbport 1521
+  select sysdate from dual;
+  ,#+END_SRC
+
+  or the alias defined in your TNSNAMES file
+  ,#+BEGIN_SRC sql :engine oracle :dbuser me :dbpassword my_insecure_password :database my_tns_alias
+  select sysdate from dual;
+  ,#+END_SRC
+#+END_SRC
+
 ** New functions
 *** ~org-insert-structure-template~
 
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 959ede3de..3ad7906cf 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -111,8 +111,14 @@ Pass nil to omit that arg."
 	       (when database (concat "-d" database))))))
 
 (defun org-babel-sql-dbstring-oracle (host port user password database)
-  "Make Oracle command line args for database connection."
-  (format "%s/%s@%s:%s/%s" user password host port database))
+  "Make Oracle command line args for database connection.
+
+If PORT and DATABASE are nil then don't pass them. This allow you to
+use names defined in your TNSNAMES file."
+  (format "%s/%s@%s%s" user password host
+	  (if (and port database)
+	      (format ":%s/%s" port database)
+	    "")))
 
 (defun org-babel-sql-dbstring-mssql (host user password database)
   "Make sqlcmd command line args for database connection.
@@ -241,6 +247,7 @@ SET NEWPAGE 0
 SET TAB OFF
 SET SPACE 0
 SET LINESIZE 9999
+SET TRIMOUT ON TRIMSPOOL ON
 SET ECHO OFF
 SET FEEDBACK OFF
 SET VERIFY OFF
-- 
2.14.3