Download
(require 'sql)
(defcustom osq-username "scott"
"oracle user name."
:group 'sqlparse
:type 'string)
(defcustom osq-password "tiger"
"oracle user password."
:group 'sqlparse
:type 'string)
(defcustom osq-server "localhost"
"Default server or host."
:type 'string
:group 'SQL
:safe 'stringp)
(defcustom osq-dbname "orcl"
"database name ."
:type 'string
:group 'SQL
:safe 'stringp)
(defcustom osq-port 1521
"Default port."
:type 'number
:group 'SQL
:safe 'numberp)
(defcustom osq-as-sysdba nil
"login as sysdba."
:type 'boolean
:group 'SQL
:safe 'booleanp)
(defvar osq-linesize 2000
"Default linesize for sqlplus")
(defun oracle-shell-query(sql)
"query `sql',and return as list"
(let ((raw-result (osq-shell-querry-raw sql)) table)
(when raw-result
(when (string-match "\\bERROR\\b" raw-result) (error raw-result))
(if (string-match "rows will be truncated" raw-result)
(progn
(setq osq-linesize (+ osq-linesize 500))
(setq table (oracle-shell-query sql)))
(setq table (osq-parse-result-as-list raw-result))))
table))
(defun osq-parse-result-as-list (raw-result)
(let (result row)
(with-temp-buffer
(insert raw-result)
(goto-char (point-min))
(while (re-search-forward "[ \t\n]* [ \t\n]*" nil t)
(replace-match " " nil nil))
(goto-char (point-min))
(while (re-search-forward "^[ \t]+" nil t)
(replace-match "" nil nil))
(goto-char (point-min))
(while (not (= (point-at-eol) (point-max)))
(setq row (split-string (buffer-substring-no-properties (point-at-bol) (point-at-eol)) " " t))
(setq result (append result (list row)))
(forward-line) (beginning-of-line))
)result ))
(defun osq-shell-querry-raw (sql)
(let ( (cmd (format "echo \"%s\" |%s" (osq-generate-sql-script sql) (osq-conn-str))))
(shell-command-to-string cmd)))
(defun osq-conn-str()
" default:sqlplus -s scott/tiger@localhost:1521/orcl"
(if osq-as-sysdba
(format "sqlplus -s %s/%s@%s:%s/%s as sysdba"
osq-username osq-password osq-server osq-port osq-dbname)
(format "sqlplus -s %s/%s@%s:%s/%s"
osq-username osq-password osq-server osq-port osq-dbname)
))
(defun osq-generate-sql-script(sql)
(when (string-match "\\(.*\\);[ \t]*" sql)
(setq sql (match-string 1 sql)))
(with-temp-buffer
(insert "set heading off;\n")
(insert (format "set linesize %d;\n" osq-linesize))
(insert "set colsep ' ';\n")
(insert "set null 'NULL';\n")
(insert "set wrap off;\n")
(insert "set pagesize 0;\n")
(insert "set feedback off;\n")
(insert "set serveroutput on;\n")
(insert (concat sql " ;\n"))
(buffer-string)
)
)
(provide 'oracle-shell-query)