EMACS and SQLPLUS

I have recently discovered EMACS thanks to the unfaultering effort by my co-brother Roy. I have only one word for EMACS- fan-"bleedy"-tastic.
Although not very intuitive, it is immensely powerful and extremely great to improve productivity.

I will break this blog into three sections for easier reading.

Section 1 - How does this fit into my work?

---

Remember the days spent with a SQL in a notepad, and server has no scripts. The day drags on with you running Ctrl+C, Ctrl+V , then right click into the Putty Console.


Now here is how it happens in Emacs.

So start the SQL-Plus buffer in Emacs. This should be inbuilt inside the 24.2 version onwards.

M-x sql-oracle
It should prompt for a userid, password and host name. Hostnames is the TNS names entry in your local tnsnames.ora file.

If you get the below error, like I did
ORA-12557: TNS:protocol adapter not loadable

Set ORACLE_HOME and PATH to same Oracle home(I have two Oracle Homes in my laptop) .Restart EMacs
Try M-x sql-oracle, again
This time hopefully you should get connected.
You are on the command prompt so you try any of the SQLs.

Now the interesting bit of working efficiently.
C-X, C-f

Then enter a existing or new SQL file name.

Your screen should split with the SQL file and the SQLPlus buffer in another.

Use C- to set the mark and move the keys to select the whole SQL

To send the selected SQL into SQLPlus buffer use the C-C C-r

Section 2 - Errors I encountered

---

Some of the errors I encountered were --

If you get the below error,

No SQL process started

You can try below

M-x sql-set-sqli-buffer
select *SQL* ( should be the default)

That lead me to another error

There is not suitable SQLi buffer

To fix this error, note the bottom of the opened SQL file. The status bar should say SQL[Oracle].

If it says SQL[ANSI], it needs to be changed by doing

M-x sql-set-product
key in "oracle"

Now the "No SQL process started" error can be fixed by using the M-x sql-set-sqli-buffer like above.

Now use C-X C-r to send the SQL from the file to the SQL buffer.

Section 3 - Couple of other hacks.

---

You must have noticed that pressing the backspace deletes the "SQL>" prompt. To fix this , add this to your .emacs file

(setq comint-prompt-regexp "^SQL>")

(custom-set-variables
'(comint-prompt-read-only t)
'(comint-use-prompt-regexp t))
(custom-set-faces)

---

PS:

This blog was written in EMACS

Roy's awesome blog on technology can be found here