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.
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.
Then enter a existing or new SQL file name.
Your screen should split with the SQL file and the SQLPlus buffer in another.
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
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
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>")
This blog was written in EMACS
Roy's awesome blog on technology can be found here