Download
(defvar oracle-query-default-connection nil)
(defvar oq-timeout-wait-for-result 300
"waiting 300s for sql result returned.")
(defvar oq-linesize 20000 "Default linesize for sqlplus")
(defvar oq-pagesize 50000 "Default pagesize for sqlplus")
(defun oracle-query-fetch-username-from-connect-string(connect-string)
(when (string-match "\\(.*\\)/.*" connect-string)
(match-string 1 connect-string)))
(defun oq-parse-result-as-list (raw-result)
(let (result row index-of-result oracle-query-heading)
(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))
(forward-line 1)
(setq oracle-query-heading
(split-string (buffer-substring-no-properties
(point-at-bol) (point-at-eol)) " " t))
(forward-line 2) (setq index-of-result 3)
(while (not (= (point-at-eol) (point-max)))
(unless (or (= 1 (% index-of-result oq-pagesize))
(= 2 (% index-of-result oq-pagesize))
(= 0 (% index-of-result oq-pagesize)))
(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)
(setq index-of-result (1+ index-of-result)))
)(cons oracle-query-heading result)))
(defun oracle-query-read-connect-string ()
(let( (connection-string (read-string "SqlPlus Connect String:" "" nil)))
(list connection-string)))
(defun oracle-query-create-connection(connect-string)
"create a connection to oracle using sqlplus ,and return the
created process"
(interactive (oracle-query-read-connect-string))
(let ((oracle-query-process (start-process
"sqlplus"
(concat " *oracle-query-"
(oracle-query-fetch-username-from-connect-string connect-string) "-"
(number-to-string (random)) "*")
"sqlplus" connect-string)))
(process-send-string oracle-query-process "set heading on;\n")
(process-send-string oracle-query-process (format "set linesize %d;\n" oq-linesize))
(process-send-string oracle-query-process "set colsep ' ';\n")
(process-send-string oracle-query-process "set null 'NULL';\n")
(process-send-string oracle-query-process "set wrap off;\n")
(process-send-string oracle-query-process (format "set pagesize %s;\n" oq-pagesize))
(process-send-string oracle-query-process "set feedback on;\n")
(process-send-string oracle-query-process "set serveroutput on;\n")
(set-process-query-on-exit-flag oracle-query-process nil)
(set-process-sentinel oracle-query-process
(lambda (proc change)
(when (string-match "\\(finished\\|exited\\|exited abnormally with code\\)" change)
(message (concat (process-name proc) "closed"))
(kill-buffer (process-buffer proc))
)))
(list oracle-query-process
(process-buffer oracle-query-process)
(oracle-query-fetch-username-from-connect-string connect-string))
))
(defun oracle-query-connection-alive-p(connection)
"test whether the connection is alive."
(and connection
(listp connection)
(processp (car connection))
(bufferp (nth 1 connection))
(buffer-live-p (nth 1 connection))
(equal (process-status (car connection)) 'run)))
(defun oracle-query-close-connection(connection)
"close connection.kill oracle process and buffer ."
(when (oracle-query-connection-alive-p connection)
(process-send-string (car connection) "exit\n"))
(sleep-for 0.1)
(when (oracle-query-connection-alive-p connection)
(kill-process (car connection))
(kill-buffer (nth 1 connection)))
)
(defun oracle-query (sql &optional oracle-query-connection)
"execute sql using `sqlplus' ,and return the result of it."
(cdr (oracle-query-with-heading sql oracle-query-connection)))
(defun oracle-query-with-heading (sql &optional oracle-query-connection)
"execute sql using `sqlplus' ,and return the result of it."
(let( (connection oracle-query-connection) process)
(unless connection
(unless (and oracle-query-default-connection
(buffer-live-p (nth 1 oracle-query-default-connection))
(equal (process-status (nth 0 oracle-query-default-connection)) 'run)
)
(setq oracle-query-default-connection (call-interactively 'oracle-query-create-connection)))
(setq connection oracle-query-default-connection)
)
(setq process (car connection))
(when (string-match "\\(.*\\);[ \t\n\r]*" sql)
(setq sql (match-string 1 sql)))
(setq sql (replace-regexp-in-string "[\n\r]" " " sql))
(with-current-buffer (process-buffer process)
(delete-region (point-min) (point-max))
(let ((start (point-min)) end)
(goto-char (point-max))
(process-send-string process (format "%s ;\n" sql))
(goto-char (point-min))
(while (not (re-search-forward "^\\([0-9]+\\|no\\) rows? \\(selected\\|updated\\|deleted\\)" nil t 1))
(when (accept-process-output process oq-timeout-wait-for-result 0 nil)
(goto-char (point-min))))
(if (not (string= (match-string 2) "selected"))
(match-string 0)
(setq end (1- (match-beginning 0)))
(when (re-search-backward "\\bSQL> " nil t 1) (setq start (match-end 0)))
(oq-parse-result-as-list (buffer-substring start end)))))))
(provide 'oracle-query)