Nicolas Goaziou <m...@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 ?
>From 0103a07b10915ce7c919b8a6858beff3dbd7e45f Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Pierre=20T=C3=A9choueyres?= <pierre.techouey...@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
